tanaike - Google Apps Script, Gemini API, and Developer Tips

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.

Gists

Introduction

When we use HTML in the Google Apps Script project, in order to show the values from the Google Apps Script side, the HTML template is used. When I used the HTML template with a large value, I understood that the process cost can be reduced by devising a script. In this report, I would like to introduce the process cost of the HTML template using the benchmark.

Gists

This is a sample method for publishing various Google Docs files with the same URL using Google Apps Script.

By updating on May 25, 2022, the content got to be able to be embedded as a full page in the new Google site. Ref In this method, this is used.

Usage

1. Create a Google Docs.

First, as a simple sample, please create a new Google Spreadsheet. And please copy the URL like https://docs.google.com/spreadsheets/d/{spreadsheetId}/edit.

By updating on May 25, 2022, the content got to be able to be embedded as a full page in the new Google site. Ref. This is very good news for me.

For example, with this update, the Web Apps created by Google Apps Script can be published by embedding to the Google site as a full page. By this, the URL of the Google site, and Google Analytics can be used. I have already changed my Web Applications. You can see them at https://tanaikech.github.io/webapps/

Gists

This is a sample script for replacing values in the cells on Google Spreadsheet using Google Apps Script.

Sample situation

The sample situation is as follows.

Sample script

This sample script used Sheets API. So, please enable Sheets API at Advanced Google services.

function myFunction() {
  const obj = {
    sample1: "ab",
    sample2: "cd",
    sample3: "ef",
    sample4: "gh",
    sample5: "ij",
    sample6: "kl",
    sample7: "mn",
    sample8: "op",
    sample9: "qr",
  };
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetId = ss.getSheetByName("Sheet1").getSheetId(); // Please set the sheet name you want to use this script.

  const requests = Object.entries(obj).map(([k, v]) => ({
    findReplace: {
      find: `(${k})`,
      matchCase: true,
      sheetId,
      replacement: `[${v}]`,
    },
  }));
  requests.push({
    findReplace: { find: ",", matchCase: true, sheetId, replacement: "" },
  });
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}

• In this script, one sheet is used. Also, you can use this script to all sheets in a Google Spreadsheet.

Reference

• FindReplaceRequest

Gists

This is a sample script for uploading files to Google Drive with asynchronous process using Python.

Sample script

import aiohttp
import asyncio
import json

folder_id = "###" # Please set the folder ID you want to put.
token = "###" # Please set your access token.
url = "https://www.googleapis.com/upload/drive/v3/files"


async def workers(file):

    async with aiohttp.ClientSession() as session:
        metadata = {"name": file["filename"], "parents": [folder_id]}
        data = aiohttp.FormData()
        data.add_field("metadata", json.dumps(metadata), content_type="application/json; charset=UTF-8")
        data.add_field("file", open(file["path"], "rb"))
        headers = {"Authorization": "Bearer " + token}
        params = {"uploadType": "multipart"}
        async with session.post(url, data=data, params=params, headers=headers) as resp:
            return await resp.json()


async def main():
    # Please set the filenames and the file paths as follows.
    fileList = [
        {"filename": "sample1", "path": "./sample1.png"},
        ,
        ,
        ,
    ]
    works = [asyncio.create_task(workers(e)) for e in fileList]
    res = await asyncio.gather(*works)
    print(res)


asyncio.run(main())

• When this script is run, the files of fileList are uploaded to Google Drive with the asynchronous process.

Note

• This sample supposes that your access token can be used for uploading files to Google Drive using Drive API. Please be careful about this.

Reference

• I answered this sample script for this thread of Stackoverflow

Gists

This is a sample script for parsing JSON data array by expanding the header row using Google Apps Script.

Sample script

function myFunction() {
  const obj = [
    { key1: "value1", key2: "value2", key3: "value3" },
    { key4: "value1", key5: "value2", key6: "value3" },
    { key7: "value1", key8: "value2", key9: "value3" },
  ];

  const headers = Object.keys(
    obj.reduce((o, e) => (Object.keys(e).forEach((k) => (o[k] = true)), o), [])
  );
  const values = [headers, ...obj.map((o) => headers.map((k) => o[k] || null))];

  SpreadsheetApp.getActiveSheet()
    .clearContents()
    .getRange(1, 1, values.length, values[0].length)
    .setValues(values);
}

Testing

When this script is run, the following result is obtained. It is found that the header row is expanded by including all keys.