Gists
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.
Gists
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
My report has been published at Champion Innovators Content Library.
If these reports will be useful, I’m glad.
Gists
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() {
// Load crypto-js.min.js.
const cdnjs =
"https://cdnjs.cloudflare.com/ajax/libs/crypto-js/4.1.1/crypto-js.min.js";
eval(UrlFetchApp.fetch(cdnjs).getContentText());
// Retrieve HTML and retrieve salted base64.
const url = "https://finance.yahoo.com/quote/PGEN/press-releases"; // This is a sample URL.
const html = UrlFetchApp.fetch(url)
.getContentText()
.match(/root.App.main = ([\s\S\w]+?);\n/);
if (!html || html.length == 1) return;
const tempObj = JSON.parse(html[1].trim());
let obj;
if (
typeof tempObj.context.dispatcher.stores === "string" ||
tempObj.context.dispatcher.stores instanceof String
) {
// Decrypt the salted base64.
const key = [
...new Map(
Object.entries(tempObj)
.filter(([k]) => !["context", "plugins"].includes(k))
.splice(-4)
).values(),
].join("");
if (!key) {
throw new Error(
"Specification at the server side might be changed. Please check it."
);
}
obj = JSON.parse(
CryptoJS.enc.Utf8.stringify(
CryptoJS.AES.decrypt(tempObj.context.dispatcher.stores, key)
)
);
} else {
obj = tempObj.context.dispatcher.stores;
}
console.log(obj);
}
- About the value of
context.dispatcher.stores, this script can be used for both the salted base64 and the JSON object.
Note
- In this sample, in order to load
crypto-js, eval(UrlFetchApp.fetch(cdnjs).getContentText()) is used. But, if you don’t want to use it, you can also use this script by copying and pasting the script of https://cdnjs.cloudflare.com/ajax/libs/crypto-js/4.1.1/crypto-js.min.js to the script editor of Google Apps Script. By this, the process cost can be reduced.
IMPORTANT
- I can confirm that this method can be used for the current situation (January 27, 2023). But, when the specification in the data and HTML is changed in the future update on the server side, this script might not be able to be used. Please be careful about this.
References
Gists
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.
Gists
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
1. Create a new Google Spreadsheet.
Please create a new Google Spreadsheet. In this flow, this Google Spreadsheet is used for testing the script. And, please copy the Spreadsheet ID. This spreadsheet ID is used.
In this case, even when Spreadsheet has no container-bound script, this goal can be achieved. Only the below standalone script can be used.
Gists
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.
Gists
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.
function sample1() {
const doc = DocumentApp.getActiveDocument();
const body = doc.getBody();
const paragraph = body.appendParagraph(
"sample paragraph 1\nsample paragraph 2\nsample paragraph 3"
);
paragraph.setLineSpacing(2); // Double
}
When this script is run, the appended paragraphs have a line space of 2 (Double).
Gists
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
Please create a new Google Form and set your sample questions. And, please open the script editor of Google Form.
2. Prepare sample script.
Please copy and paste the following script to the script editor of Google Form. And, please set the values of start and end times you want.
Gists
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() {
// Load crypto-js.min.js.
const cdnjs =
"https://cdnjs.cloudflare.com/ajax/libs/crypto-js/4.1.1/crypto-js.min.js";
eval(UrlFetchApp.fetch(cdnjs).getContentText());
// Retrieve HTML and retrieve salted base64.
const url = "https://finance.yahoo.com/quote/PGEN/press-releases"; // This is a sample URL.
const html = UrlFetchApp.fetch(url)
.getContentText()
.match(/root.App.main = ([\s\S\w]+?);\n/);
if (!html || html.length == 1) return;
const tempObj = JSON.parse(html[1].trim());
let obj;
if (
typeof tempObj.context.dispatcher.stores === "string" ||
tempObj.context.dispatcher.stores instanceof String
) {
// Decrypt the salted base64.
var key = Object.entries(tempObj).find(
([k]) => !["context", "plugins"].includes(k)
)[1];
if (!key) {
throw new Error(
"Specification at the server side might be changed. Please check it."
);
}
obj = JSON.parse(
CryptoJS.enc.Utf8.stringify(
CryptoJS.AES.decrypt(tempObj.context.dispatcher.stores, key)
)
);
} else {
obj = tempObj.context.dispatcher.stores;
}
console.log(obj);
}
- About the value of
context.dispatcher.stores, this script can be used for both the salted base64 and the JSON object.
Note
- In this sample, in order to load
crypto-js, eval(UrlFetchApp.fetch(cdnjs).getContentText()) is used. But, if you don’t want to use it, you can also use this script by copying and pasting the script of https://cdnjs.cloudflare.com/ajax/libs/crypto-js/4.1.1/crypto-js.min.js to the script editor of Google Apps Script. By this, the process cost can be reduced.
IMPORTANT
- I can confirm that this method can be used for the current situation (January 14, 2023). But, when the specification in the data and HTML is changed in the future update on the server side, this script might not be able to be used. Please be careful about this.
References