Gists

This is a sample script for converting the values on Google Spreadsheet as unpivot (reverse pivot) using Google Apps Script.

Sample script 1:

const SAMPLE1 = ([[, ...header], ...values]) =>
  header.flatMap((h, i) => values.map((t) => [h, t[0], t[i + 1]]));

• In the sample, the source data is “A1:D8”. And, a custom function of =SAMPLE1(A1:D8) is put into “F1”.
• When this script is used, the result showing the above image (most left table to middle table) is obtained.

Sample script 2:

const SAMPLE2 = v => {
  const [a, b, c] = v[0].map((_, c) => v.map(r => r[c]));
  const ch = [...new Set(a)];
  const rh = [...new Set(b)];
  const size = rh.length;
  const values = [...Array(Math.ceil(c.length / size))].map(_ => c.splice(0, size));
  const temp = [[null, ...rh], ...values.map((vv, i) => [ch[i], ...vv])];
  return temp[0].map((_, c) => temp.map(r => r[c]));
}

• In the sample, the source data is “F1:H21”. And, a custom function of =SAMPLE2(F1:H21) is put into “J1”.
• When this script is used, the result showing the above image (middle table to most right table) is obtained.

Gists

Abstract

It has already been known that Google Apps Script is a strong tool for managing Google Spreadsheets. When the values are retrieved and/or put for Google Spreadsheet, there is a case that the discontinuous cells are required to be used. This report suggests the Best Practices for processing the discontinuous cells on Google Spreadsheet. From the results of process costs, it could understand the usefulness of using the discontinuous cells with low cost using Sheets API and Class RangeList of Spreadsheet service with Google Apps Script.

My report has been published at Champion Innovators Content Library.

• Enriched Management of Rich Text on Google Spreadsheet using Google Apps Script

If these reports will be useful, I’m glad.

Gists

These are sample scripts for putting an image into a cell of a Spreadsheet using Google Apps Script.

Sample 1

In this sample, the image is put into a cell using thumbnailLink retrieved by Drive API. So, when you test this, please enable Drive API at Advanced Google services. The image is put into cell “A1”.

function sample1() {
  const fileId = "###"; // Please set the file ID of the PNG image file on Google Drive.

  const url = Drive.Files.get(fileId).thumbnailLink.replace("=s220", "=s1000");
  const image = SpreadsheetApp.newCellImage().setSourceUrl(url).build();
  const range = SpreadsheetApp.getActiveSheet().getRange("A1");
  range.setValue(image);

  const value = range.getValue();
  console.log(value.getUrl()); // ---> null
  console.log(value.getContentUrl()); // --> Exception: Unexpected error while getting the method or property getContentUrl on object SpreadsheetApp.CellImage.
}

Sample 2

In this sample, the image is put into a cell using the data URL. The image is put into cell “A1”. In this case, I believe that when the data URL is used, this method will be able to be used for various situations.

Gists

This is a workaround for automatically installing the OnEdit trigger to the copied Google Spreadsheet using Google Apps Script.

The sample situation for this workaround is as follows.

• You have a Google Spreadsheet.
• Your Spreadsheet is shared with a user as the writer.
• Your Spreadsheet has a button for executing a script for copying the active Spreadsheet.
• Your Spreadsheet has a function installedOnEdit for executing by the installable OnEdit trigger.
• You want to make the user copy the active Spreadsheet by clicking the button, and also, you want to automatically install the OnEdit trigger to installedOnEdit for the copied Spreadsheet, simultaneously.

This method is from “Using OnEdit Trigger to Google Spreadsheet by Hiding Google Apps Script from Other Users (Author: me)” and “Using OnEdit trigger on Google Spreadsheet Created by Service Account (Author: me)”.

• v2.0.3 (April 5, 2023)

  1. Forgot to update the version number and modified it. And, built the sources with the latest version. Ref

The detail information and how to get this are https://github.com/tanaikech/goodls.

Gists

In this post, I would like to introduce a sample script for putting Time-based One-time Password (TOTP) value into Google Spreadsheet using Google Apps Script.

In this sample script, I used a Javascript library of https://github.com/hectorm/otpauth . In the current stage, Google Apps Script can run with V8 runtime. By this, it seems that this library can be used with Google Apps Script.

Gists

In the current stage, Google Spreadsheet can use rich texts in cells. The rich texts can be also managed by Google Apps Script. But, I thought that creating a script for editing the existing rich text in the cell might be a bit complicated. Because, for example, in the current stage, when the text of the rich text of a cell is changed using a script, all text styles are cleared. In order to add and delete a text for the rich text in a cell, it is required to create a script while the current text style is kept. This is actually complicated. In this post, I would like to introduce the enriched management of rich text on Google Spreadsheet using Google Apps Script. In order to enrich the management of Rich Text using Google Apps Script, I created a library RichTextAssistant.

Overview

This is a GAS library for supporting editing RichText in Google Spreadsheet using Google Apps Script.

Description

There is RichTextApp in my published libraries. RichTextApp can be used mainly for converting RichText to Google Documents and vice versa. This library RichTextAssistant will support editing the rich text in Google Spreadsheets using Google Apps Script. Google Spreadsheet can use rich text as the cell value using Google Apps Script. But, I thought that when I created a script for editing the existing rich text in the cell, it might be a bit complicated. Because, for example, in the current stage, when the text of rich text of a cell is changed using a script, all text styles are cleared. In order to add and delete a text for the rich text in a cell, it is required to create a script while the current text style is kept. This is actually complicated. From this situation, when a script for supporting editing the rich text in a cell is published, it will be useful for a lot of users. So, I created it and published it as “RichTextAssistant” of a Google Apps Script library.

Gists

This is a sample script for the folder picker using jsTree with Google Apps Script and Javascript.

I have already published “File Picker using Google Apps Script and Javascript without 3rd party”. In this post, jsTree is used.

Usage

1. Install Google Apps Script library.

In this script, “FilesApp” of my Google Apps Script library is used. So, please install it. You can see how to install it at here.