Google Sheets: Add New Sheet
Adds a new sheet to a Google Sheet.
Configs
  • C1: OAuth2 Setting *
  • C2: Target Spreadsheet ID *
  • C3: New Sheet Title (if blank, set automatically)#{EL}

Notes

Capture

Appendix

  • 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
Script (click to open)
// 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 sheetId = configs.get("conf_SheetId");
  if (sheetId === "" || sheetId === null) {
    throw "Target Sheet ID isn't set";
  }

  const sheetTitle = configs.get("conf_SheetTitle");
  addSheet(oauth2, sheetId, sheetTitle);
}


/**
 * Google スプレッドシートに新しいシートを追加
 * @param {String} oauth2 OAuth2 認証設定
 * @param {String} sheetId スプレッドシートの ID
 * @param {String} sheetTitle シートのタイトル
 */
function addSheet(oauth2, sheetId, sheetTitle) {
  // Method: spreadsheets.batchUpdate
  const uri = `https://sheets.googleapis.com/v4/spreadsheets/${encodeURIComponent(sheetId)}: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(`Status: ${status}`);
        engine.log(responseStr);
        throw `Failed to add a new sheet.`;
  }
  engine.log("Succeeded to add a sheet");
}

%d bloggers like this: