tanaikech

Putting Values of All Spreadsheets in Folder to Master Spreadsheet with Low Process cost using Google Apps Script

This is a sample script for putting the values of all Spreadsheets in a folder to the master Spreadsheet with a low process cost using Google Apps Script.

There is a case in that I want to collect the values from multiple Spreadsheets and put the values into the master Spreadsheet. When this situation is achieved by Google Apps Script, as the general method, the values are required to be retrieved from each Spreadsheet in a loop. In the current stage, even when Sheets API is used, the values cannot be retrieved from multiple Spreadsheets by one API call. In this report, I would like to introduce the method for achieving this with the low process cost using Google Apps Script.

Flow

tanaikech

Workaround: Retrieving Hyperlink from Cell of Number Value using Google Apps Script

This is a workaround for retrieving the hyperlink from the cell of a number value using Google Apps Script.

As a sample situation, it supposes that a cell "A1" has a number value like 123, and a hyperlink of https://tanaikech.github.io is set to the cell. In order to retrieve the hyperlink from the cell, it is required to use the methods of getRichTextValue() and getRichTextValues(). But, in the current stage, when the cell value is a number value, when the RichText is retrieved by getRichTextValue(), null is returned. By this, unfortunately, the hyperlink of the cell cannot be retrieved. This has already been reported in the Google issue tracker. Ref

In this post, I would like to introduce a workaround for retrieving hyperlinks from such cells.

Sample script:

tanaikech

January 27, 2023: Decrypting Salted Base64 of finance.yahoo.com using Google Apps Script

This sample script decrypts the salted base64 data of finance.yahoo.com using Google Apps Script.

Recently, it seems that the specification of the key for decrypting the data has been changed at the server side. So. from this script, I updated the script as follows.

Sample script

function myFunction() {

tanaikech

Using OnEdit trigger on Google Spreadsheet Created by Service Account

In the current stage, by the current specification, Google Apps Script cannot be directly run on Google Spreadsheet created by Service Account. But, there is a case in that we want to use the OnEdit trigger on the Spreadsheet that the service account is the owner. In this post, I would like to introduce the method for achieving this.

Recently, I published "Using OnEdit Trigger to Google Spreadsheet by Hiding Google Apps Script from Other Users". Here, this method is used.

In order to test this, please do the following flow.

Flow

tanaikech

Using OnEdit Trigger to Google Spreadsheet by Hiding Google Apps Script from Other Users

This is a method for using OnEdit Trigger to Google Spreadsheet by hiding Google Apps Script from other users.

A sample flow for achieving this is as follows.

Flow

tanaikech

Replacing U+00A0 with U+0020 as Unicode using Google Apps Script

This is a sample script for checking and replacing a character of U+00A0 (no-break space) with U+0020 (space) as Unicode using Google Apps Script.

When I'm seeing the questions on Stackoverflow, I sometimes saw the situation that the script doesn't work while the script is correct. In this case, there is the case that the reason is due to U+00A0 being used as the spaces. When U+00A0 is used as the spaces, Google Apps Script and formulas cannot be correctly run. I thought that when this information is published, it might be useful for a lot of users.

In this post, I would like to introduce how to check and replace U+00A0 with U+0020 using Google Apps Script. Of course, this sample script can be also used with Javascript.

tanaikech

Set Line Space of Paragraph on Google Document using Google Apps Script

This is a sample script for setting the line space of paragraphs on Google Documents using Google Apps Script.

When the line space of a paragraph on Google Documents is manually set, you can do it as follows.

When it is set with Google Apps Script, the following script can be used.

tanaikech

Opening and Closing Google Forms on Time using Google Apps Script

This is a sample script for opening and closing Google Forms on time using Google Apps Script.

In order to test this sample script, please do the following flow.

Usage

tanaikech

January 14, 2023: Decrypting Salted Base64 of finance.yahoo.com using Google Apps Script

This sample script decrypts the salted base64 data of finance.yahoo.com using Google Apps Script.

Recently, it seems that the specification of the key for decrypting the data has been changed at the server side. So. in this post, this post is updated. About this specification, I checked this thread.

Sample script

function myFunction() {

tanaikech

Converting All Pages in PDF File to PNG Images using Google Apps Script

This is a sample script for converting all pages in a PDF file to PNG images using Google Apps Script.

I have already published "Merging Multiple PDF Files as a Single PDF File using Google Apps Script". In this post, it was found that pdf-lib can be used with Google Apps Script. From this, in this post, I would like to propose a sample script for converting all pages in a PDF file to PNG images using Google Apps Script. This cannot be directly achieved with Google Apps Script. So, I thought that this might be useful for users.

Sample script