Simplifying Spreadsheet Management: Introducing a Google Apps Script Automation

Abstract

This post introduces a Google Apps Script solution that automates the creation, sharing, and monitoring of multiple Google Spreadsheets, providing a more efficient and streamlined approach to managing user data.

Introduction

I've often encountered requests from clients who need to manage multiple Google Spreadsheets for various users, often by copying a template spreadsheet. In these situations, I typically propose the following approach:

Create a Template Spreadsheet: This spreadsheet serves as a blueprint, containing essential elements like custom functions implemented using Google Apps Script.
Develop a Dashboard Spreadsheet: This centralized hub provides an overview of all user spreadsheets.
Clone and Share Spreadsheets: For each user, a copy of the template spreadsheet is created and shared with them. The URLs of these individual spreadsheets are then recorded in the dashboard.
Import Data Using IMPORTRANGE: The dashboard utilizes the IMPORTRANGE function to fetch data from individual user spreadsheets. This enables real-time updates on the dashboard as users edit their respective sheets.

While this manual process can be effective, it can become cumbersome and time-consuming, especially as the number of users and spreadsheets grows. To streamline this workflow and enhance efficiency, I've developed a Google Apps Script solution that automates many of these steps.

By leveraging Google Apps Script, we can take advantage of Google APIs for seamless authorization and access to Google services. This allows for more robust and scalable solutions. Moreover, Google Apps Script's user-friendly interface makes it accessible to a wide range of users, from novice to experienced developers.

In this post, I'll introduce the Google Apps Script that automates the process of creating, sharing, and monitoring multiple Google Spreadsheets, providing a more efficient and streamlined solution for managing user data.

Usage

1. Prepare a template Spreadsheet

Please prepare the template Spreadsheet. As a sample situation, the following sample spreadsheet has been created.

Create a new Spreadsheet. Here, only the 1st tab is used.
Copy and paste a script const SAMPLE = e => e + 1; to the script editor and save the script.
Put a custom function =SAMPLE(1) into "A1" of the 1st tab. This is used as the sample cell value.

This is used as the template Spreadsheet.

2. Create a dashboard Spreadsheet

Here, the dashboard Spreadsheet is created. Please do the following flow.

Create a new Spreadsheet and rename the 1st sheet to "dashboard".
Put the template Spreadsheet ID and the folder ID you want to put the copied template Spreadsheet to "B2" and "B3", respectively.
Put email addresses to "A6:A". These email addresses are the user's email addresses. When the template Spreadsheet is copied, the copied Spreadsheet is shared with this email.
Copy and paste the following script as shown in the next section.

3. Script for managing Spreadsheets

1. Enable APIs

Please enable Drive API and Sheets API at Advanced Google services. Ref

2. Install library

Please install BatchRequest of the Google Apps Script library. You can see how to install it here.

3. Script

Please copy and paste the following script to the script editor of the dashboard Spreadsheet and save the script.

function myFunction() {
  console.log("--- Retrieve values from the dashboard sheet.");
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ssId = ss.getId();
  const dashboard = ss.getSheetByName("dashboard");
  const { templateSpreadsheetId, folderId } = dashboard
    .getRange("A2:B3")
    .getValues()
    .reduce((o, [a, b]) => ((o[a] = b), o), {});
  const range = dashboard.getRange("A6:B" + dashboard.getLastRow());
  const values = range.getValues();
  if (ss.getSheets().some((s) => values.flat().includes(s.getSheetName()))) {
    throw new Error(
      "The same sheet names with your inputted email addresses are existing. Please rename the sheet names."
    );
  }
  const headers = { authorization: "Bearer " + ScriptApp.getOAuthToken() };
  if (values.length == 0) return;

  console.log("--- Copy template Spreadsheet.");
  const reqs1 = values.map(([name]) => ({
    method: "POST",
    endpoint: `https://www.googleapis.com/drive/v3/files/${templateSpreadsheetId}/copy?supportsAllDrives=true`,
    requestBody: { parents: [folderId], name },
  }));
  const res1 = BatchRequest.EDo({
    batchPath: "batch/drive/v3",
    requests: reqs1,
  });
  console.log(
    `--- ${res1.length} Spreadsheets were created by copying the template Spreadsheet.`
  );

  console.log("--- Create 3 batch requests.");
  const { v, reqs2, reqs3, reqs4 } = res1.reduce(
    (o, { id, name }, i) => {
      const url = `https://docs.google.com/spreadsheets/d/${id}/edit`;
      o.v.push([name, url]);
      o.reqs2.push({
        method: "POST",
        endpoint: `https://www.googleapis.com/drive/v3/files/${id}/permissions?supportsAllDrives=true`,
        requestBody: { role: "writer", type: "user", emailAddress: name },
      });
      o.reqs3.push({
        url: `https://docs.google.com/spreadsheets/d/${ssId}/externaldata/addimportrangepermissions?donorDocId=${id}`,
        method: "post",
        headers,
      });
      const sheetId = `${123456789 + i}`;
      o.reqs4.push(
        { addSheet: { properties: { title: name, sheetId } } },
        {
          updateCells: {
            range: {
              sheetId,
              startRowIndex: 0,
              endRowIndex: 1,
              startColumnIndex: 0,
              endColumnIndex: 1,
            },
            rows: [
              {
                values: [
                  {
                    userEnteredValue: {
                      formulaValue: `=IMPORTRANGE("${url}","A1:ZZZ")`,
                    },
                  },
                ],
              },
            ],
            fields: "userEnteredValue",
          },
        }
      );
      return o;
    },
    { v: [], reqs2: [], reqs3: [], reqs4: [] }
  );

  console.log("--- Share the copied template Spreadsheet with each user.");
  BatchRequest.EDo({ batchPath: "batch/drive/v3", requests: reqs2 });

  // Ref: https://medium.com/google-cloud/allowing-access-by-importhtml-importdata-importfeed-importxml-and-importrange-on-google-3f4f5d7e6b1e
  console.log(
    "--- Authorize IMPORTRANGE between the dashboard Spreadsheet and the copied tempate Spreadsheets."
  );
  UrlFetchApp.fetchAll(reqs3);

  console.log("--- Add sheets to the dashboard Spreadsheet and put formulas.");
  Sheets.Spreadsheets.batchUpdate({ requests: reqs4 }, ssId);

  console.log("--- Update dashboard sheet.");
  range.setValues(v);
}

4. Testing

When myFunction is run under the above settings have been finished, the following steps are run.

Copy the template Spreadsheet for the number of email addresses.
Share the copied Spreadsheet with each email address.
Add sheets for the number of email addresses to the dashboard Spreadsheet.
A formula of IMPORTRANGE to cell "A1" of the added sheets.
Authorize to use IMPORTRANGE. Ref
Put the URLs of the copied Spreadsheet for each user into the dashboard sheet.

By this flow, when the user edit the sheet, you who is the owner of the dashboard Spreadsheet can see the cell values of the user's sheet in one of the sheets in the dashboard Spreadsheet. Also, you can see the URLs of each user's Spreadsheet.

Note

Unfortunately, I do not know the maximum number of users using this script. So, when under a large number of users, an error occurs or the script doesn't work, please split the users into multiple groups and run the script in every group.

tanaikech/submit.md