Putting Values of All Spreadsheets in Folder to Master Spreadsheet with Low Process cost using Google Apps Script

GasTips / Spreadsheet
Google Apps Script / Spreadsheet

Gists

This is a sample script for putting the values of all Spreadsheets in a folder to the master Spreadsheet with a low process cost using Google Apps Script.

There is a case in that I want to collect the values from multiple Spreadsheets and put the values into the master Spreadsheet. When this situation is achieved by Google Apps Script, as the general method, the values are required to be retrieved from each Spreadsheet in a loop. In the current stage, even when Sheets API is used, the values cannot be retrieved from multiple Spreadsheets by one API call. In this report, I would like to introduce the method for achieving this with the low process cost using Google Apps Script.

Flow

  1. Create the URL list for exporting the values from Spreadsheets.
    • In the current stage, when Sheets API is used in a loop, an error occurs. So, in this workaround, I use the URL for exporting Spreadsheet as CSV data. In this case, it seems that even when this URL is accessed with a loop, no error occurs.
  2. Retrieve CSV values from the URLs using UrlFetchApp.fetchAll.
    • fetchAll method works with the asynchronous process. Ref
  3. Merge the retrieved values by parsing CSV data as an array.
  4. Put the values to the master Spreadsheet using Sheets API.

Usage

1. Prepare sample Spreadsheets

Please prepare multiple Spreadsheets in a specific folder. In this sample, the values are retrieved from the 1st sheet.

2. Sample script

In this script, Drive API and Sheets API is used. So, please enable Drive API and Sheets API at Advanced Google services.

In this sample, the values are retrieved from the 1st sheet in each Spreadsheet. Please be careful about this.

function myFunction() {
  const masterSpreadsheetId = "##"; // Please set the Spreadsheet ID of the master Spreadsheet.
  const sheetName = "Sheet1"; // Please set the sheet name you want to put the values.
  const folderId = "###"; // Please set the folder ID of the folder including the sample Spreadsheets.

  const workers = 100;
  const ssIds = Drive.Files.list({
    q: `'${folderId}' in parents and mimeType='${MimeType.GOOGLE_SHEETS}' and trashed=false`,
    fields: "items(id)",
  }).items.map(({ id }) => id);
  const headers = { authorization: "Bearer " + ScriptApp.getOAuthToken() };
  const reqs = [...Array(Math.ceil(ssIds.length / workers))].map((_) =>
    ssIds.splice(0, workers).map((id) => ({
      url: `https://docs.google.com/spreadsheets/export?exportFormat=csv&id=${id}`,
      headers,
      muteHttpExceptions: true,
    }))
  );
  const values = reqs.flatMap((r) => {
    return UrlFetchApp.fetchAll(r).flatMap((rr) => {
      if (rr.getResponseCode() == 200) {
        const [, ...val] = Utilities.parseCsv(rr.getContentText());
        return val;
      }
      return [];
    });
  });
  Sheets.Spreadsheets.Values.update(
    { values },
    masterSpreadsheetId,
    sheetName,
    { valueInputOption: "USER_ENTERED" }
  );
}

  • When this script is run,

    1. Spreadsheet IDs are retrieved from the specific folder.
    2. Values are retrieved from all Spreadsheets.
      • In this script, the values are retrieved from every 100 Spreadsheets with the asynchronous process. If you increase const workers = 100; to const workers = 200;, the values are retrieved from every 200 Spreadsheets. But, if an error occurs when this value is increased, please adjust the value.
    3. Put values using Sheets API.

3. Testing

When I tested this script with const workers = 100 using 50 Spreadsheets, the processing time was about 10 seconds.

Note

  • If the number of values is large, an error might occur. At that time, please check my report.

  • This is a simple sample script for explaining this method. Please be careful about this. In this sample, the values are retrieved from the 1st sheet in each Spreadsheet. Please be careful about this. If you want to retrieve the values from other sheets, please modify the above script.

  • This sample script retrieves only values. So, if you want to retrieve other values like formulas, cell style, and so on, unfortunately, this script cannot be used.

Reference

 Share!