This is a sample script for retrieving the start and end row numbers of the same values in a column on Google Spreadsheet using Google Apps Script.
There is a case in that I want to retrieve the rows of the same values in a column on Google Spreadsheet using Google Apps Script. In this post, I would like to introduce a simple sample script for achieving this.
When Google APIs are used with googleapis for Python, the client is obtained as follows.
creds = service_account.Credentials.from_service_account_file(service_account_credential_file, scopes=scopes)
service = build("drive", "v3", credentials=creds)
In this case, when the script is run, the access token is retrieved every time. But, the expiration time of the retrieved access token is 1 hour. Here, there might be the case that you want to use the access token until the expiration time. It is considered that effectively using the access token will lead to SDGs. In this post, I would like to introduce a sample script for using the access token until the expiration time.
Today, I discussed with Riël Notermans an issue with the HTML form with the input tab of type="file" with google.script.run. Through this discussion, the reason for this issue could be found. When you use the input tab of type="file" in the HTML form, and you want to send the file content with google.script.run, I thought that this post might be useful for other users. So, I posted it here.
This is a sample script for retrieving the access token from the service account using oauth2client and google-auth with Python.
Use oauth2client.
from oauth2client.service_account import ServiceAccountCredentials
SERVICE_ACCOUNT_FILE = "credentials.json"
SCOPES = ["https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name(SERVICE_ACCOUNT_FILE, scopes=SCOPES)
res = creds.get_access_token()
access_token = res.access_token
print(access_token)
Use google-auth. In the current stage, this method might be general.
from google.oauth2 import service_account
import google.auth.transport.requests
SERVICE_ACCOUNT_FILE = "credentials.json"
SCOPES = ["https://www.googleapis.com/auth/drive"]
creds = service_account.Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)
request = google.auth.transport.requests.Request()
creds.refresh(request)
access_token = creds.token
print(access_token)
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 on the server side, again. In this update, I looked for the logic for retrieving the key value. But, I cannot still find it. So, in this post, I would like to use a workaround discussed in this thread. In this thread, the valid keys are listed in a text file. Using this, I updated the script as follows.
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.
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.
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.
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);
}
context.dispatcher.stores, this script can be used for both the salted base64 and the JSON object.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.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.