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.
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.
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.