Google スプレッドシート: セル値, 更新

// 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
*/