Skip to content

Instantly share code, notes, and snippets.

Avatar

Kanshi TANAIKE tanaikech

View GitHub Profile
@tanaikech
tanaikech / submit.md
Created February 3, 2023 05:45
Putting Values of All Spreadsheets in Folder to Master Spreadsheet with Low Process cost using Google Apps Script
View submit.md

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
tanaikech / submit.md
Last active January 31, 2023 07:27
Workaround: Retrieving Hyperlink from Cell of Number Value using Google Apps Script
View submit.md

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
tanaikech / submit.md
Last active January 27, 2023 01:42
January 27, 2023: Decrypting Salted Base64 of finance.yahoo.com using Google Apps Script
View submit.md

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
tanaikech / submit.md
Created January 25, 2023 03:03
Using OnEdit trigger on Google Spreadsheet Created by Service Account
View submit.md

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
tanaikech / submit.md
Last active January 25, 2023 00:55
Using OnEdit Trigger to Google Spreadsheet by Hiding Google Apps Script from Other Users
View submit.md

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
tanaikech / submit.md
Created January 23, 2023 00:34
Replacing U+00A0 with U+0020 as Unicode using Google Apps Script
View submit.md

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
tanaikech / submit.md
Created January 20, 2023 05:37
Set Line Space of Paragraph on Google Document using Google Apps Script
View submit.md

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
tanaikech / submit.md
Last active January 18, 2023 05:40
Opening and Closing Google Forms on Time using Google Apps Script
View submit.md

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
tanaikech / submit.md
Created January 14, 2023 01:39
January 14, 2023: Decrypting Salted Base64 of finance.yahoo.com using Google Apps Script
View submit.md

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
tanaikech / submit.md
Last active January 11, 2023 02:42
Converting All Pages in PDF File to PNG Images using Google Apps Script
View submit.md

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