Google Sheets: Spreadsheets, Add Sheet
Google Sheets: Spreadsheets, Add Sheet
Adds a new sheet to the end of the spreadsheet. If the specified sheet name already exists, this Automated Task will end with an error. If needed, SheetId can also be specified.
Configs
  • A: Select Authorization Setting (OAuth2) *
  • B: Set Document ID (Spreadsheet ID) *#{EL}
  • C: Set Name of Sheet (e.g. “#{#format(#q_date, ‘yyyy-MM’)}”) *#{EL}
  • C2: If needed Set Id Rule of Sheet (e.g. “#{processInstanceId}”)#{EL}
  • D: Select STRING DATA that stores ID of Added Sheet (update)
Script (click to open)
// 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()" /////////////////////////////////////////////////////////////////

Download

2020-10-04 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/google-sheets-spreadsheets-add-sheet/
The Addon-import feature is available with Professional or Enterprise edition.

Notes

Capture

Adds a new sheet to the end of the spreadsheet. If the specified sheet name already exists, this Automated Task will end with an error. If needed, SheetId can also be specified.

See also

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: