2023-04-18 / BPMN Icons / ,

Google Sheets: Copy Sheet

Google スプレッドシート: シートコピー

This item copies a single Google Sheet to the same or another spreadsheet.

Auto Step icon
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.`;
  }
}
%d bloggers like this: