Google スプレッドシート: Spreadsheet, シートを追加

// GraalJS Script (engine type: 2)
/*
Notes:
- See URL for "Spreadsheet ID".
    - https://docs.google.com/spreadsheets/d/SPREADSHEETID/edit#gid=0
- When setting the SheetId rule, ensure that a unique positive integer is generated.
     - If no rule, SheetId will be randomly generated by Google.
     - Set as "#{processInstanceId}" to make "Process ID" SheetId.
         - R2290: Dynamic value insertion by EL expression
         - https://questetra.zendesk.com/hc/en-us/articles/360024293072-R2290
     - If the SheetId already exists, the automated task ends with an error.
Notes(ja):
- スプレッドシートのIDはURLを参照してください。
    - https://docs.google.com/spreadsheets/d/SPREADSHEETID/edit#gid=0
- SheetId ルールを設定する場合は、ユニークな正の整数（int32）が生成されるようにします。
    - ルールを設定しない場合、Google によってランダムに生成されます。
    - "プロセスID" を SheetId とする場合は "#{processInstanceId}" のように設定します。
        - R2290: EL式による動的な値の挿入
        - https://questetra.zendesk.com/hc/ja/articles/360024293072-R2290
    - SheetId が既に存在していた場合、自動工程はエラー終了します。

- To Get "ClientId" and "Secret"
    - Access to "https://console.developers.google.com/"
- OAuth Setting sample
    - "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
    - "Scope"
        - https://www.googleapis.com/auth/spreadsheets
- Error Log sample
    - AutomatedTask UnexpectedResponseError: 400
        - { "error": {
        - "code": 400,
        - "message": "Invalid requests[0].addSheet: シート名「Test」はすでに存在し...",
        - "status": "INVALID_ARGUMENT"
        - } }
    - AutomatedTask UnexpectedResponseError: 400
        - { "error": {
        - "code": 400,
        - "message": "Invalid requests[0].addSheet: Sheet with id 1234 already exists.",
        - "status": "INVALID_ARGUMENT"
        - } }

*/

//////// START "main()" /////////////////////////////////////////////////////////////////
main();
function main(){ 

//// == Config Retrieving / 工程コンフィグの参照 ==
const strOauthSetting     = configs.get      ( "OAuth2ConfA" );   // required
const strSpreadsheetId    = configs.get      ( "StrConfB" ) + ""; // required
const strSheetName        = configs.get      ( "StrConfC" ) + ""; // required
const strSpecifiedSheetId = configs.get      ( "StrConfC2") + "";
const strPocketSheetId    = configs.getObject( "SelectConfD" );


//// == Data Retrieving / ワークフローデータの参照 ==
// (nothing)


//// == Calculating / 演算 ==

// prepare request1
// ref) https://developers.google.com
//            /sheets/api/reference/rest/v4/spreadsheets/request#addsheetrequest
let request1Obj = {};
    request1Obj.requests = [];
    request1Obj.requests[0] = {};
    request1Obj.requests[0].addSheet = {};
    request1Obj.requests[0].addSheet.properties = {};
    if( strSpecifiedSheetId !== "" ){
      request1Obj.requests[0].addSheet.properties.sheetId = strSpecifiedSheetId;
    }
    request1Obj.requests[0].addSheet.properties.title = strSheetName;
let uri1     = "https://sheets.googleapis.com/v4/spreadsheets/" +
                strSpreadsheetId + ":batchUpdate";
const token  = httpClient.getOAuth2Token( strOauthSetting );
engine.log( " AutomatedTask ApiRequest Token: prepared" );
let request1 = httpClient.begin(); // HttpRequestWrapper
    request1 = request1.bearer( token );
    request1 = request1.body( JSON.stringify( request1Obj ), "application/json" );

// post request1
const response1     = request1.post( uri1 );       // HttpResponseWrapper
engine.log( " AutomatedTask ApiRequest Start: " + uri1 );
const response1Code = response1.getStatusCode() + ""; // (primitive string)
const response1Body = response1.getResponseAsString() + "";
engine.log( " AutomatedTask ApiResponse Status: " + response1Code );
if( response1Code !== "200"){
  throw new Error( "\n AutomatedTask UnexpectedResponseError: " +
                    response1Code + "\n" + response1Body + "\n" );
}

// parse response1
/* engine.log( response1Body ); // debug
response sample
{
  "spreadsheetId": "1uwGxxxx0UWaxxxxsiCZxxxx6Y3UxxxxO0BgxxxxYRdo",
  "replies": [
    {
      "addSheet": {
        "properties": {
          "sheetId": 12345,
          "title": "Test12345",
          "index": 4,
          "sheetType": "GRID",
          "gridProperties": {
            "rowCount": 1000,
            "columnCount": 26
          }
        }
      }
    }
  ]
}
*/
const response1Obj  = JSON.parse( response1Body );
let strSheetId = response1Obj.replies[0].addSheet.properties.sheetId + "";
engine.log( " AutomatedTask ApiResponse NewSheetId: " + strSheetId );


//// == Data Updating / ワークフローデータへの代入 ==
if( strPocketSheetId !== null ){
  engine.setData( strPocketSheetId, strSheetId );
}

} //////// END "main()" /////////////////////////////////////////////////////////////////