Developers & Practitioners

Efficient File Management using Batch Requests with Google Apps Script

September 29, 2022

Kanshi Tanaike

Google Cloud Innovator Champion

Building batch requests with Google Apps Script

Abstract

Google Drive alone can handle small file management jobs, but for larger batches of files, it can be too much for a simple Drive script to manage. With Google Apps Script, even large batches can be executed within 6 minutes, offering businesses the monetary and time benefits of efficient file management. This report looks at how Google Apps Script improves file management with batch requests, judging its efficacy by measuring the benchmark.

Introduction

When you need to manage small sets of files on Google Apps Script, Drive Service is right for the job. But when there are too many files, the process cost of the script the Drive service creates can be too high.

In the “Batch requests” section of the official Drive API document, we see that a batch can process multiple requests. In fact, the asynchronous process can handle up to one hundred Drive API requests with one API call. This can lead to significant process cost reduction when using batch requests for file management.

The issue is that batch requests aren’t available for synchronous processes run through the Google Apps Script Drive service. This means that these users can’t easily use Drive API for their Google Apps Script batch review, removing the process cost benefit that comes from efficiently managing files during the app development process.

To show how much of a difference batch processing makes, this article will measure the benchmarks involved in efficient file management. I’ve reported various Google Apps Script benchmarks before, but this is the first time I’ve measured benchmarks related to file management.

Creating batch requests for Google Apps Script

To create Google Apps Script batch requests, you need to first build the request body and then send it as “multipart/mixed.” You can find information about Drive API batch requests in the official documentation, but here is a sample script:

/**
 * Create a request body of batch requests and request it.
 * 
 * @param {Object} object Object for creating request body of batch requests.
 * @returns {Object} UrlFetchApp.HTTPResponse
 */
function batchRequests(object) {
  const { batchPath, requests } = object;
  const boundary = "sampleBoundary12345";
  const lb = "\r\n";
  const payload = requests.reduce((r, e, i, a) => {
    r += `Content-Type: application/http${lb}`;
    r += `Content-ID: ${i + 1}${lb}${lb}`;
    r += `${e.method} ${e.endpoint}${lb}`;
    r += e.requestBody ? `Content-Type: application/json; charset=utf-8" ${lb}${lb}` : lb;
    r += e.requestBody ? `${JSON.stringify(e.requestBody)}${lb}` : "";
    r += `--${boundary}${i == a.length - 1 ? "--" : ""}${lb}`;
    return r;
  }, `--${boundary}${lb}`);
  const params = {
    muteHttpExceptions: true,
    method: "post",
    contentType: `multipart/mixed; boundary=${boundary}`,
    headers: { Authorization: "Bearer " + ScriptApp.getOAuthToken() },
    payload,
  };
  return UrlFetchApp.fetch(`https://www.googleapis.com/${batchPath}`, params);
}

The sample “object” value is as follows:

In this sample object, the filename of an existing file is changed. In the case of Drive API v3, "batchPath" would be "batch/drive/v3". You can learn more about this in the "batchPath" entry from the Google API Discovery Service documentation.

It’s important to remember that when the batch requests are used with Drive API, the maximum number of requests which can be included in one batch request is 100. So, if you want to use 150 requests, you’ll have to run this function twice.

Sample scripts

In this section, I would like to introduce the sample batch request scripts used in this function. The sample scripts in this report use Drive API, so to recreate these, you’ll need to enable Drive API at Advanced Google services.

Sample situation 1

In this sample situation, filenames in the specific folder —in this sample, it's Spreadsheet — are modified. Here, the process times were measured by changing the number of files.

Sample script 1 - 1

This sample script uses the Google Drive service.

Sample script 1 - 2

This sample script uses Drive API with the batch requests.

Sample situation 2

In this sample situation, multiple Google Sheets are created to the specific folder, and each Sheet is shared with the specific users with a star. Here, the process times were measured by changing the number of files.

Sample script 2 - 1

This sample script uses Google Drive service and Google Sheets service.

Sample script 2 - 2

This sample script uses Drive API with batch requests.

function sample2B() {
  const n = 10; // As a sample, 10 Spreadsheets are created.
  const folderId = "###"; // Please set the folder ID.
  const emails = ["###", "###"]; // Please set the sharing users.

const parser_ = text => {
    const temp = text.split("--batch");
    const regex = new RegExp("{[\\S\\s]+}", "g");
    return temp.slice(1, temp.length - 1).map(e => regex.test(e) ? JSON.parse(e.match(regex)[0]) : e);
  };

// Create new Spreadsheets.
  const requests1 = [];
  for (let i = 0; i < n; i++) {
    const name = `sample${i + 1}`;
    requests1.push({
      method: "POST",
      endpoint: "https://www.googleapis.com/drive/v3/files",
      requestBody: { name, parents: [folderId], mimeType: MimeType.GOOGLE_SHEETS, starred: true },
    });
  }
  const object1 = { batchPath: "batch/drive/v3", requests: requests1 };
  const res1 = batchRequests(object1);
  const text1 = res1.getContentText();
  if (res1.getResponseCode() != 200) {
    throw new Error(text1);
  }

// Create permissions.
  const requests2 = parser_(text1).flatMap(({ id }) =>
    emails.map(emailAddress => ({
      method: "POST",
      endpoint: `https://www.googleapis.com/drive/v3/files/${id}/permissions`,
      requestBody: { role: "writer", type: "user", emailAddress }
    }))
  );
  const object2 = { batchPath: "batch/drive/v3", requests: requests2 };
  const res2 = batchRequests(object2);
  if (res2.getResponseCode() != 200) {
    throw new Error(res2.getContentText());
  }
}

Results and discussions

For the above 2 sample situations, the results are as follows:

https://storage.googleapis.com/gweb-cloudblog-publish/images/Kanshi_image_1.max-1000x1000.png

Fig1. Process costs for renaming files with and without batch requests. Blue and red lines are with and without batch requests, respectively.

https://storage.googleapis.com/gweb-cloudblog-publish/images/Kanshi_Image_2.max-1000x1000.png

Fig2. Process costs for creating and sharing Sheets with and without batch requests. Blue and red lines are with and without batch requests, respectively.

From both results, when the batch requests are used for managing files on Google Drive, the process cost can be reduced. In Fig. 1, only the file metadata is modified, meaning the process cost for renaming the filename is small. For Fig. 2, the process cost for creating the Sheet is high. From this, we see that the process cost for Fig. 2 is higher than that of Fig. 1.

Summary

The result of the benchmarks shows that when batch requests are used for managing files on Google Drive, the process cost can be reduced.

Batch requests can be used for not only Drive API, but also other APIs, including Calendar API, Gmail API, Directory API, and Cloud Storage. For example, when you use batch requests with Calendar API, you can reduce the process cost for creating and updating events.

Developers & Practitioners

Customize Google Workspace with Add-ons

Google provides a tool that allows you to customize Google Workspace for exactly the situation I need: Google Apps Script.

By Alice Keeler • 6-minute read

Developers & Practitioners

Hidden gems of Google BigQuery

Read on to learn about BigQuery features I did not know about until recently. Once I discovered them, I loved them immediately. I hope you will love them too.

By Artem Nikulchenko • 4-minute read