TANAIKE
The Thinker
About Articles Topics Tags Contact Links Web Applications Donate Home

Copying Sheet Including Charts from Google Spreadsheet to Another Google Spreadsheet using Google Apps Script

GasTips / GoogleChartTips
Google Apps Script / spreadsheet / chart

Gists

Abstract

This report presents a workaround for copying a sheet, including its charts, from Google Spreadsheet “A” to Google Spreadsheet “B” using Google Apps Script.

Description

In this report, I would like to introduce a workaround for the following goal:

In general, when Sheets API is used, all objects of the Spreadsheet can be copied easily. However, when I tested this, I noticed the following problems:

Due to this situation, I determined that Sheets API cannot be used for this scenario. Therefore, I attempted to achieve this using the Spreadsheet service (SpreadsheetApp) instead of Sheets API.

When the Spreadsheet service is used, I observed the following issues:

Based on these findings, I would like to propose a sample script for achieving the aforementioned goal as follows.

Note: In this sample script, Google Apps Script is executed using Node.js. Therefore, a Web App is deployed.

Usage

1. Prepare Google Apps Script project

Please create a Google Apps Script project. In this case, both the standalone type and the container-bound script type can be used.

2. Sample script

Please copy and paste the following script to the script editor of the Google Apps Script project.

/**
 * ### Description
 * Copying a sheet including charts from Google Spreadsheet "A" to Google Spreadsheet "B" using a script.
 *
 * @param {Object} object Object including source spreadsheet ID, source sheet name, and target spreadsheet ID.
 * @return {void}
 */
function main_(object) {
  const { srcSSId, srcSheetName, dstSSId } = object;

  // Copy the source sheet to the target Spreadsheet "B".
  const srcSheet =
    SpreadsheetApp.openById(srcSSId).getSheetByName(srcSheetName);
  const dstSS = SpreadsheetApp.openById(dstSSId);
  if (
    dstSS.getSheets().some((s) => s.getSheetName() == srcSheet.getSheetName())
  ) {
    throw new Error("Target sheet has already been existing.");
  }
  const dstSheet = srcSheet.copyTo(dstSS).setName(srcSheet.getName());

  // Remove all charts in the target sheet.
  dstSheet.getCharts().forEach((c) => dstSheet.removeChart(c));

  // Retrieve all charts in the source sheet, and update all charts for the target sheet.
  // And, insert the updated charts to the target sheet.
  const charts = srcSheet.getCharts();
  charts.forEach((c) => {
    const chart = c.modify();
    chart.getRanges().forEach((r) => {
      const dataSheet = r.getSheet();
      const dataSheetName = dataSheet.getSheetName();
      if (!dstSS.getSheets().some((s) => s.getSheetName() == dataSheetName)) {
        dataSheet.copyTo(dstSS).setName(dataSheetName);

        // If you want to copy only the cell values without the formulas, please use the following script.
        // const sheet = dstSS.insertSheet(dataSheetName);
        // const values = dataSheet.getDataRange().getValues();
        // sheet.getRange(1, 1, values.length, values[0].length).setValues(values);
      }
      chart.removeRange(r);
      chart.addRange(
        dstSS.getSheetByName(dataSheetName).getRange(r.getA1Notation())
      );
      chart.setOption(
        "applyAggregateData",
        c.getOptions().get("applyAggregateData")
      );
    });
    dstSheet.insertChart(chart.build());
  });

  // Refresh formulas in the target sheet.
  const dataRange = dstSheet.getDataRange();
  dataRange.createTextFinder("=").matchFormulaText(true).replaceAllWith("=@@@");
  dataRange.createTextFinder("=@@@").matchFormulaText(true).replaceAllWith("=");
}

/**
 * ### Description
 * This is used as Web Apps of GET method.
 *
 * @param {Object} e Event object for Web Apps.
 * @return {ContentService.TextOutput}
 */
function doGet(e) {
  try {
    main_(e.parameters);
  } catch ({ stack }) {
    return ContentService.createTextOutput(stack);
  }
  return ContentService.createTextOutput("Done.");
}

3. Deploy Web Apps

The detailed information can be seen in the official document.

Please set this using the script editor.

  1. On the script editor, at the top right of the script editor, please click “click Deploy” -> “New deployment”.
  2. Please click “Select type” -> “Web App”.
  3. Please input the information about the Web App in the fields under “Deployment configuration”.
  4. Please select “Me” for “Execute as”.
  5. Please select “Anyone” for “Who has access to the app:”.
  6. Please click “Deploy” button.
  7. On the script editor, at the top right of the script editor, please click “click Deploy” -> “Test deployments”.
  8. Copy Web Apps URL. It’s like https://script.google.com/macros/s/###/exec.

4. Testing

The sample Node.js script is as follows. Please replace webAppsUrl with your Web Apps URL. In this sample, it supposes that the charts are included in the sheet srcSheetName on Spreadsheet srcSSId, and, that sheet is copied to Spreadsheet dstSSId.

const params = {
  srcSSId: "###", // Please set source Spreadsheet ID.
  srcSheetName: "###", // Please set source sheet name.
  dstSSId: "###", // Please set target Spreadsheet ID.
};
const q = new URLSearchParams(params);
const webAppsUrl = "https://script.google.com/macros/s/{deploymentId}/exec";

fetch(`${webAppsUrl}?${q}`)
  .then((res) => res.text())
  .then((res) => console.log(res))
  .catch((err) => console.error(err));

In this script, the datasheet is copied from the source Spreadsheet to the target Spreadsheet to use in the charts.

When this script is run, the following result is obtained. The reason that the values of the pie chart are changed is due to the formulas of =RANDBETWEEN(1;10) in the datasheet.

Copying Sheet Including Charts from Google Spreadsheet to Another Google Spreadsheet using Google Apps Script

As another approach, when a curl command is used, it becomes as follows.

curl -L "https://script.google.com/macros/s/{deploymentId}/exec?srcSSId=###&srcSheetName=###&dstSSId=###"

Note

References

 Share!