// GraalJS Script (engine type: 2)
//////// START "main()" /////////////////////////////////////////////////////////////////
main();
function main(){
//// == Config Retrieving / 工程コンフィグの参照 ==
const strAuthzSetting = configs.get( "AuthzConfA" ); // required
engine.log( " AutomatedTask Config: Authz Setting: " + strAuthzSetting );
const strValue = configs.get( "StrConfB" ) + ""; // required
const strFileId = configs.get( "StrConfC1" ) + ""; // required
const strSheetName = configs.get( "StrConfC2" ) + "";
const strCellId = configs.get( "StrConfC3" ) + ""; // required
const strPocketBackup = configs.getObject( "SelectConfD" );
//// == Data Retrieving / ワークフローデータの参照 ==
// (nothing)
//// == Calculating / 演算 ==
//// Back up the current value of cell
if( strPocketBackup !== null ){
/// Gets values from a spreadsheet.
/// (Google Sheets > API v4)
/// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get
// request0, prepare
let getUri0 = "https://sheets.googleapis.com/v4/spreadsheets/" +
strFileId + "/values/" + strSheetName + "!" + strCellId;
let request0 = httpClient.begin(); // HttpRequestWrapper
request0 = request0.authSetting( strAuthzSetting ); // with "Authorization: Bearer XX"
// request0, try
const response0 = request0.get( getUri0 ); // HttpResponseWrapper
engine.log( " AutomatedTask ApiRequest0 Start: " + getUri0 );
const response0Code = response0.getStatusCode() + "";
const response0Body = response0.getResponseAsString() + "";
engine.log( " AutomatedTask ApiResponse Status: " + response0Code );
if( response0Code !== "200"){
throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
response0Code + "\n" + response0Body + "\n" );
}
// response0, parse
/* Example 1 - empty cell
{
"range": "Sheet1!D4",
"majorDimension": "ROWS"
}
*/
/* Example 2 - multiline string cell
{
"range": "Sheet1!D3",
"majorDimension": "ROWS",
"values": [
[
"Multiline\nCell"
]
]
}
*/
const response0Obj = JSON.parse( response0Body );
if( response0Obj.hasOwnProperty("values") ){
engine.setData( strPocketBackup, response0Obj.values[0][0] );
}
}
//// Overwrite the value in the cell
/// Sets values in a range of a spreadsheet.
/// (Google Sheets > API v4)
/// https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update
// request1, prepare
let request1Obj = {};
request1Obj.values = [];
request1Obj.values[0] = [];
request1Obj.values[0][0] = strValue;
let putUri1 = "https://sheets.googleapis.com/v4/spreadsheets/" +
strFileId + "/values/" + strSheetName + "!" + strCellId;
let request1 = httpClient.begin(); // HttpRequestWrapper
request1 = request1.authSetting( strAuthzSetting ); // with "Authorization: Bearer XX"
request1 = request1.queryParam( "valueInputOption", "USER_ENTERED" );
request1 = request1.body( JSON.stringify( request1Obj ), "application/json" );
// request1, try
const response1 = request1.put( putUri1 ); // HttpResponseWrapper
engine.log( " AutomatedTask ApiRequest1 Start: " + putUri1 );
const response1Code = response1.getStatusCode() + "";
const response1Body = response1.getResponseAsString() + "";
engine.log( " AutomatedTask ApiResponse Status: " + response1Code );
if( response1Code !== "200"){
throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
response1Code + "\n" + response1Body + "\n" );
}
// response1, parse
/* engine.log( response1Body ); // debug
{
"spreadsheetId": "1SK4mXXXXXq7ZriYYYYYOJHmIZZZZZAtjynWWWWW-qlE",
"updatedRange": "Sheet1!C3",
"updatedRows": 1,
"updatedColumns": 1,
"updatedCells": 1
}
*/
const response1Obj = JSON.parse( response1Body );
engine.log( " AutomatedTask ApiRequest1 updateRange: " + response1Obj.updatedRange );
//// == Data Updating / ワークフローデータへの代入 ==
// (No Output except Console Log and response0Obj.values[0][0] )
} //////// END "main()" /////////////////////////////////////////////////////////////////
/*
NOTES
- If the sheet name is omitted, Value will be inserted into the first visible sheet.
- Cell Coordinates in A1 notation (where "A" is the column and "1" is the row of the cell)
- If set the value to an empty string, a cell will be an empty value (deleted).
- It also supports overwriting with multi-line strings.
- For merged cells, the value can only be stored in the upper left cell.
NOTES-ja
- シート名を省略した場合、「非表示でない先頭シート」に挿入されます。
- セル座標はA1記法で指定します（"A" はセルの列番号、"1" はセルの行番号です）
- 上書き値が空文字列の場合、セルデータは空になります。（削除されます）
- 複数行文字列での上書きにも対応しています。
- 結合セル（Merged Cell）の場合、値は「左上のセル」にのみ保持されます。
*/
/*
APPENDIX
- OAuth Setting sample [HTTP Authorization Setting]
- "Authorization Endpoint URL"
- https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force
- "Token Endpoint URL"
- https://accounts.google.com/o/oauth2/token
- to get your "ClientId" and "Secret"
- Access to https://console.developers.google.com/apis/credentials
- Authorization Scopes (one of the following)
- https://www.googleapis.com/auth/drive
- https://www.googleapis.com/auth/drive.file
- https://www.googleapis.com/auth/spreadsheets
- About the levels of access
- https://developers.google.com/identity/protocols/oauth2/scopes#sheets
APPENDIX-ja
- OAuth 設定例（［HTTP認証設定］の例）
- "Authorization Endpoint URL"
- https://accounts.google.com/o/oauth2/auth?access_type=offline&approval_prompt=force
- "Token Endpoint URL"
- https://accounts.google.com/o/oauth2/token
- なお "ClientId" と "Secret" は開発者コンソールにアクセスして取得します。
- Access to https://console.developers.google.com/apis/credentials
- 認可スコープ（以下のいずれか）
- https://www.googleapis.com/auth/drive
- https://www.googleapis.com/auth/drive.file
- https://www.googleapis.com/auth/spreadsheets
- それぞれのアクセスレベルについて
- https://developers.google.com/identity/protocols/oauth2/scopes#sheets
*/
Pingback: Google Spreadsheets Cell Value Update – Questetra Support