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

Gists

This is a sample script for creating the user’s dashboard by inputting the user name and password using Web Apps with Google Apps Script. In this case, Google Spreadsheet is used as a database of the dashboard.

Usage

1. Create a Google Apps Script project.

In order to use Web Apps, please create a new Google Apps Script project. In this case, please create a new Spreadsheet and open the script editor of Spreadsheet.

Gists

This is a sample script for retrieving the names of month and day of week using Google Apps Script.

I think that you might have a case that you want to retrieve the names of month and day of week using Google Apps Script. This sample script retrieves them using a simple script.

Using Utilities.formatDate of Google Apps Script, the names of month and day of week can be retrieved using a simple script.

Gists

This is a sample script for checking the exchange rate using GOOGLEFINANCE with Google Apps Script.

Recently, I have published a report of “Report: Obtaining Values from GOOGLEFINANCE using Google Apps Script”. Ref In this post, I would like to introduce a sample script for checking the exchange rate using Google Apps Script.

Sample script

Please copy and paste the following script to the script editor of Spreadsheet.

// When this script is run, a trigger for executing "checkCurrency" function is installed.
function setTrigger() {
  const functionName = "checkCurrency";
  const trigger = ScriptApp.getScriptTriggers().find(t => t.getHandlerFunction() == functionName);
  if (trigger) {
    ScriptApp.deleteTrigger(trigger);
  }
  ScriptApp.newTrigger(functionName).timeBased().everyMinutes(10).create();
}

function checkCurrency() {
  const threshold = ###; // Please set the the threshold value you want to check.
  const ticker = "CURRENCY:USDJPY"; // Please set ticker you want to check.
  const email = "###"; // Please set the email address you want to notice.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  const range = sheet.getRange(sheet.getLastRow() + 1, 1, 1, 2);
  range.setFormulas([["=NOW()", `=GOOGLEFINANCE("${ticker}")`]]);
  range.offset(0, 0, 1, 1).setNumberFormat("yyyy/MM/dd H:mm:ss");
  SpreadsheetApp.flush();
  const values = range.getValues();
  range.setValues(values);
  if (values[0][1] > threshold) { // Or if you want to check when the current value is less than the threshold, please modify this to values[0][1] < threshold
    MailApp.sendEmail({
      to: email,
      subject: `Report: Value is over the threshold (${threshold})`,
      htmlBody: `<p>Current value is ${values[0][1].toFixed(2)}.</p><p><a href="${ss.getUrl()}">Open Spreadsheet</a>`,
    });
  }
}

• In this script, when the function of checkCurrency is run, the current value of exchange rate is retrieved as a fixed value without using the formula. When the value is over threshold, an email is sent.

Gists

This is a sample script for replacing images on Google Document in order using Google Apps Script.

Sample script

This sample script uses Drive API and Docs API. So, please enable Drive API and Docs API at Advanced Google services. Ref

In this sample script, the images on Google Document are replaced with the image files on your Google Drive in order. Each image in Document is replaced in order of file Ids in fileIds.

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.