v1.0.12 (December 12, 2025)
You can see the detail information here https://github.com/tanaikech/UtlApp
The Gemini API recently introduced the URL context tool, a feature designed to allow the model to directly fetch and utilize content from specified URLs to ground its responses. Ref
This report provides a practical demonstration of this tool’s capabilities. We will investigate its impact on two critical aspects of AI model interaction: the accuracy of the generated response and the total token consumption, which directly affects API costs.
v1.0.11 (June 14, 2025)
You can see the detail information here https://github.com/tanaikech/UtlApp
v1.0.10 (June 13, 2025)
You can see the detail information here https://github.com/tanaikech/UtlApp
v1.0.9 (June 13, 2025)
You can see the detail information here https://github.com/tanaikech/UtlApp
This report showcases a practical application of Google Apps Script, demonstrating how new JavaScript methods can be used to create a script that automatically transfers selected rows between sheets in a Google Sheet.
JavaScript, a fundamental pillar of contemporary web development, has experienced a significant rise in popularity due to its versatility and widespread adoption. As JavaScript’s influence has expanded, so too has Google Apps Script, a cloud-based scripting language constructed on the V8 JavaScript engine. This evolution has led to the introduction of novel methods and features, thereby expanding the capabilities of developers working within the Google Workspace ecosystem.
v1.0.7 (September 4, 2024)
You can see the detail information here https://github.com/tanaikech/UtlApp
This sample script demonstrates uploading multiple files using split asynchronous processes with resumable upload. It leverages JavaScript and HTML within Google Spreadsheets.
In my previous report, “Resumable Upload of Multiple Files with Asynchronous Process for Google Drive”, I presented an approach for uploading files asynchronously.
This script builds upon that concept, introducing a method for uploading multiple files with split asynchronous processes that utilize resumable upload.
Here’s the process breakdown:
v1.0.3 (June 26, 2024)
You can see the detail information here https://github.com/tanaikech/TriggerApp
ResumableUploadForGoogleDrive_js was updated to v2.0.2.
v2.0.2 (May 23, 2024)
This Class can achieve the resumable upload of a file by reading the file to the memory.
<script src="https://cdn.jsdelivr.net/gh/tanaikech/ResumableUploadForGoogleDrive_js@2.0.2/resumableupload_js.min.js"></script>
This Class can achieve the resumable upload of a file by directly reading partially the file from the local Disk. By this, the large file which is over the memory of the local PC can be uploaded.
v1.0.7 (May 15, 2024)
x instead of “left”, “center”, and “right”, the inputted number is directly used.You can see the detail information here https://github.com/tanaikech/PDFApp
v1.0.6 (May 15, 2024)
You can see the detail information here https://github.com/tanaikech/PDFApp
This is a simple sample script for adding the page numbers to PDF data using Google Apps Script.
When you use this script, please copy and paste the following script to the script editor of Google Apps Script. And, please set the file ID of the PDF file.
In this script, pdf-lib is used.
/**
* ### Description
* Add page numbers to PDF.
*
* @param {Object} blob PDF blob.
* @param {Object} pageFormat Format of page number.
* @returns {Blob} Updated PDF blob.
*/
async function addPageNumbers_(blob, pageFormat) {
if (blob.getContentType() != MimeType.PDF) {
throw new Error("Blob is not PDF.");
}
// Load pdf-lib
const cdnjs = "https://cdn.jsdelivr.net/npm/pdf-lib/dist/pdf-lib.min.js";
eval(
UrlFetchApp.fetch(cdnjs)
.getContentText()
.replace(
/setTimeout\(.*?,.*?(\d*?)\)/g,
"Utilities.sleep($1);return t();"
)
);
const data = new Uint8Array(blob.getBytes());
const pdfData = await PDFLib.PDFDocument.load(data);
const pdfDoc = await PDFLib.PDFDocument.create();
(await pdfDoc.copyPages(pdfData, pdfData.getPageIndices())).forEach(
(page, i) => {
const { width } = page.getSize();
const obj = { center: width / 2, left: 20, right: width - 20 };
const pageFormatObj = { ...pageFormat };
pageFormatObj.x = obj[pageFormat.x];
page.drawText(`${i + 1}`, pageFormatObj);
pdfDoc.addPage(page);
}
);
const bytes = await pdfDoc.save();
return Utilities.newBlob(
[...new Int8Array(bytes)],
MimeType.PDF,
`new_${blob.getName()}`
);
}
// Please run this function.
function sample1() {
const fileId = "###"; // Please set the file ID of the PDF file.
const pdfBlob = DriveApp.getFileById(fileId).getBlob(); // Of course, you can directly give the PDF blob.
const pageFormat = { size: 10, x: "center", y: 10 };
addPageNumbers_(pdfBlob, pageFormat).then((newBlob) =>
DriveApp.createFile(newBlob)
);
}
// This function is a simple demonstration script.
function sample2() {
// Create a sample Google Document.
const tempDoc = DocumentApp.create("tempDoc");
const body = tempDoc.getBody();
for (let p = 0; p < 5; p++) {
body.appendParagraph(`sample text ${p + 1}`).appendPageBreak();
}
tempDoc.saveAndClose();
const pdfBlob = tempDoc.getBlob();
// Add page numbers.
const pageFormat = { size: 10, x: "center", y: 10 };
addPageNumbers_(pdfBlob, pageFormat).then((newBlob) =>
DriveApp.createFile(newBlob)
);
}
sample1, the page numbers are added to the center of each page.sample2, a new Google Document is created including 5 pages. And, the page numbers are added to the center of each page.{ size: 10, x: "center", y: 10 } is used for the page numbers. Here, the page numbers are put to only “left”, “center”, and “right” of the bottom of the page. But, there are various parameters in DrawTextOptions. Ref So, when you want to customize more, please modify the script.
v1.0.6 (May 4, 2024)
You can see the detail information here https://github.com/tanaikech/UtlApp
v1.0.5 (April 16, 2024)
You can see the detail information here https://github.com/tanaikech/UtlApp
v1.0.4 (April 13, 2024)
You can see the detail information here https://github.com/tanaikech/UtlApp
v1.0.5 (February 5, 2024)
You can see the detail information here https://github.com/tanaikech/PDFApp
v1.0.4 (February 5, 2024)
copyPages.You can see the detail information here https://github.com/tanaikech/PDFApp
v1.0.3 (February 5, 2024)
You can see the detail information here https://github.com/tanaikech/UtlApp
v1.0.2 (January 25, 2024)
You can see the detail information here https://github.com/tanaikech/UtlApp
v1.0.3 (November 26, 2023)
mergePDFs. The method for using mergePDFs and the output are not changed. With this modification, the large PDF data can be merged.You can see the detail information here https://github.com/tanaikech/PDFApp
One day, you might have a situation where you are required to create a Web Apps with Google Apps Script and are required to load another HTML created by Javascript on the Web Apps. This report will help achieve such a situation.
Google Apps Script can create Web Apps. Ref When you access the Web Apps using your browser, you can see the HTML. When your browser can run Javascript, you can see the HTML reflecting the Javascript. The Web Apps created by Google Apps Script is one of the important and useful cloud applications. About the Web Apps, you have a situation where it is required to insert another HTML to the current HTML. And, another HTML might be required to be created by Javascript including the HTML. However, it is difficult a little to find detailed information about this. This report introduces a simple sample script for achieving such a situation.
This is a Google Apps Script library for managing the histories of the Google Apps Script project.
On August 23, 2023, the project history has been implemented in the new IDE of Google Apps Script. Ref and Ref In the current stage, the users can see the history of the previously deployed script version. This is a very important implementation for a lot of developers.
Here, I remember the classic IDE. In the case of the classic IDE, the users had been able to see the previously saved script version regardless of the deployment and just the save of the script. This is not implemented in the new IDE. I would like to expect that this will be implemented in the future update.
At the Google Apps Script project, the values can be transferred from HTML to Google Apps Script using google.script.run with Javascript. In this case, unfortunately, the values of all types cannot be transferred. In the official document, it says as follows. Ref
Most types are legal, but not Date, Function, or DOM element besides form; see description
Legal parameters are JavaScript primitives like a Number, Boolean, String, or null, as well as JavaScript objects and arrays that are composed of primitives, objects, and arrays. A form element within the page is also legal as a parameter, but it must be the function’s only parameter. Requests fail if you attempt to pass a Date, Function, DOM element besides a form, or other prohibited type, including prohibited types inside objects or arrays. Objects that create circular references will also fail, and undefined fields within arrays become null. Note that an object passed to the server becomes a copy of the original. If a server function receives an object and changes its properties, the properties on the client are not affected.
v1.0.2 (August 21, 2023)
insertHeaderFooter was added. Ref When this method is used, the custom header and footer can be added when a Google Spreadsheet is exported as PDF.You can see the detail information here https://github.com/tanaikech/PDFApp
This is a sample script for adding header and footer to PDF using Google Apps Script.
In the current stage, when Google Spreadsheet is manually exported as a PDF file at “Print settings” on the UI of Spreadsheet, the custom header and footer can be added as shown in the following image.
But, unfortunately, in the current stage, this cannot be directly achieved by Google Apps Script. So, I created this sample script. This sample script uses pdf-lib of Javasscript library.
v1.0.1 (August 18, 2023)
pageInfo is added to the retrieved metadata. By this, each page size can be obtained.You can see the detail information here https://github.com/tanaikech/PDFApp
v1.0.1 (August 18, 2023)
toDay is not used, there was a case that the next trigger is not installed. This bug was removed.You can see the detail information here https://github.com/tanaikech/TriggerApp
This is a Google Apps Script library for managing PDFs.
Google Apps Script is one of the most powerful tools for cloud computing. When Google Apps Script is used, the result can be obtained even when the user doesn’t stay in front of the PC and mobile phone by the triggers. One day, there might be a case where it is required to manage PDF data using Google Apps Script. The combination of Google Docs (Document, Spreadsheet, and Slide) and PDFs is useful for various situations. However, unfortunately, there are no built-in methods for directly managing PDFs using Google Apps Script. Fortunately, it seems that pdf-lib of the Javascript library can be used with Google Apps Script. By this, PDF data can be managed with Google Apps Script using this library. This Google Apps Script library manages PDFs by using it as a wrapper between Google Apps Script and pdf-lib.
When PDF file can be managed with Google Apps Script, that will lead to the automation process on cloud. In this report, the method for cooking PDF over Google Apps Script.
Google Apps Script is one of the strong tools for achieving the automation process. When Google Apps Script can be used for the situation, it can be processed with cloud computing. By this, the users are not required to stay on the desks with the PC. One day, there might be a case where you are required to manage PDF files using Google Apps Script. When PDF files can be managed with Google Apps Script, that will be very important for achieving the automation process. Unfortunately, there are no built-in methods for directly managing PDF data using Google Apps Script. Fortunately, after the V8 runtime has been released, several raw Javascript libraries could be used with Google Apps Script. pdf-lib is also one of them. When this is used, PDF data can be cooked over Google Apps Script. In this report, I would like to introduce achieving this using a Google Apps Script library.
This is a sample script for embedding the objects in PDF using Google Apps Script.
Recently, I had a situation where it is required to manage PDFs using Google Apps Script. At that time, I had a situation where it is required to embed objects of texts and images in PDF using Google Apps Script. So, I created the following Class with Google Apps Script. When this Class is used, the objects of texts and images can embed in PDF.
This is a sample script for creating PDF forms from a Google Slide template using Google Apps Script.
Recently, I had a situation where it is required to create a custom PDF form. In that case, I thought that when a PDF form can be created from a template, it might be useful. So, I created the following Class with Google Apps Script. When this Class is used, a custom PDF form can be easily created from a Google Slide as a template.
This is a sample script for retrieving and putting values for PDF Forms using Google Apps Script.
PDF can have the PDF Form for inputting the values in the PDF by the user. Ref Recently, I had a situation that required me to retrieve and put the values to the PDF Form using Google Apps Script. In order to achieve this, I created a Class object with Google Apps Script. That is as follows.
This is a sample script for changing the order of pages in a PDF file using Google Apps Script.
Before you run this script, please set the variables in the function main.
/**
* ### Description
* Changing order of pages in a PDF file.
*
* @param {Object} fileId is file ID of PDF file. newOrderOfpages is new order of pages. About "ignoreSkippedPages", if this is false, when the PDF has 5 pages and "newOrderOfpages" is "[3, 2]", the exported PDF file has 5 pages of 3, 2, 1, 4, 5. If this is true, when the PDF has 5 pages and "newOrderOfpages" is "[3, 2]", the exported PDF file has only 2 pages of 3 and 2.
* @return {void}
*/
async function changeOrderOfPDFPages_({
fileId,
newOrderOfpages,
ignoreSkippedPages,
}) {
// Load pdf-lib
const cdnjs = "https://cdn.jsdelivr.net/npm/pdf-lib/dist/pdf-lib.min.js";
eval(UrlFetchApp.fetch(cdnjs).getContentText()); // Load pdf-lib
const setTimeout = function (f, t) {
Utilities.sleep(t);
return f();
};
const blob = DriveApp.getFileById(fileId).getBlob();
const pdfData = await PDFLib.PDFDocument.load(
new Uint8Array(blob.getBytes())
);
const numberOfPages = pdfData.getPageCount();
const maxPage = Math.max(...newOrderOfpages);
if (numberOfPages < maxPage || numberOfPages < newOrderOfpages.length) {
throw new Error(
"Maximum page in the order of pages is over than the maximum page of the original PDF file."
);
}
let skippedPages = [];
if (!ignoreSkippedPages && numberOfPages > newOrderOfpages.length) {
skippedPages = [...Array(numberOfPages)]
.map((_, i) => i + 1)
.filter((e) => !newOrderOfpages.includes(e));
}
const pdfDoc = await PDFLib.PDFDocument.create();
const pages = await pdfDoc.copyPages(
pdfData,
[...Array(numberOfPages)].map((_, i) => i)
);
[...newOrderOfpages, ...skippedPages].forEach((e) =>
pdfDoc.addPage(pages[e - 1])
);
const bytes = await pdfDoc.save();
return Utilities.newBlob(
[...new Int8Array(bytes)],
MimeType.PDF,
"sample.pdf"
);
}
function main() {
const fileId = "###"; // Please set a file ID of your a PDF file or a file ID of Google Docs files (Document, Spreadsheet, Slide).
const newOrderOfpages = [3, 1, 2, 5, 4]; // Please set new order of the pages in a PDF file. In this sample, the order of pages of the original PDF file is changed to 3, 1, 2, 5, 4.
const ignoreSkippedPages = false; // If this is false, when the PDF has 5 pages and "newOrderOfpages" is "[3, 2]", the exported PDF file has 5 pages of 3, 2, 1, 4, 5. If this is true, when the PDF has 5 pages and "newOrderOfpages" is "[3, 2]", the exported PDF file has only 2 pages of 3 and 2.
changeOrderOfPDFPages_({ fileId, newOrderOfpages, ignoreSkippedPages }).then(
(blob) => {
DriveApp.createFile(blob.setName("sample.pdf"));
}
);
}
When this script is run, a new PDF file is created with the new order of pages.
This is a sample script for managing the metadata of PDF data using Google Apps Script.
There might be a case in that you want to retrieve and update the metadata of PDF data using Google Apps Script. In this post, I would like to introduce achieving this.
This is a Class ManagePdfMetadata. This Class is used for managing the metadata of PDF files using Google Apps Script. And, in this Class, a Javascript library of pdf-lib is used for managing the PDF metadata. This Javascript library is loaded in this Class.
This is a Google Apps Script library for efficiently managing the time-driven triggers for executing Google Apps Script using Google Apps Script.
Google Apps Script can execute with not only the manual operation but also several triggers. The time-driven trigger is one of them, and this is one of a lot of important functions. When the time-driven trigger is used, Google Apps Script can be automatically executed at the time you set without launching the user’s PC.
This is a sample script for exporting the specific pages from a PDF as a new PDF using Google Apps Script.
In this sample script, pdf-lib is used. In the current stage, it seems that this Javascript can be directly used with Google Apps Script.
async function myFunction() {
// Retrieve PDF data.
const fileId = "###"; // Please set a file ID of your a PDF file or a file ID of Google Docs files (Document, Spreadsheet, Slide).
const pageNumbers = [2, 4]; // In this sample, 2 and 4 pages are exported as a PDF.
const blob = DriveApp.getFileById(fileId).getBlob();
// Merge PDFs.
const cdnjs = "https://cdn.jsdelivr.net/npm/pdf-lib/dist/pdf-lib.min.js";
eval(UrlFetchApp.fetch(cdnjs).getContentText()); // Load pdf-lib
const setTimeout = function (f, t) {
Utilities.sleep(t);
return f();
};
const pdfDoc = await PDFLib.PDFDocument.create();
const pdfData = await PDFLib.PDFDocument.load(
new Uint8Array(blob.getBytes())
);
const pages = await pdfDoc.copyPages(
pdfData,
[...Array(pdfData.getPageCount())].map((_, i) => i)
);
pages.forEach((page, i) => {
if (pageNumbers.includes(i + 1)) {
pdfDoc.addPage(page);
}
});
const bytes = await pdfDoc.save();
// Create a PDF file.
DriveApp.createFile(
Utilities.newBlob([...new Int8Array(bytes)], MimeType.PDF, "sample.pdf")
);
}
When this script is run, the specific pages (In this sample, 2 and 4 pages.) in pageNumbers of a PDF file of fileId are exported as a new PDF file.
This is a Google Apps Script library for easily managing the template of Google Documents and Google Slides using Google Spreadsheet as a database using Google Apps Script.
You might have situations where are required to create multiple Google Documents and Google Slides from the templates using Google Spreadsheet as a database with Google Apps Script. When the simple texts are replaced with the placeholders on the templates, this can be achieved by a simple script. However, there is the case that the images are required to be inserted into the templates. In this case, the script is complicated. The method for replacing the placeholders with the images is different between Google Documents and Google Slides. And, when I see questions on Stackoverflow, a lot of questions related to the template process using Google Documents and Google Slides can be seen. Furthermore, I personally have a lot of questions related to this from other users. From this situation, I thought that when managing the template process can be simply run, it will be useful for a lot of users. So, I created this library.
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.
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.
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 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 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.
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.
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.
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);
}
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.
This is a sample script for merging multiple PDF files as a single PDF file using Google Apps Script.
In this sample script, pdf-lib is used. In the current stage, it seems that this Javascript can be directly used with Google Apps Script.
As a sample situation, please put multiple PDF files in your Google Drive. This sample merges those PDF files as a single PDF file.
This is a sample script for retrieving the start and end of the month in a year using Google Apps Script and Javascript.
function myFunction() {
const year = 2023; // Please set year you expect.
const res = [...Array(12)].map((_, i) =>
[0, 1].map((e) => new Date(year, i + e, 1 - e))
);
console.log(res);
console.log(res.map(([a, b]) => [a.toDateString(), b.toDateString()]));
}
https://jsfiddle.net/mLrhqwgo/
When this script is run, the following value is obtained with console.log(res.map(([a, b]) => [a.toDateString(), b.toDateString()])).
[
["Sun Jan 01 2023", "Tue Jan 31 2023"],
["Wed Feb 01 2023", "Tue Feb 28 2023"],
["Wed Mar 01 2023", "Fri Mar 31 2023"],
["Sat Apr 01 2023", "Sun Apr 30 2023"],
["Mon May 01 2023", "Wed May 31 2023"],
["Thu Jun 01 2023", "Fri Jun 30 2023"],
["Sat Jul 01 2023", "Mon Jul 31 2023"],
["Tue Aug 01 2023", "Thu Aug 31 2023"],
["Fri Sep 01 2023", "Sat Sep 30 2023"],
["Sun Oct 01 2023", "Tue Oct 31 2023"],
["Wed Nov 01 2023", "Thu Nov 30 2023"],
["Fri Dec 01 2023", "Sun Dec 31 2023"]
]
This is a simple sample script for filtering JSON objects using Google Apps Script.
In the current stage, V8 runtime can be used with Google Apps Script. By this, when you want to filter a JSON object, you can use the following sample script.
In this sample script, obj is filtered by the value of the even number.
const obj = { key1: 1, key2: 2, key3: 3, key4: 4, key5: 5 };
const res = Object.fromEntries(
Object.entries(obj).filter(([, v]) => v % 2 == 0)
);
console.log(res); // {"key2":2,"key4":4}
When v % 2 == 0 is modified to v % 2 == 1, you can filter the JSON object with the odd number like {"key1":1,"key3":3,"key5":5}.
This sample script decrypts the salted base64 data of finance.yahoo.com using Google Apps Script.
Recently, when I saw the HTML of finance.yahoo.com, I noticed that the data is converted by the salted base64. In order to decrypt the data, it is required to use the key data. But, unfortunately, I couldn’t find the key data from the HTML. When I searched for it, I found this thread. From the thread, I could retrieve the key data. By this, I could a script for decrypting the salted base64.
This is a sample script for encrypting and decrypting with AES using crypto-js with Google Apps Script.
Unfortunately, in the current stage, Google Apps Script cannot encrypt and decrypt AES using the built-in functions. In this post, in order to achieve this, “crypto-js” is used from cdnjs.com ( https://cdnjs.com/libraries/crypto-js ). In the current stage, it seems that the main functions of crypto-js.min.js can be directly used with Google Apps Script. But, unfortunately, all functions cannot be used. Please be careful about this.
This is a sample script for retrieving the values from a publicly shared Google Spreadsheet using an API key with Javascript.
In this sample script, googleapis for Javascript is used.
<script async defer src="https://apis.google.com/js/api.js" onload="handleClientLoad()"></script>
<script>
function handleClientLoad() {
const apiKey = "###"; // Please set your API key.
const spreadsheetId = "###"; // Please set your Spreadsheet ID.
gapi.load('client', async () => {
await gapi.client.init({ apiKey, discoveryDocs: ["https://sheets.googleapis.com/$discovery/rest?version=v4"] });
const { result } = await gapi.client.sheets.spreadsheets.values.get({ spreadsheetId, range: "Sheet1" });
console.log(result);
});
}
In this sample, I used Method: spreadsheets.values.get. Of course, in this case, you can use the methods of Method: spreadsheets.get, Method: spreadsheets.values.batchGet.
This is a sample script for updating Array1 with Array2 using Google Apps Script.
As a sample situation, there are 2 arrays (Array1 and Array2) of the 2-dimensional array. The sample situation can be seen in the above sample Spreadsheet.
I sometimes see such questions on Stackoverflow. So, I thought that when this sample script is posted, it might be useful for users.
This is a report related to the rule of item IDs for questions of Google Forms.
When the questions are created using the method of batchUpdate with Google Forms API, the created questions have the item IDs when the item IDs are not given in the request body. ( https://developers.google.com/forms/api/reference/rest/v1/forms#item ) For example, when you want to create a question and update the created question in one API call, it is required to include the custom item ID in the request body. But, it seems that in the current stage, the information of the item ID has never been published. So, in this report, I would like to consider the rule of item IDs of questions for Google Forms API.
This is a Javascript library for cropping images by the border.
When an image is used, there is a case where I wanted to simply crop the image by a script. In this Javascript library, the image is cropped by a border. The sample situation is as follows.
In this sample situation, a red rectangle is enclosed by a border (1 pixel) with “#000000”. By this border, this library crops the red rectangle. In this case, the 1-pixel border is not included in the resulting image.
This is a sample script for splitting and processing an array every n length using Google Apps Script. When I prepare a sample script with Google Apps Script, I sometimes have the situation that it is required to split and process an array every n length. This sample script is for achieving this situation.
Please set limit. This sample script splits the sample array every 3 length.
When you use this script with Google Apps Script, please enable V8 runtime.
This is a sample script for increasing the column letter by one using Google Apps Script.
const increase = (ar) =>
ar.map((e) => {
const idx = [...e].reduce(
(c, e, i, a) =>
(c += (e.charCodeAt(0) - 64) * Math.pow(26, a.length - i - 1)),
-1
);
// Ref: https://stackoverflow.com/a/53678158
columnIndexToLetter = (n) =>
(a = Math.floor(n / 26)) >= 0
? columnIndexToLetter(a - 1) + String.fromCharCode(65 + (n % 26))
: "";
return columnIndexToLetter(idx + 1);
});
const samples = ["A", "Z", "AA", "AZ", "ZZ"];
const res = increase(samples);
console.log(res); // <--- [ 'B', 'AA', 'AB', 'BA', 'AAA' ]
["A", "Z", "AA", "AZ", "ZZ"] is increased by one. As the result, [ 'B', 'AA', 'AB', 'BA', 'AAA' ] is obtained.
In this report, I would like to introduce to apply the spread syntax and the destructuring assignment to Google Spreadsheet with Google Apps Script. The destructuring assignment can be used without V8 runtime. But, the spread syntax is required to be used with V8 runtime. Recently, I often saw the script using them at Stackoverflow. And also, I have sometimes gotten the questions related to the spread syntax and the destructuring assignment. So, I thought that I would like to introduce in my blog.
This is a Javascript library for sending the HTML form object to Google Apps Script using google.script.run.
HtmlFormObjectParserForGoogleAppsScript_js was updated to v1.0.1.
v1.0.1 (January 11, 2022)
includeOrder was added as the 4th argument. This library returns the parsed form object as a JSON object. By this, the order of HTML form object is not saved. From this version, this order can be included. This argument includes the order of each input tag in form. The default value is false. When this value is true, a property of orderOfFormObject is included in the returned object. This value is the order of HTML form object. Using this value, you can retrieve the parsed form object in order of HTML form.You can see the detail of this at https://github.com/tanaikech/HtmlFormObjectParserForGoogleAppsScript_js
ResumableUploadForGoogleDrive_js was updated to v2.0.0.
v2.0.0 (November 15, 2021)
This is a Javascript library to achieve the resumable upload for Google Drive.
When a file more than 5 MB is uploaded to Google Drive with Drive API, the resumable upload is required to be used. I have already published the sample script for “Resumable Upload for Web Apps using Google Apps Script”. Ref In this case, Web Apps is used. Here, I would like to introduce the script for the resumable upload created by only Javascript. Unfortunately, in the current stage, at google-api-javascript-client, there are no methods for the resumable upload. And, I thought that when this function is created as a Javascript library, it might be useful for users. Also that library is also useful for me. So I created this. If this was useful for your situation, I’m glad.
This is a sample script for compiling the continuous numbers using Google Apps Script. For example, the values of [4, 5, 9, 3, 10, 5, 11, 7, 7, 13, 1] are converted to ["1","3-5","7","9-11","13"].
const compilingNumbers = (ar) => {
const { values } = [...new Set(ar.sort((a, b) => a - b))].reduce(
(o, e, i, a) => {
if (
o.temp.length == 0 ||
(o.temp.length > 0 && e == o.temp[o.temp.length - 1] + 1)
) {
o.temp.push(e);
} else {
if (o.temp.length > 0) {
o.values.push({ start: o.temp[0], end: o.temp[o.temp.length - 1] });
}
o.temp = [e];
}
if (i == a.length - 1) {
o.values.push(
o.temp.length > 1
? { start: o.temp[0], end: o.temp[o.temp.length - 1] }
: { start: e, end: e }
);
}
return o;
},
{ temp: [], values: [] }
);
return values;
};
// Please run this function.
function main() {
const ar = [4, 5, 9, 3, 10, 5, 11, 7, 7, 13, 1]; // This is sample values.
const values = compilingNumbers(ar);
console.log(values);
const res = values.map(({ start, end }) =>
start == end ? start.toString() : `${start}-${end}`
);
console.log(res);
}
When this script is run, console.log(values) and console.log(res) show [{"start":1,"end":1},{"start":3,"end":5},{"start":7,"end":7},{"start":9,"end":11},{"start":13,"end":13}] and ["1","3-5","7","9-11","13"], respectively. From this result, it is found that the continuous numbers were compiled.
This is a sample script for using Google API Client Library (gapi) for JavaScript with the service account. Unfortunately, in the current stage, gapi cannot directly use the service account. So, in this case, it is required to implement the script for retrieving the access token from the service account. In this report, I would like to introduce the method for using gapi with the service account using a Javascript library.
This is a Javascript library to retrieve the access token from the Google Service Account. Ref
I have already posted the sample script for retrieving the access token from the Google Service Account. Ref But, when I use this script, I thought that when this was published as the Javascript library, it will be useful. So I created this.
<script src="getaccesstokengromserviceaccount_js.min.js"></script>
Or, using jsdelivr cdn
<script src="https://cdn.jsdelivr.net/gh/tanaikech/GetAccessTokenFromServiceAccount_js@master/getaccesstokengromserviceaccount_js.min.js"></script>
You can see the detail of this at https://github.com/tanaikech/GetAccessTokenFromServiceAccount_js
This is a Javascript library for sending the HTML form object to Google Apps Script using google.script.run.
HTML form object is parsed by this library, and the object can be sent to Google Apps Script using google.script.run. After the V8 runtime got to be able to be used for Google Apps Script, when the file input tag is included in the HTML form object, the object sent to Google Apps Script using google.script.run cannot be directly used. Because it seems that the binary file data cannot be parsed when it is sent with google.script.run. Ref In the current stage, as the workaround, the file data is required to be sent to Google Apps Script using google.script.run after it was converted to the byte array and the base64 data. I think that this might be resolved in the future update. But I thought that as the current workaround, when the parser for converting this is prepared, it might be useful for users, because I saw the several questions which have this issue at Stackoverflow.
This is a report for safe-uploading files to Google Drive by HTML put in the external server using Google Apps Script.
When you want to make the user upload a file to your own Google Drive using the HTML put in the external server of Google side, when the file size is smaller than 50 MB, this can be achieved without using the access token. Ref (When the HTML is put in the internal server of Google side, you can also use google.script.run.) But, when the file size is over 50 MB, it is required to upload the file with the resumable upload. In this case, the access token is required to be used. In this case that the user uploads to your own Google Drive, when the access token is used in the upload, it is considered that this is the weak point of the security. In this report, I would like to propose the method for safe-uploading files to Google Drive by HTML put in the external server using Google Apps Script. Please think of this as one of several methods.
This is a library for running Batch Requests for Google APIs using Javascript.
When users use Google’s APIs, one quota is used for one API call. When the batch request is used, several APIs can be called by one quota, although there are some limitations in the batch request.
google-api-javascript-client can run the batch request. Ref But, I created this for my self study. This library can achieve the batch request using fetch without using google-api-javascript-client.
This is a sample script for retrieving the access token for Service Account using Javascript. The flow for using this script is as follows.
private_key and client_email to the script.In this script, 2 libraries of jsencrypt and crypto-js are used.
<script src="https://cdnjs.cloudflare.com/ajax/libs/jsencrypt/3.0.0-rc.1/jsencrypt.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/crypto-js/4.0.0/crypto-js.min.js"></script>
<script>
async function sample() {
const private_key = "###"; // private_key of JSON file retrieved by creating Service Account
const client_email = "###"; // client_email of JSON file retrieved by creating Service Account
const scopes = ["https://www.googleapis.com/auth/drive.readonly"]; // Scopes
const url = "https://www.googleapis.com/oauth2/v4/token";
const header = { alg: "RS256", typ: "JWT" };
const now = Math.floor(Date.now() / 1000);
const claim = {
iss: client_email,
scope: scopes.join(" "),
aud: url,
exp: (now + 3600).toString(),
iat: now.toString(),
};
const signature =
btoa(JSON.stringify(header)) + "." + btoa(JSON.stringify(claim));
const sign = new JSEncrypt();
sign.setPrivateKey(private_key);
const jwt =
signature + "." + sign.sign(signature, CryptoJS.SHA256, "sha256");
const params = {
method: "POST",
headers: { "Content-Type": "application/json" },
body: JSON.stringify({
assertion: jwt,
grant_type: "urn:ietf:params:oauth:grant-type:jwt-bearer",
}),
};
const obj = await fetch(url, params)
.then((res) => res.json())
.catch((err) => console.log(err));
console.log(obj);
}
sample();
</script>
If the access token retrieved at above is used for retrieving file list, the sample script is as follows.
This is a sample script for uploading the image files to the specific album in Google Photos using axios.
Before you use this script, please retrieve the access token for uploading the files using Google Photos API.
In this sample script, several image files can be uploaded.
<input type="file" id="files" name="file" multiple />
<input type="button" onclick="main()" value="upload" />
<script>
function upload({ files, albumId, accessToken }) {
const description = new Date().toISOString();
const promises = Array.from(files).map((file) => {
return new Promise((r) => {
axios
.post("https://photoslibrary.googleapis.com/v1/uploads", file, {
headers: {
"Content-Type": "application/octet-stream",
"X-Goog-Upload-File-Name": file.name,
"X-Goog-Upload-Protocol": "raw",
Authorization: `Bearer ${accessToken}`,
},
})
.then(({ data }) => {
r({
description: description,
simpleMediaItem: { fileName: file.name, uploadToken: data },
});
});
});
});
return Promise.all(promises).then((e) => {
return new Promise((resolve, reject) => {
console.log(e);
axios
.post(
"https://photoslibrary.googleapis.com/v1/mediaItems:batchCreate",
JSON.stringify({ albumId: albumId, newMediaItems: e }),
{
headers: {
"Content-type": "application/json",
Authorization: `Bearer ${accessToken}`,
},
}
)
.then(resolve)
.catch(reject);
});
});
}
// This function is run.
function main() {
const obj = {
files: document.getElementById("files").files,
albumId: "###", // Please set the album ID.
accessToken: "###", // Please set your access token.
};
upload(obj)
.then((e) => console.log(e))
.catch((err) => console.log(err));
}
</script>
This is the sample scripts for creating new event with Google Meet link to Google Calendar using various languages. When I saw the official document of “Add video and phone conferences to events”, in the current stage, I can see only the sample script for Javascript. But I saw the several questions related to this for various languages. So I published the sample scripts for creating new event with Google Meet link to Google Calendar using various languages.
I have already reported for showing the log to “Apps Script Dashboard” when it requests to the Web Apps. Ref In order to show the log to “Apps Script Dashboard” when it requests to the Web Apps, it is required to use the access token. But in the current stage, when the access token is used for XMLHttpRequest and fetch of Javascript in the request headers, the error related to CORS occurs. So, in this report, I would like to propose the workaround for resolving this issue.
This is a sample script for retrieving the difference between 2 arrays, which are the old values and the new values, using Google Apps Script. In my environment, I sometimes have the situation that it is required to retrieve the difference between 2 arrays. So I prepared this as a sample script. I think that this can be also used at Javascript and Node.js. If this was also useful for your situation, I’m glad.
Updated: GetFileList for golang, Javascript, Node.js and Python
This is the libraries to retrieve the file list with the folder tree from the specific folder of own Google Drive and shared Drives.
v1.0.2 (May 15, 2020)
Shared drive got to be able to be used. The file list can be retrieved from both your Google Drive and the shared drive.
id of resource, you can retrieve the file list from the folder in the shared Drive.You can see the detail information here https://github.com/tanaikech/GetFileList_js
This is a Javascript library to achieve the resumable upload for Google Drive.
When a file more than 5 MB is uploaded to Google Drive with Drive API, the resumable upload is required to be used. I have already published the sample script for “Resumable Upload for Web Apps using Google Apps Script”. Ref In this case, Web Apps is used. Here, I would like to introduce the script for the resumable upload created by only Javascript. Unfortunately, in the current stage, at google-api-javascript-client, there are no methods for the resumable upload. And, I thought that when this function is created as a Javascript library, it might be useful for users. Also that library is also useful for me. So I created this. If this was useful for your situation, I’m glad.
v1.0.1 (September 30, 2019)
You can see the detail information here https://github.com/tanaikech/GetFileList_js
This is a Javascript library to use “google.script.run” with the synchronous process.
When I create Web Apps, add-on using a side bar and dialog, there is the case that I want to use google.script.run with the synchronous process. As you know, google.script.run works with the asynchronous process. So in order to use it as the synchronous process, the script is required to be prepared. I also saw several issues for such situation at Stackoverflow and other sites. I thought that when the script for achieving this was prepared as a library, it might be useful for users. So I created this.
This is a Javascript library to retrieve the file list with the folder tree from the specific folder (publicly shared folders and own folders) of Google Drive.
The library for retrieving the file list with the folder tree from the specific folder of Google Drive has already been published for Google Apps Script, golang, node.js and python as GetFileList. Ref Here, this GetFileList was released as the library of Javascript.
At October 11, 2019, I published a Javascript library to to run the resumable upload for Google Drive. When this is used, the large file can be uploaded. You can also use this js library.
This is a sample script for uploading files to Google Drive using Javascript. The files are uploaded by Drive API v3. gapi.client.drive.files.create() can create an empty file on Google Drive. But it cannot directly upload files including contents. I think that this might not be able to upload files and metadata with the multipart/related, although this might be resolved by the future update. So now, as one of workarounds, I use using XMLHttpRequest.
This is for adding the query parameters to the URL. These scripts can be also used for Javascript. When I created an endpoint with some query parameters, I had used the scripts of various patterns every time. Today, I prepared this sample script to unify them. If this is also useful for you, I’m glad.
String.prototype.addQuery = function (obj) {
return (this == "" ? "" : `${this}?`) + Object.entries(obj).flatMap(([k, v]) => Array.isArray(v) ? v.map(e => `${k}=${encodeURIComponent(e)}`) : `${k}=${encodeURIComponent(v)}`).join("&");
}
function myFunction1() {
const url = "https://sampleUrl";
const query = {
query1: ["value1A", "value1B", "value1C"],
query2: "value2A, value2B",
query3: "value3A/value3B",
};
const endpoint = url.addQuery(query);
console.log(endpoint); // https://sampleUrl?query1=value1A&query1=value1B&query1=value1C&query2=value2A%2C%20value2B&query3=value3A%2Fvalue3B
}
// In this case, only the query parameter is exported. This value can be used for requesting with Form data.
function myFunction2() {
const url = "";
const query = {
query1: ["value1A", "value1B", "value1C"],
query2: "value2A, value2B",
query3: "value3A/value3B",
};
const endpoint = url.addQuery(query);
console.log(endpoint); // query1=value1A&query1=value1B&query1=value1C&query2=value2A%2C%20value2B&query3=value3A%2Fvalue3B
}
String.prototype.addQuery = function (obj) {
return this + Object.keys(obj).reduce(function (p, e, i) {
return p + (i == 0 ? "?" : "&") +
(Array.isArray(obj[e]) ? obj[e].reduce(function (str, f, j) {
return str + e + "=" + encodeURIComponent(f) + (j != obj[e].length - 1 ? "&" : "")
}, "") : e + "=" + encodeURIComponent(obj[e]));
}, "");
}
function myFunction() {
var url = "https://sampleUrl";
var query = {
query1: ["value1A", "value1B", "value1C"],
query2: "value2A, value2B",
query3: "value3A/value3B",
};
var endpoint = url.addQuery(query);
Logger.log(endpoint);
}
Both sample scripts return the following URL including the query parameters.
This sample script is for retrieving a key with the maximum value from an object. This can be also used by Google Apps Script.
var obj = {"a": 5, "b": 4, "c": 3, "d": 2, "e": 1};
var res = Object.keys(obj).reduce(function(a, c) {
return obj[a] > obj[c] ? a : c;
});
Logger.log(res); // >>> a
google.script.run doesn’t return values. So I tried this using jQuery.Deferred.
function doGet() {
return HtmlService.createHtmlOutputFromFile('index')
.setSandboxMode(HtmlService.SandboxMode.IFRAME);
}
function getValues(e) {
return e + "hoge";
}
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.js"></script>
<body>
<input type="button" id="button" value="ok">
<div id="result"></div>
<script>
$(() => {
$("#button").click(() => {
var str = "fuga";
googleScriptRun(str).then((res) => {
$('#result').text(res);
});
});
});
function googleScriptRun(str) {
var d = new $.Deferred();
google.script.run.withSuccessHandler((res) => {d.resolve(res)}).getValues(str);
return d.promise();
}
</script>
</body>
fugahoge
Above sample can be also written as follows.
This is a sample script for combining and mixing 2 objects. Each object is an array which included a dictionary type. When the key of the dictionary object is the same, the values are mixed.
This can be also used for Google Apps Script.
var obj1 = [
{"key1": ["value1a1", "value1a2"]},
{"key1": ["value1aa1", "value1aa2"]},
{"key2": ["value2a1", "value2a2"]},
{"key3": ["value3a1", "value3a2"]},
];
var obj2 = [
{"key1": ["value1b1", "value1b2"]},
{"key3": ["value3b1", "value3b2"]},
{"key3": ["value3bb1", "value3bb2"]},
{"key4": ["value4b1", "value4b2"]},
];
[
{"key1": ["value1a1", "value1a2", "value1b1", "value1b2", "value1aa1", "value1aa2"]},
{"key2": ["value2a1", "value2a2"]},
{"key3": ["value3a1", "value3a2", "value3b1", "value3b2", "value3bb1", "value3bb2"]},
{"key4": ["value4b1", "value4b2"]}
]
function mixture(obj1, obj2) {
Array.prototype.push.apply(obj1, obj2);
var temp = [];
var res = [];
obj1.forEach(function(e, i){
temp[i] = !~temp.indexOf(Object.keys(e)[0]) ? Object.keys(e)[0] : false;
if (temp[i]) {
res.push(e);
} else {
res.forEach(function(f, j){
if (Object.keys(f)[0] == Object.keys(e)[0]) {
Array.prototype.push.apply(res[j][Object.keys(f)[0]], e[Object.keys(e)[0]]);
}
});
}
});
return res;
}
var obj1 = [
{"key1": ["value1a1", "value1a2"]},
{"key1": ["value1aa1", "value1aa2"]},
{"key2": ["value2a1", "value2a2"]},
{"key3": ["value3a1", "value3a2"]},
];
var obj2 = [
{"key1": ["value1b1", "value1b2"]},
{"key3": ["value3b1", "value3b2"]},
{"key3": ["value3bb1", "value3bb2"]},
{"key4": ["value4b1", "value4b2"]},
];
var res = mixture(obj1, obj2);
console.log(JSON.stringify(res))
This is a sample script for coffeescript.
This sample script is for adding object to object by javascript.
var obj = {
key1: "value1",
key2: "value2",
key3: "value3"
};
var obj1 = {
key4: "value4",
key5: "value5",
key6: "value6"
};
Object.assign(obj, obj1);
console.log(obj);
{ key1: 'value1',
key2: 'value2',
key3: 'value3',
key4: 'value4',
key5: 'value5',
key6: 'value6' }
This sample script is for straightening elements in 2 dimensional array using Google Apps Script (GAS). When applications using Spreadsheet are developed by GAS, it usually uses 2 dimensional array by setValues(). And the lengths of each element are required to be the same. On the other hand, data used for the applications might not be the same length for each element in 2 dimensional array. This sample script can be used under such situation.
This sample script is for changing values by checking duplicated values of JSON for Javascript.
Please see the following script. There is an array with a JSON data with 3 keys and 3 values. It is found that the values for each element duplicate. These duplicated values are changing by adding numbers.
I use this for managing filenames. This script also can be used for Google Apps Script. If this was useful for you, I’m glad.
This sample is for retrieving array coordinates of duplicated elements.
var inputdata = ["a", "b", "b", "c", "d", "c", "e", "a", "f", "g"];
var dic = {};
var result = [];
inputdata.forEach(function(e, i){
if (dic[e]) {
result[i] = 'Duplicated';
} else {
result[i] = null;
}
dic[e] = "temp";
});
Logger.log(JSON.stringify(result))
Logger.log([i for (i in result) if(result[i]=='Duplicated')])
[null,null,"Duplicated",null,null,"Duplicated",null,"Duplicated",null,null]
[2, 5, 7]
This sample removes duplicate JSON elements for a value of a certain key. When the value of the certain key is removed, only a first duplicate element is left. Also I had wanted to be used for Google Apps Script. So it became like this.
Script :
function removeDup(arr, key){
var temp = [];
var out = [];
arr.forEach( function (e, i) {
temp[i] = (temp.indexOf(e[key]) === -1) ? e[key] : false;
if (temp[i]) out.push(e);
});
return out;
}
JSON :
This script transposes from an array with n rows x m columns to the array with m rows x n columns. In this script, you can use array of n != m .
array = [
[a1, b1, c1, d1, e1],
[a2, b2, c2, d2, e2],
[a3, b3, c3, d3, e3],
[a4, b4, c4, d4, e4],
[a5, b5, c5, d5, e5],
[a6, b6, c6, d6, e6],
[a7, b7, c7, d7, e7],
[a8, b8, c8, d8, e8],
[a9, b9, c9, d9, e9],
[a10, b10, c10, d10, e10]
]
var result = [];
for (i in array[0]){
result.push(array.map(function(e,j){return e[i]}));
}
result = [
[a1, a2, a3, a4, a5, a6, a7, a8, a9, a10],
[b1, b2, b3, b4, b5, b6, b7, b8, b9, b10],
[c1, c2, c3, c4, c5, c6, c7, c8, c9, c10],
[d1, d2, d3, d4, d5, d6, d7, d8, d9, d10],
[e1, e2, e3, e4, e5, e6, e7, e8, e9, e10]
]
This is a Sample Array Script for Spreadsheet. It makes an 2D array filled by strings and number. The strings and number are column strings and row number, respectively.
However, because this is a sample, the maximum column number is 26.
function sa(row, col){
if (col > 26) return;
var ar = new Array(row);
for(var i = 0; i < row; i++) ar[i] = new Array(col);
for (var i = 0; i < row; i++){
for (var j = 0; j < col; j++){
ar[i][j] = String.fromCharCode(i + 97) + String(j + 1);
}
}
return ar.map(function(x, i){return x.map(function(y, j){return ar[j][i]})});
}
When “sa(10,10)” is given, following array can be output.