
Google Sheets: Add New Sheet
This item adds a new sheet to a Google Sheet.
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;
}