Gists

This is a report for management of images on Google Spreadsheet using Google Apps Script.

At October 30, 2018, Cass OverGridImage and the method of inserImage have been added to Spreadsheet Service. Ref At January 19, 2022, Class CellImageBuilder and Class CellImage have been added to Spreadsheet Service. Ref By these Classes and methods, the images got to be able to be managed on Google Spreadsheet. But, when the image is used to the various situations, there are the cases that it is required to ingenuity to manage the images. So, in this report, I would like to introduce the management of images on Google Spreadsheet using the sample scripts of Google Apps Script.

Gists

This is a report for the documentation comments for the functions of Google Apps Script.

When the documentation comments for functions of Google Apps Script are considered, you will think JsDoc. At Google Apps Script, a part of JsDoc can be used. But, in this report, I would like to introduce the documentation comments including JsDoc.

Sample situations

Sample 1

For example, when the following sample function is written,

Gists

Introduction

In order to retrieve the values from XML data, when XML data is parsed using Google Apps Script, there are several methods for parsing the data. Class XmlService, which is a built-in Class for managing XML data, might be the first way to come up with it. At Stackoverflow, it is posted questions that XML data is often parsed using Class XmlService. It is considered that Class XmlService is suitable for managing XML data.

Gists

This is a sample script for retrieving the values of dropdown list of the smart chips on Google Document using Google Apps Script.

At August 23, 2021, 3 Classes for retrieving the smart chips have been added to Google Apps Script. But, in the current stage, unfortunately, all values of the smart chips cannot be retrieved by the Classes. For example, the dropdown list of the smart chips cannot be retrieved using the 3 Classes.

Gists

This is a sample script for removing the invalid named range of #REF from Google Spreadsheet using Google Apps Script.

Issue and workaround

For example, there are 2 sheets of “Sheet1” and “Sheet2” in a Google Spreadsheet. A new named range of sample is created for the range of Sheet1!A1, and remove the sheet of “Sheet1”. By this flow, when the named range list is checked by UI on Spreadsheet, sample has #REF. This is the invalid named range.

Gists

This is a sample script for converting A1Notation to GridRange and vice versa using Google Apps Script without any scopes.

A1Notation and GridRange are often used with Sheets API. I have posted a sample script for converting A1Notation to GridRange before. Ref But, in that case, I used the method of Spreadsheet service (SpreadsheetApp). By this, in order to use the script, it is required to authorize the scopes. In this sample script, A1Notation can be converted to GridRange and vice versa with no scopes. Also, this sample script can be used for Javascript and Node.js.

Gists

This is a sample script for shortening a long URL using Firebase Dynamic Links API with Google Apps Script.

IMPORTANT

Before you use this script, please create a new Firebase project and link it to your Google Cloud Platform Project. Ref And, please enable Firebase Dynamic Links API at the API console. And then, please create your API key from your Google Cloud Platform Project.

Sample script

const apiKey = "###"; // Please set your API key.
const longUrl = "###"; // Please set the long URL you want to shorten.
const yourDynamicLinkDomain = "###"; // Please set your dynamic link domain.

const url =
  "https://firebasedynamiclinks.googleapis.com/v1/shortLinks?key=" + apiKey;
const options = {
  payload: JSON.stringify({
    dynamicLinkInfo: {
      dynamicLinkDomain: yourDynamicLinkDomain,
      link: longUrl,
    },
  }),
  contentType: "application/json",
};
const res = UrlFetchApp.fetch(url, options);
const { shortLink } = JSON.parse(res.getContentText());

console.log(shortLink);

• When this script is run, longUrl is shortened.

Note

If the warning of Setup URL patterns to whitelist in the Firebase Dynamic Links console. is shown, please include the URL to the whitelist to “Allowlist URL pattern”. Ref By this, the warning can be removed.

Gists

This is a sample script for protecting the cells of a Spreadsheet by clicking a checkbox using Google Apps Script.

You might have a situation where you want to protect the cells when a user checks a checkbox. This sample script is for achieving this situation. The demonstration of this sample script is as follows.

• This demonstration is for a user. You can see that when the checkbox is checked, the checkbox and the right side of the checkbox are protected.

Gists

This is a sample flow for parsing XML data in Google Apps Script using IMPORTXML. Recently, it seems that ContentService.MimeType.XML has been removed by the Google side. By this, in the current stage, the XML data cannot be directly loaded by the Web Apps URL with IMPORTXML. From this current situation, I would like to introduce a workaround. In this workaround, the XML data in Google Apps Script is parsed by IMPORTXML of the built-in function of Google Spreadsheet. By this workaround, I thought that this will be useful for testing a custom XML data using IMPORTXML.

There is a method of ScriptApp.getService().getUrl() for obtaining the Web Apps URL in Google Apps Script.

• Before the V8 runtime is released, this method had returned the endpoint like https://script.google.com/macros/s/{deploymentId}/exec.

• After the V8 runtime was released, the endpoint of https://script.google.com/macros/s/{deploymentId}/dev was returned.

• Now, it seems that this returns https://script.google.com/macros/s/###/exec.

But, in the current stage, when I access this endpoint, the message of Sorry, unable to open the file at this time. Please check the address and try again. is returned. So, I tried to search the deployment ID of ### from https://script.google.com/macros/s/###/exec returned with current ScriptApp.getService().getUrl(). But, unfortunately, I cannot find the deployment ID. I’m worried that this might be related to the error.