2023-04-13 / BPMN Icons / ,

Google Sheets: Add New Sheet

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

This item adds a new sheet to a Google Sheet.

Auto Step icon
Basic Configs
Step Name
Note
Configs for this Auto Step
conf_OAuth2
C1: OAuth2 Setting *
conf_SheetId
C2: Target Spreadsheet ID *
conf_SheetTitle
C3: New Sheet Title (if blank, set automatically)#{EL}
conf_AddedSheetTitle
C4: Data item to save Added Sheet Title

Notes

  • The spreadsheet ID is included in the URL. 　https://docs.google.com/spreadsheets/d/SPREADSHEETID/edit#gid=0

Capture

Script (click to open)
  • An XML file that contains the code below is available to download
    • google-sheets-sheet-add.xml (C) Questetra, Inc. (MIT License)
    • If you are using Professional, you can modify the contents of this file and use it as your own add-on auto step

// OAuth2 config
// 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
// Consumer Key: (Get by Google Developers Console)
// Consumer Secret: (Get by Google Developers Console)

main();
function main() {
    //// == 工程コンフィグの参照 / Config Retrieving ==
    const oauth2 = configs.get("conf_OAuth2");
    const spreadSheetId = retrieveSpreadSheetId();
    const sheetTitle = configs.get("conf_SheetTitle");
    const addedSheetTitleDef = configs.getObject("conf_AddedSheetTitle");

    //// == Calculating / 演算 ==
    const addedSheetTitle = addSheet(oauth2, spreadSheetId, sheetTitle);

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

/**
 * config からスプレッドシートの ID を読み出す
 * @return {String} spreadSheetId スプレッドシートの ID
 */
function retrieveSpreadSheetId() {
    let spreadSheetId = configs.get("conf_SheetId");
    const spreadSheetIdDef = configs.getObject("conf_SheetId");
    if (spreadSheetIdDef !== null) {
        spreadSheetId = engine.findData(spreadSheetIdDef);
    }
    if (spreadSheetId === null || spreadSheetId === "") {
        throw "Target Spreadsheet ID isn't set.";
    }
    return spreadSheetId;
}

/**
 * Google スプレッドシートに新しいシートを追加
 * @param {String} oauth2 OAuth2 認証設定
 * @param {String} spreadSheetId スプレッドシートの ID
 * @param {String} sheetTitle シートのタイトル
 * @return {String} addedSheetTitle 追加したシートのタイトル
 */
function addSheet(oauth2, spreadSheetId, sheetTitle) {
    // Method: spreadsheets.batchUpdate
    const uri = `https://sheets.googleapis.com/v4/spreadsheets/${encodeURIComponent(spreadSheetId)}:batchUpdate`;

    const requestObj = {
        requests: [
            { "addSheet": {} }
        ]
    };
    if (sheetTitle !== "" && sheetTitle !== null) {
        requestObj.requests[0].addSheet["properties"] = {
            "title": sheetTitle
        };
    }

    const response = httpClient.begin()
        .authSetting(oauth2)
        .body(JSON.stringify(requestObj), "application/json")
        .post(uri);

    const status = response.getStatusCode();
    const responseStr = response.getResponseAsString();
    if (status >= 300) {
        engine.log(responseStr);
        throw `Failed to add a new sheet. status: ${status}`;
    }

    // レスポンスから追加したシートのタイトルを取得して返す
    const { replies } = JSON.parse(responseStr);
    return replies[0].addSheet.properties.title;
}
%d bloggers like this: