HtmlFormApp was updated to v1.0.1.

• v1.0.1 (May 29, 2023)

  1. When multiple files are uploaded, each URL was set as the hyperlink.

You can see the detail information here https://github.com/tanaikech/HtmlFormApp

Gists

Abstract

In this report, the detailed specification of PropertiesService has been investigated. It is considered that knowing this specification will be useful for developing applications with Google Apps Script. As a result, it was found that the maximum key and value sizes are 524,287 bytes with a 1-byte key and 8,066 bytes, respectively. And also, it was found that the maximum size of PropertiesService is required to be considered with both the key and value sizes.

My report has been published at Champion Innovators Content Library and Google Cloud Medium publication.

• Exporting Binary Data with Batch Requests using Google Apps Script

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

Gists

This is a sample script for converting Google Spreadsheet to an HTML table using Google Apps Script.

There is the case that it is required to convert a sheet in a Google Spreadsheet to an HTML table. For example, there might be a situation that a sheet in a Google Spreadsheet is sent as an email including an HTML table. And, there might be a situation in which a sheet in a Google Spreadsheet is published to an HTML page including the converted HTML table. I have published the method for achieving this before. Ref But, in that case, the column width, the row height, merged cells, and the images in the cells couldn’t be used. When those are included in the script, the script becomes complicated. So, I couldn’t include it. But, recently, I have come up with a simple method for achieving this. In this post. I would like to introduce a sample script for converting a sheet in a Google Spreadsheet to HTML.

My report has been published at Champion Innovators Content Library and Google Cloud Medium publication.

• Best Practices for Discontinuous Cells on Google Spreadsheet by Google Apps Script

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

Overview

This is a Google Apps Script library including useful scripts for supporting to development of applications by Google Apps Script. In the current stage, the 3 categories “For array processing”, “For binary processing”, and “For string processing” are included in this library.

Description

When I create applications using Google Apps Script, there are useful scripts for often use. At that time, I thought that when those scripts can be simply used, they will be useful not only to me but also to other users. From this motivation, I created a Google Apps Script library including those scripts. But, I have been using these useful scripts only in my development before.

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.