My report has been published on the Blog of Google Cloud.

Blog

Efficient File Management using Batch Requests with Google Apps Script

Twitter

Gist

This is a sample script for retrieving the batch path for using the batch requests using Google Apps Script.

After August 12, 2020, in order to use batch requests, the batch path is required to be used to the endpoint of the batch requests. And, the batch path is sometimes updated. So, when a constant batch path has been continued to be used, this might lead to the reason for an error. In this sample script, the batch path is retrieved from Discovery API. By this, the latest batch path can be always obtained.

BatchRequest was updated to v1.2.0.

• v1.2.0 (September 30, 2022)

  1. A new method of getBatchPath(name, version) was added. After August 12, 2020, in order to use batch requests, the batch path is required to be used to the endpoint of the batch requests. And, the batch path is sometimes updated. So, when a constant batch path has been continued to be used, this might lead to the reason for an error. In this method, the batch path is retrieved from Discovery API. By this, the latest batch path can be always simply obtained from the name of Google API. And, the retrieved batch path can be used in Do(object) and EDo(object) methods.

You can check this at https://github.com/tanaikech/BatchRequest.

Overview

This is a Google Apps Script library for supporting Document service, Docs API, Spreadsheet service, Sheets API, Slides service and Slides API. The aim of this library is to compensate the processes that they services cannot achieve.

DocsServiceApp was updated to v1.2.0

• v1.2.0 (September 29, 2022)

  1. Added a new method of getNamedFunctions(). This method can retrieve the named functions from Google Spreadsheet.

You can see the detail information here https://github.com/tanaikech/DocsServiceApp

Gists

This is a sample script for retrieving the named functions from Google Spreadsheet using Google Apps Script.

Recently, the named functions got to be able to be used in Google Spreadsheet. Ref When several named functions are added, I thought that I wanted to retrieve these functions using a script. But, unfortunately, in the current stage, it seems that there are no built-in methods (SpreadsheetApp and Sheets API) for directly retrieving the named functions. So, I created this sample script.

Gists

This sample script retrieves the cell coordinates of cells with the quote prefix. In Google Spreadsheet, when a single quote is added to the top letter of the cell value, the cell is used as the text value. When we want to search the cells with the quote prefix in Spreadsheet, unfortunately, in the current stage, this cannot be achieved using Spreadsheet service (SpreadsheetApp) and Sheets API. In this method, such cells can be retrieved. The output values are the cell coordinates of the cells with the quote prefix.

Overview

This is a Google Apps Script library for supporting Document service, Docs API, Spreadsheet service, Sheets API, Slides service and Slides API. The aim of this library is to compensate the processes that they services cannot achieve.

DocsServiceApp was updated to v1.1.0

• v1.1.0 (September 28, 2022)

  1. Added a new method of getQuotePrefixCells(). This method can detect the cells with the quote prefix cells.

You can see the detail information here https://github.com/tanaikech/DocsServiceApp

Gists

This is a sample script for showing the specific rows and columns in Google Spreadsheet using Google Apps Script.

When you export a Google Spreadsheet as a PDF file, you might have a case where you want to export the specific rows and columns in a sheet. In this post, I would like to introduce the sample script for achieving this.

Gists

These are sample scripts for removing the vertical borders of a table in Google Document using Google Apps Script.

Unfortunately, in the current stage, only the vertical borders cannot be removed from the table in Google Document using the Google Document service (DocumentApp). I believe that this might be resolved in the future update. But, in the current stage, when Google Docs API is used, this can be achieved. So, Google Docs API can be used as the current workaround. But, I thought that the combination of Google Document service (DocumentApp) and Google Docs API might be a bit complicated. So, I would like to introduce 3 sample scripts.

Gists

This is a sample script for retrying UrlFetchApp of Google Apps Script when an error occurs.

When the HTTP request is run using UrlFetchApp, there is a case that an error occurs in various situations. And, there is a case that when the request is run again, no error occurs. This sample script can automatically retry the requests using Google Apps Script.

Sample script

This is Class RetryFetch.

/**
 * UrlFetchApp is run by retrying when an error occurs.
 */
class RetryFetch {
  /**
   * @param {string} url URL
   * @param {object} params Object
   * @param {number} numberOfRetr Number of retry when an error occurs with the HTTP request.
   * @param {number} waitTime Wait time between the HTTP request.
   * @return {UrlFetchApp.HTTPResponse}
   */
  constructor(url, params = {}, numberOfRetry = 2, waitTime = 3) {
    this.url = url;
    if (!params.muteHttpExceptions) {
      params.muteHttpExceptions = true;
    }
    this.params = params;
    this.numberOfRetry = numberOfRetry;
    this.waitTime = waitTime;
    this.his = [];
  }

  fetch() {
    const res = UrlFetchApp.fetch(this.url, this.params);
    const statusCode = res.getResponseCode();
    this.his.push({ date: new Date(), params: this.params, statusCode });
    if (statusCode != 200 && this.numberOfRetry > 0) {
      console.log(`Status code: ${statusCode}, Retry: ${this.numberOfRetry}`);
      const idx = this.his.length - 1;
      this.his[idx].responseHeader = res.getAllHeaders();
      this.his[idx].error = res.getContentText();
      this.numberOfRetry--;
      Utilities.sleep(this.waitTime * 1000);
      this.fetch();
    } else if (this.numberOfRetry == 0) {
      return null;
    }
    return res;
  }

  /**
   * Return history of fetch requesting in this Class.
   * @return {array} History.
   */
  get history() {
    return this.his;
  }
}

This is a sample script for using Class RetryFetch.