
Google Sheets: Copy Sheet
This item copies a single Google Sheet to the same or another spreadsheet.
Basic Configs
- Step Name
- Note
Configs for this Auto Step
- conf_OAuth2
- C1: OAuth2 Setting *
- conf_SpreadSheetId
- C2: Source Spreadsheet ID *
- conf_SheetTitle
- C3: Source Sheet Title *
- conf_Destination
- C4: Destination Spreadsheet ID (if blank, the same spreadsheet)
- conf_NewSheetTitle
- C5: New Sheet Title (if blank, set automatically)#{EL}
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-copy.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 = configs.get("conf_SpreadSheetId");
const sheetTitle = configs.get("conf_SheetTitle");
const destinationDef = configs.getObject("conf_Destination");
let destinationSpreadsheetId = "";
if (destinationDef === null) {
destinationSpreadsheetId = configs.get("conf_Destination");
} else {
destinationSpreadsheetId = engine.findData(destinationDef);
}
if (destinationSpreadsheetId === "" || destinationSpreadsheetId === null) {
destinationSpreadsheetId = spreadSheetId;
}
const newSheetTitle = configs.get("conf_NewSheetTitle");
if (newSheetTitle.length > 100) {
throw "Sheet Title is too long. It cannot be longer than 100 characters.";
}
//// == Calculating / 演算 ==
const sheetId = getSheetId(oauth2, spreadSheetId, sheetTitle);
const destinationSheetId = copySheet(
oauth2,
spreadSheetId,
sheetId,
destinationSpreadsheetId
);
if (newSheetTitle !== "" && newSheetTitle !== null) {
setTitle(
oauth2,
destinationSpreadsheetId,
destinationSheetId,
newSheetTitle
);
}
}
/**
* Google スプレッドシートのシート ID を取得
* @param {String} oauth2 OAuth2 認証設定
* @param {String} spreadSheetId スプレッドシートの ID
* @param {String} sheetTitle シートタイトル
* @return {Number} sheetId シート ID
*/
function getSheetId(oauth2, spreadSheetId, sheetTitle) {
const uri = `https://sheets.googleapis.com/v4/spreadsheets/${encodeURIComponent(
spreadSheetId
)}?includeGridData=false`;
const response = httpClient.begin().authSetting(oauth2).get(uri);
const status = response.getStatusCode();
const responseStr = response.getResponseAsString();
if (status !== 200) {
engine.log(responseStr);
throw `Failed to get sheet information. status: ${status}`;
}
const { properties, sheets } = JSON.parse(responseStr);
for (let i = 0; i < sheets.length; i++) {
if (sheets[i].properties.title === sheetTitle) {
const sheetId = parseInt(sheets[i].properties.sheetId, 10);
return sheetId;
}
}
// error (not exist)
throw `Sheet ${sheetTitle} does not exist`;
}
/**
* Google スプレッドシートに新しいシートをコピー
* @param {String} oauth2 OAuth2 認証設定
* @param {String} spreadSheetId スプレッドシートの ID
* @param {String} sheetId シートの ID
* @param {String} destinationSpreadsheetId コピー先スプレッドシート ID
* @return {String} destinationSheetId コピー先シートの ID
*/
function copySheet(oauth2, spreadSheetId, sheetId, destinationSpreadsheetId) {
// Method: spreadsheets.copyTo
const uri = `https://sheets.googleapis.com/v4/spreadsheets/${encodeURIComponent(
spreadSheetId
)}/sheets/${encodeURIComponent(sheetId)}:copyTo`;
const response = httpClient
.begin()
.authSetting(oauth2)
.body(JSON.stringify({ destinationSpreadsheetId }), "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 copy sheet.`;
}
return JSON.parse(responseStr).sheetId;
}
/**
* Google スプレッドシートのシート名を変更する
* @param {String} oauth2 OAuth2 認証設定
* @param {String} spreadSheetId スプレッドシートの ID
* @param {String} sheetId シートの ID
* @param {String} newSheetTitle シートの新しいタイトル
*/
function setTitle(oauth2, spreadSheetId, sheetId, newSheetTitle) {
// Method: spreadsheets.batchUpdate
const uri = `https://sheets.googleapis.com/v4/spreadsheets/${encodeURIComponent(
spreadSheetId
)}:batchUpdate`;
const requestObj = {
requests: [
{
updateSheetProperties: {
fields: "title",
properties: {
title: newSheetTitle,
sheetId: sheetId,
},
},
},
],
};
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 set sheet title.`;
}
}