Here, CLI tools, libraries, Reports, Benchmarks and Sample Scripts for taking advantage of Google Apps Script which are publishing in my blog, Gists, GitHub and my answers on Stackoverflow are summarized. I hope that you have the chance for knowing the possibilities of Google Apps Script from my contents. If these are useful for you, I'm glad.
Japanese version of this list is here.
- News
- Trend of Google Apps Script
- Settings
- CLI tools for GAS
- Web Applications
- GAS libraries
- GAS library database
- Go libraries
- Node.js modules
- Python library
- Javascript library
- Add-ons
- Reports
- Benchmarks
- Communities
- Sample Scripts
- June 3, 2024: My post was featured in The overwhelmed person’s guide to Google Cloud: week of May 23
- December 13, 2023: Drive API v3 has been released to Advanced Google services
- August 12, 2023: My report has been featured in Google Workspace Developer Newsletter on July 2023
- June 11, 2023: My report has been published at Champion Innovators Content Library and Google Cloud Medium Publication
- May 21, 2023: My report has been published at Champion Innovators Content Library and Google Cloud Medium Publication
- May 16, 2023: My report has been published at Champion Innovators Content Library and Google Cloud Medium Publication
- April 20, 2023: My report "Enriched Management of Rich Text on Google Spreadsheet using Google Apps Script" has been published at Champion Innovators Content Library
- January 31, 2023: My report has been published at Champion Innovators Content Library
- November 15, 2022: My report has been featured by Google Workspace Developer Newsletter
- November 3, 2022: My report has been published at Champion Innovators Content Library
- October 3, 2022: My report has been published on the Blog of Google Cloud
- September 6, 2022: My report has been published at Champion Innovators Content Library
- August 17, 2022: My report has been published at Champion Innovators Content Library
- June 2, 2022: Embed content as a full page in new Google Sites
- April 14, 2022: Updating Script Editor of Google Apps Script
- March 17, 2022: Finally, Google Forms API has been officially released as version 1
- March 10, 2022: Bug of Create Method of Google Forms API was Removed
- January 22, 2022: On January 19, 2022, 2 Classes of 'CellImageBuilder' and 'CellImage' have been added to Spreadsheet Service
- December 9, 2021: Google Forms API now available in open beta
- December 9, 2021: [Fixed] Google Apps Script Web App HTML form file-input fields not in blob compatible format
- October 16, 2021: Announcing the Google Forms API
- October 14, 2021: Google Cloud Innovators Champions
- April 6, 2021: A Bug of New IDE about Time Zone of Google Apps Script project was removed
- December 15, 2020: Meet the Google Workspace Developer Experts
- December 8, 2020: New IDE for Google Apps Script has been finally released
- July 30, 2020: Drive API got to be able to create Google Apps Script project again
- July 10, 2020: Transfer of owner of files got to be able to be achieved with batch requests of Drive API
- July 9, 2020: Specification of Files: copy in Drive API was changed
- July 7, 2020: Transfer of owner of files got not to be able to be used with batch requests of Drive API
- June 15, 2020: Transfer of owner of files got to be able to be achieved with batch requests of Drive API
- June 4, 2020: Managing Shared Drive using Drive Service of Google Apps Script
- March 11, 2020: Drive API cannot create Google Apps Script project no longer
- February 7, 2020: V8 Runtime was added to Google Apps Script at February 7, 2020
- April 8, 2019: Specification of Google Apps Script Project was Changed at April 8, 2019
At Stackoverflow, a lot of people post questions and answers to the questions every day. There are various tags in Stackoverflow. A lot of discussions are performed at each tag. Their discussions bring important information and are much useful for a lot of people. As one of the tags, there is "google-apps-script". I sometimes discuss the questions with that tag. When we see the discussions, we can notice that the discussions are changed and progressed over time, because "Google Apps Script" which is the origin of the tag is updated. This report thinks this change is the trend of the tag of "google-apps-script". This trend includes the number of questions, questioners, answerers, and tags added to the tag of "google-apps-script". The trend of the tag of "google-apps-script" is deeply related to the progression of Google Apps Script and the various applications for Google Apps Script. In this report, as one of several approaches, the trend of Google Apps Script was investigated by statistically analyzing all questions with the tag of "google-apps-script". As the result, it was found that by investigating all questions with the tag "google-apps-script", the tags added to this main tag strongly affected the trend of the tag "google-apps-script". Also, the possibility which can estimate the future trend by investigating the tags added to the tag of "google-apps-script" was indicated.
-
Linking Cloud Platform Project to Google Apps Script Project: At April 8, 2019, the specification of Google Apps Script Project was changed. In this report, the flow for linking Cloud Platform Project to GAS project is explained.
-
Linking Google Cloud Platform Project to Google Apps Script Project for New IDE: This is the document for linking Google Cloud Platform Project to Google Apps Script Project for New IDE. And also, several sample scripts using Google Apps Script API and Google Photos API are introduced.
-
Redeploying Web Apps without Changing URL of Web Apps for new IDE: At March 15, 2021, one endpoint is created for one deployment. Ref By this, when you redeploy "Web Apps", the endpoint is changed. Because the deployment ID is changed. It seems that this it the new specification. In this report, I would like to introduce the method for redeploying Web Apps without changing the URL of Web Apps for new IDE.
- ggsrun : Execute Google Apps Script (GAS) at own terminal on local PC.
- gislack : Submit files to both Gist and Slack.
- goris : Search for images with Google Reverse Image Search (goris).
- gogauth : Easily retrieve access token for using APIs on Google.
- gorearrange : Interactively rearrange a text data on a terminal.
- goodls : Download shared files from Google Drive.
- Search Google Apps Script Libraries : This is an application for searching Google Apps Script libraries from the database.
- Fields Builder For Google APIs : FieldsBuilderForGoogleAPIs is a Web Application for building the fields value for using Google APIs. This is mainly used for developing the scripts for using Google APIs. GitHub
You can search the GAS libraries at Search Google Apps Script Libraries
- BatchRequest : This is a library for running Batch Requests using Google Apps Script (GAS).
- ConvertNFDtoNFC : This is a script for converting strings from NFD (Normalization Form Decomposition) to NFC (Normalization Form Composition) using Google Apps Script.
- FilesApp : FilesApp is a GAS library for retrieving file and folder list in Google Drive using Google Apps Script (GAS). Also this can create a tree from all files and folders in Google Drive.
- ImgApp : This is a library of image tools for Google Apps Script.
- ManifestsApp : This is a Manifests library for Google Apps Scripts.
- ProjectApp : This is a project library for Google Apps Script (GAS).
- ProjectApp2 : This is a GAS project library for Google Apps Script (GAS). This library can be used for the projects of both standalone script type and container-bound script type.
- OnedriveApp : This is a library of Google Apps Script for using Microsoft OneDrive.
- Resumable_Upload_For_WebApps : This is a sample script for uploading files with large size (> 50 MB) at Web Apps using Google Apps Script (GAS). The resumable upload method is used for uploading files. This script can be also applied to the script using gapi of javascript.
- RunAll : This is a library for running the concurrent processing using only native Google Apps Script (GAS).
- SOUWA_GAS : GAS library for summing string elements in an array at the high speed
- ZipFolder : This is a library for zipping a folder using Google Apps Scripts.
- RangeListApp : RangeListApp is a GAS library for retrieving, putting and replacing values for Spreadsheet by a range list with a1Notation using Google Apps Script (GAS).
- DownloadLargeFilesByUrl : DownloadLargeFilesByUrl is a GAS library for downloading large files from URL to Google Drive using Google Apps Script (GAS).
- ArrangeStackingOrder : ArrangeStackingOrder is a GAS library for arranging the stacking order of page elements on Google Slides using Google Apps Script (GAS).
- ProcessApp : This is a library for retrieving the process and information of Google Apps Script. For example, one of methods retrieves the total execution time of all functions executed by the time-driven trigger at owner's account.
- GistChecker : This is a GAS library for notifying the change of number of comments, stars and forks of own Gists as an email using Google Apps Script.
- FetchApp : This is a GAS library for creating and requesting the type of multipart/form-data using Google Apps Script. This library enhances Class UelFetchApp of Google Apps Script.
- GetEditType : GetEditType is a GAS library for retrieving the edit types of the OnEdit event trigger of Spreadsheet using Google Apps Script (GAS).
- UnzipGs : This is a GAS library for unzipping a Zip file protected by a password using Google Apps Script.
- GmailToList : This is a library for exporting all messages of Gmail as a list using Google Apps Script (GAS).
- EncodeApp : EncodeApp is a GAS library for retrieving the encoding set (charset) and doing URL encode with the specific encoding set using Google Apps Script (GAS).
- DateFinder : DateFinder is a GAS library for searching the date objects from the cell range on the sheet in the Spreadsheet and retrieving the searched range as the RangeList object using Google Apps Script (GAS).
- RichTextApp : This is a GAS library for copying the rich text with the text styles from Google Document to Google Spreadsheet or from Google Spreadsheet to Google Document using Google Apps Script (GAS). And, also the rich texts in the cells can be converted to HTML format.
- GPhotoApp : This is a GAS library for retrieving and creating the albums and media items using Google Photo API using Google Apps Script (GAS).
- CopyFolder : This is Google Apps Script library for copying folder on Google Drive.
- OwnershipTransfer : This is a Google Apps Script library for achieving the ownership-transfer of the specific folder including the files and sub-folders using Drive API.
- GASProjectApp : This is a Google Apps Script library for creating, updating and exporting Google Apps Script project of the standalone type using Drive API. In this case, Apps Script API is not used.
- DocsServiceApp : This is a Google Apps Script library for supporting Document service, Docs API, Spreadsheet service, Sheets API, Slides service and Slides API. The aim of this library is to compensate the processes that they services cannot achieve.
- HtmlFormApp : This is a Google Apps Script library for parsing the form object from HTML form and appending the submitted values to the Spreadsheet.
- DocNamedRangeApp : This is a Google Apps Script library for managing the named range on Google Documents.
- RichTextAssistant : This is a GAS library for supporting editing RichText in Google Spreadsheet using Google Apps Script.
- UtlApp : This is a Google Apps Script library including useful scripts for supporting to development of applications by Google Apps Script.
- TemplateApp : 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.
- TriggerApp : This is a Google Apps Script library for efficiently managing the time-driven triggers for executing Google Apps Script using Google Apps Script.
- PDFApp : This is a Google Apps Script library for managing PDFs.
- ScriptHistoryApp : This is a Google Apps Script library for managing the histories of the Google Apps Script project.
- MicrosoftDocsApp : This is a Google Apps Script library for using Microsoft Docs files (Word, Excel, and PowerPoint files) using Document service, Spreadsheet service, and Slides Service of Google Apps Script.
- CorporaApp : This is a Google Apps Script library for managing the corpora of Gemini API.
- ImageBotApp : This is the image bot using Gemini with Google Apps Script and Google Drive.
- GoogleApiApp : This is a Google Apps Script library for supporting to use Google APIs with Google Apps Script.
- GeminiWithFiles : A new Google Apps Script library called GeminiWithFiles simplifies using Gemini, a large language model, to process unstructured data like images and PDFs. GeminiWithFiles can upload files, generate content, and create descriptions from multiple images at once. This significantly reduces workload and expands possibilities for using Gemini.
- MoveFolder : This is a Google Apps Script library for moving a folder including files and folders on Google Drive.
- Google Apps Script Library Database : This is for the Google Apps Script Library Database.
- go-getfilelist : This is a Golang library to retrieve the file list with the folder tree from the specific folder of Google Drive.
- go-gettokenbyserviceaccount : This is a Golang library to retrieve access token from Service Account of Google without using Google's OAuth2 package.
- go-gdoctableapp : This is a Golang library for managing tables on Google Document using Google Docs API.
- node-getfilelist : This is a Node.js module to retrieve the file list with the folder tree from the specific folder of Google Drive.
- node-gdoctableapp : This is a Node.js module to manage the tables on Google Document using Google Docs API.
- getfilelistpy : This is a python library to retrieve the file list with the folder tree from the specific folder of Google Drive.
- gdoctableapppy : This is a python library to manage the tables on Google Document using Google Docs API.
- GetFileList_js : 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.
- syncGoogleScriptRun : This is a Javascript library to use "google.script.run" with the synchronous process.
- ResumableUploadForGoogleDrive_js : This is a Javascript library to achieve the resumable upload for Google Drive.
- BatchRequest_js : This is a library for running Batch Requests for Google APIs using Javascript.
- HtmlFormObjectParserForGoogleAppsScript_js : This is a Javascript library for sending the HTML form object to Google Apps Script using
google.script.run
. - GetAccessTokenFromServiceAccount_js : This is a Javascript library to retrieve the access token from the Google Service Account.
- Javascript Library for Cropping Image by Border : This is a Javascript library for cropping images by the border.
- RearrangeScript : Rearranging Google Apps Scripts (GAS) in a project which can be seen at the script editor. GitHub
- ShapeApp : Manipulating shapes on Google Slide. It can create and update shapes by inputting parameters, and can arrange shapes. This is made of Google Apps Scripts (GAS). GitHub
I considered an efficient algorithm for summation of array elements. All elements in an array are string. When those elements are summed using scripts, a standard method is to add each element in order. If the script is run without any optimize, the process becomes gradually sluggish, because the total amount of active data during the summation process is proportional to the square of the number of array elements. This leads directly to the high process-cost. Such phenomenon notably appears at Google Apps Script (GAS). This report says about the solution of this problem using a new algorithm of a pyramid method. The pyramid method achieves that the total amount of active data increases proportional to the linear of the number of array elements. By this, the processing time becomes much shorter than that of the process using the standard method. The pyramid method achieved the process-cost reduction of 99.7% compared with the standard method at GAS. I realized again that new discoveries are hidden into the familiar scenes of every-day life.
By recent Google update (Google update at October 24, 2017), various new winds to GAS developers were blown. There is "Manifests" as one of the new winds. "Manifests" makes us manage the project using JSON. Especially, the special scopes which have to use OAuth2 process can be used by only setting them to the Manifests. I think that this is the largest modification. However, when scopes are added to a project using Manifests, users who use the project can use only added scopes. This means that when users create scripts in the project, if there are some scopes which is required to be added, such scopes cannot be automatically added. So the error of "Insufficient Permission" occurs. In this report, I would like to introduce the workaround for avoiding this problem.
This is a document for explaining the difference between given values and retrieved values for shapes on Google Slides.
There is Web Apps as one of applications using Google Apps Script (GAS). I sometimes use this Web Apps. But I have only a little the information for the specification of Web Apps. So in order to take more advantage of Web Apps, I investigated and summarized about this. The aim of this report is to become one of the basic information for creating various applications using Web Apps with GAS.
Here I would like to introduce about the limitation of images for inserting to Spreadsheet using Google Apps Script (GAS). When you want to insert the images to Spreadsheet using GAS, insertImage() of class Sheet is usually used for this situation. At this time, an error sometimes occurs. This indicates that there is the limitation for inserting images to Spreadsheet. So I investigated the limitation. As a result, it was found that the limitation depends on the image area (pixels^2) rather than the file size of it. The maximum area of image which can be inserted was 1,048,576 pixels^2.
This is a report about the possibility of asynchronous process using event triggers.
onEdit()
which is a simple trigger is often used as a trigger when the values are modified on Spreadsheet. When users want to use the script including some methods which are required to be authorized as the onEdit event, a installable trigger of onEdit is used. If the trigger is installed for the function ofonEdit()
, when the event trigger is run,onEdit()
is run 2 times. In order to avoid this, the installable trigger is installed to the functions except for the functions of simple triggers. The functions of simple triggers which is the same events are not used in the project. When I thought about this situation, I thought that bothonEdit()
which is run by the simple trigger and the function which is run by the installable trigger might be able to be used, simultaneously. So I investigated about this situation. As the result, it was found that the following simple triggers and installable triggers work as the asynchronous process.
When V8 is enabled,
Array.apply
has the limitation for the number of elements. When it is over the limitation, an error likeRangeError: Maximum call stack size exceeded
occurs, while the issue didn't occur when V8 is disabled. In this case, this issue occurs at both Google Apps Script and Javascript. So please be careful this.
When
//
in template literal is used in a HTML file in script editor, it is used as a comment start.
I have already reported about "Change Tab Detection on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script". Ref It is considered that when the situation which uses the event trigger of
onSelectionChange
is thought, the response speed is important. So, here, I investigated the characteristics of response for the event trigger ofonSelectionChange
.
This is a report for detecting quickly checked checkboxes on Google Spreadsheet using Google Apps Script. It supposes that when the checkbox is checked, a function of Google Apps Script is run by the event trigger. In this case, when the multiple checkboxes on Google Spreadsheet are checked quickly, the script cannot be run for all checked checkboxes, because of the response speed of the event trigger. It is considered that to understand the response of event trigger is useful for creating the application for Spreadsheet. In this report, the detection of quickly checked checkboxes on Google Spreadsheet using Google Apps Script has been investigated. From this result, it led to understanding the response of event trigger.
In this report, I would like to report for processing to create new file to the specific folder using Drive API. When the new file is created to the specific folder using Drive API, the property of
parents
with the value of folder ID is included in the request body of the method "Files: create". About this process, I had thought that the file is directly created to the specific folder. But, I could confirm that the new file is created by the following process.
- Create new file to the root folder.
- Move the created file to the specific folder.
These process is done by one API call. In this report, I would like to introduce the experimental result for confirming above process. In this case, Drive API v3 is used with Google Apps Script.
At Stackoverflow, a lot of people post the questions and answer to the questions every day. By this, there are a lot of important information in Stackoverflow. I have already reported "Trend of google-apps-script Tag on Stackoverflow" using the data retrieved from Stackoverflow. Ref It is found that the important statistical result can be obtained by analyzing the data on Stackoverflow. In this report, I would like to introduce the statistical analysis of duplicated questions for the google-apps-script tag in Stackoverflow. When the duplicated question is analyzed, it is considered that the important issues for users can be known. As the result, it was found that there are the trend that the duplicated questions related to Javascript, Google Spreadsheet, the process cost and the cooperation with HTML and Javascript are posted.
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.
In this report, I would like to report about the current specification of the search query for the file list method in Drive API.
Recently, I noticed that the specification of the search query for the file list method in Drive API might have been changed. I thought that to know the change of specification of the search query is important for creating the application using Drive API. In this report, I would like to introduce the current specification of the search query.
When the users try to write to Spreadsheet using a form, the developers have to consider to the concurrent submission from the form. For example, when the multiple users submit the data with the form simultaneously, all data are possibly not to be saved to the Spreadsheet. So it is considered that it is important to know the information about the concurrent writing to Google Spreadsheet using a form. In this report, such situation was investigated.
As the result, when the success rate for writing concurrently to Google Spreadsheet is investigated, it was found that the concurrent writing with Google Form is suitable rather than Web Apps created by Google Apps Script. The threshold number of users for succeeding to write all data to Spreadsheet was 35 for Google Form and 26 for Web Apps, respectively. And, when Web Apps is used, it was also found that Lock Service was definitely required to be used for the multiple submission.
In this report, it has investigated the large decimal numbers and the exponential notation for Google Spreadsheet. When the large decimal numbers are put to the Spreadsheet, the Spreadsheet automatically sets the display value using the exponential notation. In this report, the result when the values are retrieved by Spreadsheet service and Sheets API is shown.
This is a report about images put with "=IMAGE(IMAGE_URL)" function on Google Spreadsheet.
When "=IMAGE(IMAGE_URL)" is put to a cell "A1" on Spreadsheet, the image is shown in the cell. For this situation, when the cell "A1" is copied with "range.copyTo(range, { contentsOnly: true })", the formula is removed and an image can be seen. I thought that when the image of the URL is removed, how are those images. In this report, such a situation was investigated.
Here, I would like to introduce a report for occurring and resolving the infinite loop on Google Spreadsheet using Google Apps Script. I have reported this to Google issue tracker.
Applicating Spread Syntax and Destructuring assignment to Google Spreadsheet with Google Apps Script
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.
On March 14, 2022, it reported about "Google Sheets doubles cell limit". Ref By this update, now, the users can use 10,000,000 cells in a Google Spreadsheet. This is great news for us. When I tried to handle 10,000,000 cells in a Google Spreadsheet using Google Apps Script, it was found that there were various important points. In this report, I would like to introduce the important points for handling 10,000,000 cells in Google Spreadsheet using Google Apps Script.
This is a report for obtaining the values from GOOGLEFINANCE using Google Apps Script. When I tested to retrieve the values from
GOOGLEFINANCE
function on Google Spreadsheet using Google Apps Script, I noticed that the values can be retrieved.
This is a sample method for publishing various Google Docs files with the same URL using Google Apps Script.
By updating on May 25, 2022, the content got to be able to be embedded as a full page in the new Google site. Ref In this method, this is used.
This is a report for the documentation comments for the functions of Google Apps Script.
When the documentation comments for functions of Google Apps Script are considered, you will think JsDoc. At Google Apps Script, a part of JsDoc can be used. But, in this report, I would like to introduce the documentation comments including JsDoc.
This is a report for management of images on Google Spreadsheet using Google Apps Script.
At October 30, 2018, Cass OverGridImage and the method of inserImage have been added to Spreadsheet Service. At January 19, 2022, Class CellImageBuilder and Class CellImage have been added to Spreadsheet Service. By these Classes and methods, the images got to be able to be managed on Google Spreadsheet. But, when the image is used to the various situations, there are the cases that it is required to ingenuity to manage the images. So, in this report, I would like to introduce the management of images on Google Spreadsheet using the sample scripts of Google Apps Script.
Report: Challenging Exporting Selected Cells on Spreadsheet as Image using Google Apps Script and Javascript
This is a report for challenging exporting the selected cells on Spreadsheet as an image using Google Apps Script and Javascript.
This is a sample script for efficiently creating Web Apps using a Google Apps Script library.
When a Google Apps Script library is used for creating Web Apps, the following advantage can be obtained.
- The script of the client-side can be simpler. Because most scripts for constructing Web Apps are included in the Google Apps Script library.
- When the script of Web Apps (In this case, the script of Google Apps Script library is modified.) is modified, the latest script is reflected in the Web Apps, immediately. Because when the Google Apps Script library is used as the latest version when the script of the library is modified, the client can use the latest script of the library, immediately. So, the downtime of Web Apps can be reduced.
- By this, it is not required to manually reflect the latest version of the script to the Web Apps.
- When you can change the script of Web Apps by changing the deployed version of the library.
Report: Process Cost of Google Apps Script During Large Calculations by Formulas on Google Spreadsheet
In Google Apps Script, there is the Web Apps. When Web Apps is used, the users can execute Google Apps Script using HTML and Javascript. This can be applied to various applications. When the Web Apps is deployed with "Anyone", anyone can access the Web Apps. And, there is the case that Web Apps deployed with "Anyone" is required to be used. Under this condition, when 2 Factor Authentication (2FA) can be implemented, it is considered that the security can be higher and it leads to giving various directions for the applications using Web Apps. In this report, I would like to introduce the method for implementing the pseud 2FA for Web Apps deployed with “Anyone” using Google Apps Script.
It has already been known that Google Apps Script is a strong tool for managing Google Spreadsheets. When the values are retrieved and/or put for Google Spreadsheet, there is a case that the discontinuous cells are required to be used. This report suggests the Best Practices for processing the discontinuous cells on Google Spreadsheet. From the results of process costs, it could understand the usefulness of using the discontinuous cells with low cost using Sheets API and Class RangeList of Spreadsheet service with Google Apps Script.
In this report, the detailed specification of PropertiesService has been investigated. It is considered that knowing this specification will be useful for developing applications with Google Apps Script. As a result, it was found that the maximum key and value sizes are 524,287 bytes with a 1-byte key and 8,066 bytes, respectively. And also, it was found that the maximum size of PropertiesService is required to be considered with both the key and value sizes.
This report introduces the method for easily implementing HTML forms with a Google Spreadsheet as a database using Google Apps Script. There are 2 patterns for the HTML form using Google Apps Script. One is that an HTML form is put into the same Google Apps Script project. Another is that an HTML form is put to a different server from a Google Apps Script project. In this report, the methods for easily implementing both patterns are introduced using the sample scripts.
Recently, I felt a change like never before in the questions on Stackoverflow. In order to confirm this, in this report, the trend of "google-apps-script" tag on Stackoverflow in the first half (January 1st to June 1st) of 2023 has been investigated. From this report, in 2023 when the affection of COVID-19 has been reduced socially, the appreciable trend was confirmed to the questions including a tag of "google-apps-script". It is guessed that the origin of this appreciable trend is due to AI chatbots. The appearance of AI chatbots might give us a phase of major change to the method for understanding the statistical data obtained from online sites.
Here, I would like to introduce a report for understanding the flow of the request to Web Apps created by Google Apps Script. There might be a case that various applications using the Web Apps are created and the Web Apps are used as the webhook. In that case, it is considered that when you have understood the flow of requests to the Web Apps, your goal might be able to be smoothly achieved. In this report, I would like to introduce the information about it.
This report introduces the values that can be used for transferring between HTML and Google Apps Script with google.script.run.
This is a report for taking advantage of the auto-completion of the script editor for Google Apps Script.
Google Apps Script is one of the strong tools with cloud computing, and it is very useful for various situations. Google Apps Script can be run by various methods. This report introduces how to execute Google Apps Script. Recently, generative AI has given a lot of new users a chance to use Google Apps Script. If this report helps the users develop applications using Google Apps Script, I'm glad.
This is the dining reservation system using Google Apps Script.
- It was found that the process cost of
e.range.getA1Notation()
was 20 % and 10 % for those ofe.source.getActiveCell().getA1Notation()
andSpreadsheetApp.getActiveSheet().getActiveCell().getA1Notation()
, respectively.
- In the case of the sample script for retrieving the multiple of 5 from the array, the loop using "map, filter" is the most suitable way.
- Ascending order of cost for each method is "map, filter", "Comprehension", "forEach", "for in", "for loop" and "while".
- Cost for "forEach", "Comprehension" and "map, filter" is lower than that for "for in", "for loop" and "while".
- Cost of push() and new Array() is almost the same.
- When the array is changed from 1 dimensional array to 2 dimensional array, the increasing ratio of the cost for "Comprehension", "forEach" and "map, filter" is much lower than that for "for in", "for loop" and "while".
- For the conventional method using "for loop", a new method could be proposed using the result of this report.
- For "reduce", the process costs between 1 and 2 dimensional array are almost the same.
- It was found that the fetchAll method is worked by the asynchronous processing.
- After it worked by the asynchronous processing, the returned values is reordered by the order of requests.
- It was also found that if you want to retrieve the data from the several URL, the process cost of
UrlFetchApp.fetchAll()
is much lower than that ofUrlFetchApp.fetch()
using for loop.
- Process cost of search by indexOf() was the lowest of all methods.
- 2nd and last one were the search by for loop and the search by the hash, respectively.
- About the search by hash, although the cost of search by the hash from the object is very low, the cost for creating the object to search the hash was the highest of all. By this, the search by hash became the lowest rank. If the object for searching has already been created, the cost of search by the hash will be the lowest of all.
- Search using indexOf can reduce the process cost of more than 99 % from the linear search and the search using hash.
- From these results, it is considered that the scan for indexOf() may be different from the general for loop.
- It was found that the cost of "Ternary operator" was the lowest of all methods and conditions.
- For the single conditional branch, 2nd one was "If". But for the multiple conditional branches, "Switch" was the 2nd one. This indicates that "If" and "Switch" are suitable for the single and multiple conditional branches (more than 2 branches), respectively.
- In the case of the multiple conditional branches, the process cost can be reduced by put the condition with the high possibility of "true" to the fore.
- "Logical operator" was the lowest rank for the single and multiple conditional branches. It is considered that "Logical operator" is not suitable for the general use, because of the high cost and low readability.
- "filter using reversed array" makes the process cost be 43 % lower compare with "Decreasing for loop".
- "reversed array" was obtained using "Array.prototype.reverse()" for the created a sample array.
- "Decreasing for loop" is almost the same with "Increasing for loop".
- Cost of "reverse()" is sufficiently small for "for loop" and "filter".
In this report, the process cost for reading and writing Spreadsheet has been investigated. From this investigation, the following results were obtained.
- For the process costs for reading values from Spreadsheet
- Process costs of getValues() and getSheetValues() of Spreadsheet Service are almost the same.
- Process costs of values.get and values.batchGet of Sheets API are almost the same.
- Methods of Sheets API can reduce the process costs from those of Spreadsheet Service by about 35 %.
- For the process costs for writing values from Spreadsheet
- Process costs of values.update, values.batchUpdate and values.append of Sheets API are almost the same.
- Methods of Sheets API can reduce the process costs from those of Spreadsheet Service by about 19 %.
- There is the inversion point between setValues() of Spreadsheet Service and the methods of Sheets API.
- When the data size is small, setValues() is suitable for writing values.
- When the data size becomes large, the methods of Sheets API are suitable for writing values.
From these results, it is considered that the methods for reading and writing Spreadsheet of Sheets API are using the different algorithm and/or process from those of Spreadsheet service.
In this report, the process cost for importing CSV data to Spreadsheet using GAS has been investigated. As the result, the following results were obtained.
- It was found that pattern4, which uses
pasteData
of Sheets API, was the lowest cost of all.- When pattern4 is used for importing CSV data to Spreadsheet, the cost can be reduced by 56 % from pattern1 and pattern2, which use the method parsing and putting values.
- When pattern4 is used for importing CSV data to Spreadsheet, the cost can be reduced by 72 % from pattern3, which use the method converting mimeType from CSV to Spreadsheet.
In this report, the process cost of "loop" for the array processing using GAS with using V8 runtime has been investigated. As the result, it was found the following important features for GAS with V8.
- In the case of the sample script for retrieving the multiple of 5 from the array, the loop costs using "for loop", "while", "forEach", "map, filter" and "reduce" are almost the same.
- In the case of "for in", the process cost is higher than those of "for loop", "while", "forEach", "map, filter" and "reduce". But when that is compared with the condition without V8, the cost of "for in" with V8 is much lower than those without V8.
- Costs of
push()
andnew Array()
are almost the same.- When v8 runtime is used for the loop process, the process cost could be largely reduced when it is compared with the script without V8.
- For all methods of "for loop", "for in", "while", "forEach", "map, filter" and "reduce", the process costs of 97.0 % for 1D array and 98.4 % for 2D array could be reduced.
In this report, the process costs of 7 situations under V8 were measured. As the result, the following results could be obtained.
- Process cost with and without the arrow function was almost the same.
- Process cost "includes" and "indexOf" was almost the same.
- When the destructuring assignment is used, the cost was about 15 % higher than that without the destructuring assignment.
- Process cost with and without Map object was almost the same.
- But in this case, the cost of
Object.fromEntries
is added for retrieving the result as the object. And whenObject.fromEntries
is not used, the cost with Map object was about 20 % lower than that without Map object.- Process cost of
Array.prototype.push
was the lowest ofArray.prototype.push.apply
, Spread syntax andconcat
. The costs of Spread syntax andconcat
were about 3,040 % and 36,666 % higher than that ofArray.prototype.push
, respectively.- Process cost of only
reduce
was the lowest of onlyreduce
,Object.assign
and Spread syntax. The costs ofObject.assign
and Spread syntax were about 265 % and 448,063 % higher than that of onlyreduce
, respectively.- Process costs of
Array.from
and only map were almost the same. The cost ofObject.entries
was about 131 % higher than that ofArray.from
.
Here, I would like to report the process costs for searching values in Spreadsheet using Google Apps Script (GAS). When the values are searched in Google Spreadsheet, the following 3 patterns can be considered. Ref
- Retrieve all values using getValues, and the values are searched from the retrieved array.
- Use TextFinder.
- Use Query language.
In these cases, it has already been found that the lowest process cost is to use the Query language. And about finding values from an array, I have already been reported as "Benchmark: Search for Array Processing using Google Apps Script". But I had never summarized the process costs for TextFinder and find values from an array. So in this report, I would like to introduce this. As the result, the importance of TextFinder for retrieving the row numbers and the row values by searching a value could be understand.
Here, I would like to report the process costs for retrieving the values from the arrays for Spreadsheet using Google Apps Script (GAS). When Spreadsheet is used with Google Apps Script, we have the following situations.
- Retrieve values from the multiple rows in a column.
- Retrieve values from the multiple columns in a row.
When the values are retrieved from above situations, it is required to retrieve the values from 1 dimensional array in the 2 dimensional array. In this report, the process costs for retrieving the values from the 2 dimensional array of above situations have been measured.
As the result, it was found that when the values are retrieved from the arrays with n rows in a column and n columns in a row, to use the destructuring assignment and to use the index are suitable, respectively.
Benchmark: Measuring Process Costs for Formulas in Cells on Google Spreadsheet using Google Apps Script
When Google Spreadsheet is used, there is the case that the built-in functions and the custom functions in the cells are used. For the functions of Google Apps Script, there is the method for measuring the process cost. Ref But for the built-in functions, it is required to create the script for it. In this report, the script for measuring a function put in a cell has been proposed, and the process cost of the built-in functions has been measured. The proposed script can measure the process cost for the built-in functions and custom functions on Google Spreadsheet. The script is created with using Google Apps Script. When the process cost can be known for the built-in functions and custom functions, it is considered that it will be useful for the developers using Google Spreadsheet.
Benchmark: Process Costs for Retrieving 1st Empty Cell and 1st Non Empty Cell of Specific Column in Google Spreadsheet using Google Apps Script
Here, I would like to report the process costs for retrieving the 1st empty cell or 1st non empty cell of the specific column of Google Spreadsheet using Google Apps Script (GAS). For this situations, the following 2 patterns can be considered.
- Retrieving 1st empty cell of specific column by searching from TOP of sheet
- Retrieving 1st NON empty cell of specific column by searching from BOTTOM of sheet
Actually, when I create the applications using GAS, there is the case that it's required to retrieve the 1st empty cell or 1st non empty cell of the specific column. But, in such case, I would like to make the process cost of this as low as possible comparing with other part of the application. From such my experience, I thought that when the process cost of this can be reduced, it will be also useful for other users. So in this report, I would like to introduce the process cost of this situation. And, the following results were obtained.
- In order to retrieve the 1st empty cell of the specific column by searching from TOP of sheet, the process cost of method using
getNextDataCell
is the lowest of all methods.
- In order to retrieve 1st NON empty cell of specific column by searching from BOTTOM of sheet, the process cost of method using
TextFinder
is the lowest of all methods.
When we use HTML in the Google Apps Script project, in order to show the values from the Google Apps Script side, the HTML template is used. When I used the HTML template with a large value, I understood that the process cost can be reduced by devising a script. In this report, I would like to introduce the process cost of the HTML template using the benchmark.
As the result, it was clearly found that when an HTML table is created with Google Apps Script and shows the HTML table with the HTML template, the process cost becomes low. And also, it was found that in order to reduce the process cost for using the HTML template, it is required to prepare the HTML data with the Google Apps Script side.
In order to retrieve the values from XML data, when XML data is parsed using Google Apps Script, there are several methods for parsing the data. Class XmlService, which is a built-in Class for managing XML data, might be the first way to come up with it. At Stackoverflow, it is posted questions that XML data is often parsed using Class XmlService. It is considered that Class XmlService is suitable for managing XML data.
But, when I have created applications using XML data with Google Apps Script before, I have felt that the process of Class XmlService might be high. So, in this report, the process cost for parsing XML data using Google Apps Script. As the result, it was found that after the V8 runtime had been released, the methods except for Class XmlService in order to parse XML data can be also used.
Benchmark: High-Efficiency Finding and Replacing Many Values in Google Spreadsheet with Low Process Cost using Google Apps Script
This is a sample script for high-efficiency finding and replacing many values in Google Spreadsheet with the low process cost using Google Apps Script.
When the various values are replaced in Google Spreadsheet using Google Apps Script, I'm worried about the process cost. So, in this report, I would like to introduce a sample script for high-efficiency achieving this.
As the result, using a sample situation, when the process cost of the sample script using Sheets API is compared with that of the sample script using Spreadsheet services (SpreadsheetApp), it was found that the above script using Sheets API could reduce the process cost by about 70 % from the script using Spreadsheet service.
There is a maximum executing time for Google Apps Script (GAS). That is 6 minutes. And, in the case of the custom function and the simple trigger, it is 30 seconds. So users always have to pay attention to reducing the process cost of the scripts. Especially, it is very important to know the process costs for the array processing, because array processing is often used for spreadsheets and Google APIs. I have already reported about the array processing at "Benchmark: Loop for Array Processing using Google Apps Script with V8" and "Search for Array Processing using Google Apps Script". In this report, the process cost checking a value in a one-dimensional array using Google Apps Script has been investigated.
When a value is searched from the 1-dimensional array and a 2-dimensional array, after V8 runtime could be used, I use JSON object, Set object, and Map Object. But, I had never measured the process cost of this situation. In this post, I would like to introduce the process cost for searching a value using a JSON object, Set object, and Map object converted from the 1-dimensional array and 2-dimensional array.
Consumer (personal) version of Google+ is closed on April 2, 2019. By this, Apps Script community of Google+ is also closed. This is one of important communities for discussing. So in this post, I would like to introduce the other communities related to Google Apps Script.
- File upload using doPost on Google Web Apps
- Retrieving Access Token for Google Drive API using GAS
- Create Folder Tree on Google Drive
- Download Files Without Authorization From Google Drive
- How to use "fields" of Drive APIs
- File Transfer for Google Drive Without Authorization
- Converting PDF to TXT
- Retrieving Access Token for Google APIs
- Downloading Files From Google Drive Under No Authorization Using Browser
- (NEW) Retrieve old revision file from Google Drive
- Get File List Under a Folder on Google Drive
- Retrieving Access Token From OneDrive using Google Apps Script
- Interconversion Between Google Docs and Microsoft Docs
- Retrieving Files with Filename Included Special Characters using Google Apps Script
- Selecting Files in Google Drive using Select Box for Google Apps Script
- Uploading Local Files to Google Drive without Authorization using HTML Form
- Retrieving latest created file from PDF files on Google Drive
- Downloading File Using Button of Dialog Box on Google Docs
- Which of Drive API v2 or v3 is used for DriveApp.searchFiles()?
- Resumable Conversion from CSV File with Large Size (> 50 MB) to Several Spreadsheets by Splitting File
- Upload Files to Google Drive using Javascript
- Enhanced makeCopy() using Google Apps Script
- Retrieving file list with folder structure under a specific folder in Google Drive
- Uploading Multiple Files From Local To Google Drive using Google Apps Script
- tarUnarchiver for Google Apps Script
- Converting Many Files to Google Docs using Google Apps Script
- Creating Google Document by Converting PDF and Image Files with OCR using Google Apps Script
- Overwriting Several Google Documents by 2 Text Files using Google Apps Script
- Modifying Revisions of a File on Google Drive using Google Apps Script
- One Time Download for Google Drive
- Resumable Upload of Multiple Files with Asynchronous Process for Google Drive
- Moving File to Specific Folder using Google Apps Script
- Uploading File to Google Drive using HTML and Google Apps Script
- Retrieving Files and Folders without Parents in Google Drive
- Creating Shortcut on Google Drive using Google Apps Script
- Batch Requests for Drive API using Google Apps Script
- Converting SVG Format to PNG Format using Google Apps Script
- Uploading File to Google Drive from External HTML without Authorization
- Using Values Submitted from HTML Form using Google Apps Script
- Achieving Search of Files by 'is:unorganized owner:me' using Google Apps Script
- Safe-Uploading for Google Drive by HTML in External Server using Google Apps Script
- Retrieving Files of 'Shared with Me' in Specific Folder using Google Apps Script
- Workaround: createdDate cannot be used with searchFiles of DriveApp in Google Apps Script
- Workaround: Checking Existence of File ID in Google Drive without Access token and API key
- Retrieving Specific Folders from Google Drive using Google Apps Script
- Transferring Owner of File to Other User using Google Apps Script
- Retrieving Total File Sizes in Specific Folder of Google Drive using Google Apps Script
- Folder Picker using jsTree with Google Apps Script and Javascript
- Comparing File Contents of Files on Google Drive using Google Apps Script
- Move Folder including Files and Folders in Google Drive using Google Apps Script
- Analyzing Folder Structures with Google Apps Script
- Uploading Large Files to Gemini with Google Apps Script: Overcoming 50 MB Limit
- Uploading Multiple Files with Split Asynchronous Processes and Resumable Upload in Google Spreadsheets
- Retrieving ClientId using Google Apps Script
- Copying and Overwriting GAS Project
- Remove Third-party Apps with Account Access using Google Apps Script
- Retrieving Reformatted Scripts without Comments in a Project using Google Apps Script
- Backup Project as zip File using Google Apps Script
- Full-text search of Google Apps Script Projects using Google Apps Script
- Managing History of Google Apps Script
- Download a CSV File from Spreadsheet Using Google HTML Service
- Send E-mail with Excel file converted from Spreadsheet
- Export CSV File from Spreadsheet and Make Download Button
- Creating Downloaded Excel file as Spreadsheet
- Creating Spreadsheet from Excel file
- Converting Spreadsheet to PDF
- Overwriting Spreadsheet to Existing Excel File
- Retrieving User Information with Shared Spreadsheet
- Embedding a Map to a Cell using Custom Function on Spreadsheet
- Embedding Animation GIF in A Cell on Spreadsheet
- Search Route and Embedding Map using Custom Function on Spreadsheet
- Pseudo Browser with Google Spreadsheet
- Retrieving Images on Spreadsheet
- Converting a1Notation to GridRange for Google Sheets API
- Retrieving Values By Header Title for Spreadsheet
- Retrieving Spreadsheet ID from Range using Google Apps Script
- Enhanced onEdit(e) using Google Apps Script
- Measuring Execution Time of Built-In Functions for Google Spreadsheet
- Retrieving Instance of User-Interface Environment
- Automatic Recalculation of Custom Function on Spreadsheet Part 1
- Open Site with New Window using Google Apps Script
- Append Values by Inserting Rows using Google Sheets API
- Retrieves All Named Ranges in Spreadsheet as a1Notation
- CLEAN method for Google Apps Script
- Retrieve Last of Specific Row and Column
- Parsing A1Notations using Google Apps Script
- Closing Existing Sidebar using Google Apps Script
- Opening Dialog Box during Calculation and Retrieving Calculated Result using Google Apps Script
- Creating One-time Writing Cells using Google Apps Script
- Limitations for Inserting Images to Google Docs
- Possibility of Real Time Processes In a Cell on Spreadsheet using Google Apps Script
- Fixing Value Putting by Custom Function of Spreadsheet using Google Apps Script
- Protecting Cells of Spreadsheet that Users Copied from Your Google Drive to User's Google Drive using Google Apps Script
- Retrieving Values from Filtered Sheet in Spreadsheet using Google Apps Script
- Automatic Recalculation of Custom Function on Spreadsheet Part 2
- Retrieving Values from Sheet Filtered by Slicer in Spreadsheet using Google Apps Script
- Dynamically Updating Custom Menu of Google Spreadsheet using Google Apps Script
- Rearranging Columns on Google Spreadsheet using Google Apps Script
- Retrieving Overwrapped Cells Between 2 Ranges on Google Spreadsheet using Google Apps Script
- Updated: Expanding A1Notations using Google Apps Script
- Workaround for Retrieving Direct Links of All Sheets from URL of 2PACX-### of Web Published Google Spreadsheet
- Hiding and Deleting Rows and Columns on Google Spreadsheet using Google Apps Script
- Change Tab Detection on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script
- Enhanced Custom Function for Google Spreadsheet using Web Apps as Wrapper
- Disabling Buttons Put on Google Spreadsheet using Google Apps Script
- Highlighting Row and Column of Selected Cell using Google Apps Script
- Workaround: Putting Multiple Hyperlinks to a Cell using Sheets API
- Search Dialog Sample using TextFinder with Google Apps Script
- Adjusting Text Length to Fit in Cell Width on Google Spreadsheet using Google Apps Script
- Switching Buttons for Google Spreadsheet using Google Apps Script
- Converting Range in Google Spreadsheet as Image using Google Apps Script
- Creating Spreadsheet with Custom Header and Footer using Google Apps Script
- Google Apps Script: Running Specific Function When Specific Sheet is Edited on Google Spreadsheet
- Setting Alternate Background Colors for Rows in Google Spreadsheet using Google Apps Script
- Sorting Cells on Google Spreadsheet with Background colors using Google Apps Script
- Retrieving All Values from All Sheets from URL of 2PACX- of Web Published Google Spreadsheet using Google Apps Script and Javascript
- Creating Multiple Buttons on Google Spreadsheet using Google Apps Script
- User Runs Script for Range Protected by Owner using Google Apps Script
- Downloading Google Spreadsheet as XLSX and PDF file by Clicking Button
- Replacing Multiple Values in Google Spreadsheet with Low Process Cost using Google Apps Script
- Downloading Active Sheet in Google Spreadsheet as CSV and PDF file by Clicking Button
- Copying Protections for Spreadsheet using Google Apps Script
- Creating Colorful Buttons on Google Spreadsheet using Google Apps Script
- Retrieving Hidden Rows and Showing Rows by Filter View on Google Spreadsheet using Google Apps Script
- Compiling Continuous Numbers using Google Apps Script
- Taking Advantage of TextFinder for Google Spreadsheet
- Converting Values of Google Spreadsheet to Object using Google Apps Script
- Updating Values of Sheet A with Values of Sheet B using Google Apps Script
- Letting Users Running Google Apps Script on Google Spreadsheet without both Authorizing Scopes and Showing Script
- Converting Range ID to Range Object on Google Spreadsheet using Google Apps Script
- Retrieving Data from Content-Type of 'text/event-stream' using Javascript and Google Apps Script
- Pseudo OnEdit Trigger for Google Spreadsheet without Simple and Installable Triggers using Google Apps Script
- Checking whether Cells on Google Spreadsheet have Checkboxes using Google Apps Script
- Inverting Selected Ranges on Google Spreadsheet using Google Apps Script
- Detecting Operations to Google Spreadsheet by Owner, Specific Users, and Anonymous Users using Google Apps Script
- Importing CSV Data by Keeping Number Formats of Cells on Google Spreadsheet using Google Apps Script
- Workaround for Inserting Non-public image of Google Drive using IMAGE Function in a Cell on Google Spreadsheet using Google Apps Script
- Counter in Cell of Google Spreadsheet using Infinite Loop with Google Apps Script
- Merging Columns with Same Header Title in Google Spreadsheet using Google Apps Script
- Merging Rows with Same Header Title in Google Spreadsheet using Google Apps Script
- Merging Rows with Same Header Title in Google Spreadsheet using Google Apps Script
- Increasing Column Letter by One using Google Apps Script
- Number of Requests for Sheets API using Google Apps Script
- Expanding Rows in Google Spreadsheet using Google Apps Script
- Retrieving and Parsing XML data from Google Workspace Update Blog and Putting it to Google Spreadsheet using Google Apps Script
- Retrieving Text Positions in Text Data using Google Apps Script
- Parsing JSON Data Array by Expanding Header Row using Google Apps Script
- Replacing Values in Cells on Google Spreadsheet using Google Apps Script
- Parsing XML Data in Google Apps Script using IMPORTXML
- Protecting Cells of Spreadsheet by Clicking Checkbox using Google Apps Script
- Converting A1Notation to GridRange and vice versa using Google Apps Script without any Scopes
- Removing Invalid Named Ranges from Google Spreadsheet using Google Apps Script
- Checking Exchange Rate using GOOGLEFINANCE with Google Apps Script
- Creating User's Dashboard by Inputting Name and Password using Web Apps with Google Apps Script
- Clearing Discrete Cell Values on Multiple Sheets using Google Apps Script
- Using RichTextValues with Custom Function on Google Spreadsheet
- Moving Cell Detection on Google Spreadsheet using Google Apps Script
- Updating Destination Sheet by Source Sheet in Google Spreadsheet using Google Apps Script
- Detecting Cells with Quote Prefix in Google Spreadsheet using Google Apps Script
- Showing Specific Rows and Columns in Google Spreadsheet using Google Apps Script
- Retrieving Cell Coordinates of Cells with Quote Prefix using Google Apps Script (Single Quote)
- Retrieving Named Functions from Google Spreadsheet using Google Apps Script
- Updating Array1 with Array2 using Google Apps Script
- Putting Multiple Hyperlinks to a Cell using Sheets API with Google Apps Script and Node.js
- Workaround: Detecting to Edit Google Spreadsheet using Sheets API with Service Account
- Copying and Deleting Dimension Groups in Google Spreadsheet using Google Apps Script
- Rearranging Columns on Google Spreadsheet using Google Apps Script
- Using OnEdit Trigger to Google Spreadsheet by Hiding Google Apps Script from Other Users
- Using OnEdit trigger on Google Spreadsheet Created by Service Account
- Workaround: Retrieving Hyperlink from Cell of Number Value using Google Apps Script
- Putting Values of All Spreadsheets in Folder to Master Spreadsheet with Low Process cost using Google Apps Script
- Retrieving Start and End Row Numbers of Same Values in a Column on Google Spreadsheet using Google Apps Script
- Removing Quote Prefix of Cell value using Google Apps Script (Single Quote)
- Importing Microsoft Excel to Google Spreadsheet using Custom Function with Google Apps Script
- Enriched Management of Rich Text on Google Spreadsheet using Google Apps Script
- Putting TOTP into Google Spreadsheet using Google Apps Script
- Workaround: Automatically Installing OnEdit Trigger to Copied Google Spreadsheet using Google Apps Script
- Putting Image into Cell of Spreadsheet using Google Apps Scrip
- Unpivot on Google Spreadsheet using Google Apps Script
- Converting Google Spreadsheet to HTML Table using Google Apps Script
- Converting Relative Reference to Absolute Reference and vice versa of A1Notation on Google Spreadsheet using Google Apps Script
- Copying Google Spreadsheet by Removing Container-Bound Script Using Google Apps Script
- Automatically Refreshing Basic Filter on Google Spreadsheet using Google Apps Script
- Overwrapped Cells on Google Spreadsheet using Google Apps Script
- Benchmark: Efficiently Deleting Rows by Conditions on Google Spreadsheet using Google Apps Script
- Focusing Selected Cell to Top Left on Google Spreadsheet using Google Apps Script
- Copy Date Object between Google Spreadsheets with Different Timezone using Google Apps Script
- Workaround: Detecting Change of IMPORTRANGE using OnEdit trigger with Google Apps Script
- Uploading Files without Authorizing Scopes by Shared Users with Dialog on Google Spreadsheet using Google Apps Script
- Technique for Processing Google Spreadsheet Including Merged Cells using Google Apps Script
- Technique of Array Processing for Custom Functions on Google Spreadsheet using Google Apps Script
- Workaround: Making Users Edit Protected Cells using Google Apps Script
- Technique for Managing Rich Text on Google Spreadsheet using Google Apps Script
- Allowing Access by IMPORTHTML, IMPORTDATA, IMPORTFEED, IMPORTXML, and IMPORTRANGE on Google Spreadsheet with Google Apps Script
- Technique for Protecting Google Spreadsheet using Google Apps Script
- Copying Sheet Including Charts from Google Spreadsheet to Another Google Spreadsheet using Google Apps Script
- Technique for Appending Values to Specific Columns on Google Spreadsheet using Google Apps Script
- Identifying Colored Cell Regions in Google Sheets with Google Apps Script
- Consolidate Scattered A1Notations into Continuous Ranges on Google Spreadsheet using Google Apps Script
- Inserting Animated GIFs over Cells on Google Sheets using Google Apps Script
- Retrieving Number of Lines of Google Document
- Replacing Text to Image for Google Document using Google Apps Script
- Modify Shading Color of Paragraph on Google Document using Google Apps Script
- Modify Searched Text to Small Capital Letters using Google Apps Script
- Deleting Positioned Images on Google Document using Google Apps Script
- Limitations for Inserting Images to Google Docs
- Retrieving Total Page of Google Document using Google Apps Script
- Deleting Pages of Google Document using Google Apps Script
- Creating New Table and Putting Values to Cells using Google Docs API with Google Apps Script
- Deleting Last Empty Page of Google Document using Google Apps Script
- Updated Specification of Google Spreadsheet: Multiple Hyperlinks to a Cell
- Modifying 1st-Page Header in Google Document using Google Apps Script
- Retrieving All URLs in Google Document using Google Apps Script
- Changing Font of Selected Text to 'Google Sans' on Google Document using Google Apps Script
- Replacing Template Texts with Array in Google Document using Google Apps Script
- Retrieving Glyph Value from List Items of Google Document using Google Apps Script
- Pseudo OnEdit Trigger for Google Document using Google Apps Script
- Report: Inserting Multiple Paragraphs to Google Document in Order using Google Docs API
- Retrieving Summary of Google Document using Google Apps Script
- Replacing Multiple Paragraphs on Google Document with a Regex using Google Apps Script
- Retrieving Values of Dropdown List of Smart Chips on Google Document using Google Apps Script
- Replacing Images on Google Document in Order using Google Apps Script
- Updating Same Position on Google Document using Google Apps Script
- Converting Large images to Google Document by OCR using Google Apps Script
- Removing Vertical Borders of Table in Google Document using Google Apps Script
- Retrieving Values of Calendar Events of Smart Chips on Google Document using Google Apps Script
- Set Line Space of Paragraph on Google Document using Google Apps Script
- Inserting Paragraphs with Checkboxes in Google Documents using Google Apps Script
- Workaround: Exporting Google Documents as HTML with Image Hyperlinks
- Managing Footnotes on Google Documents using Google Apps Script
- Convert Soft Breaks to Hard Breaks on Google Documents using Google Apps Script
- Techniques for Creating Nested Lists on Google Documents using Google Docs API
- Convert Google Document to Markdown and vice versa using Google Apps Script
- Retrieving Size of Tables in Google Slides using Google Apps Script
- Summarizing Slides as Thumbnails
- Limitations for Inserting Images to Google Docs
- Managing Texts on Google Slides using Google Apps Script
- Cropping Images in Google Slides using Google Apps Script
- Retrieving Data from QR code on Google Slides using Google Apps Script
- Adding Slide Page Link to Shape using Google Apps Script
- Creating Custom Grid View of Google Slides as Image and Spreadsheet using Google Apps Script
- Simple Photo Gallery Created by Google Slides and Web Apps using Google Apps Script
- Simply Editing Texts of Texts Boxes on Google Slides using Google Apps Script
- Reducing Table Height of Table Inserted from Google Spreadsheet to Google Slides using Google Apps Script
- Exporting All Thumbnail Images Retrieved from Google Slides as Zip File using Google Apps Script
- Inverting Selected Objects on Google Slides using Google Apps Script
- Managing Row Height and Column Width of Table on Google Slides using Google Apps Script
- How to Retrieve Replied Emails for Gmail
- Adding a Label to a Message using Message ID for Gmail
- Sending Gmail with Title and Body Including Emoji using Google Apps Script
- Sending Multiple Emails using Batch Request with Gmail API using Google Apps Script
- Converting Gmail Message to Image using Google Apps Script
- Searching Gmail Messages by Gmail Filters using Google Apps Script
- Flexible Labeling for Gmail using Gemini Pro API with Google Apps Script
- Running Google Apps Script by Event Notification from Google Calendar
- Retrieving Event ID from Event URL of Google Calendar using Google Apps Script
- Managing A Lot Of Google Calendar Events using Batch Requests with Google Apps Script
- Sample Scripts for Creating New Event with Google Meet Link to Google Calendar using Various Languages
- Directly Submitting Answers to Google Form using Google Apps Script
- Putting All Response Values from Google Form to Google Spreadsheet using Google Apps Script
- Using Google Forms API with Google Apps Script
- Creating Quizzes in Google Form using Google Forms API with Google Apps Script
- Creating Quizzes in Google Form using Google Forms Service with Google Apps Script
- Analyzing Responses from Grid Items of Google Form using Google Apps Script
- Opening and Closing Google Forms on Time using Google Apps Script
- Uploading Movie File on Google Drive to YouTube using Google Apps Script
- Retrieving subscriberCount of Channel from Video URLs of YouTube using Google Apps Script
- Curl Command Uploading Video File to YouTube with Resumable Upload using YouTube API
- Uploading Video File on Google Drive to YouTube with Resumable Upload using Google Apps Script
- Making charts at spreadsheet
- Embedding a Chart to a Cell using Custom Function on Spreadsheet
- Changing Line to Bars for Combo Chart using GAS
- Adding Title of vAxis to Embedded Chart on Spreadsheet using Google Apps Script
- Changing Slack Status using Google Apps Script
- Uploading Image Files to Slack Using Incoming Webhooks by Google Apps Script
- Using Dialog Box of Slack by Google Apps Script
- Bitfinex API for Google Apps Script
- Binance API for Google Apps Script
- Bittrex API for Google Apps Script
- Zaif API for Google Apps Script
- Cryptopia API for Google Apps Script
In this case, the APIs and resources of Microsoft are used with Google Apps Script.
- OnedriveApp : This is a library of Google Apps Script for using Microsoft OneDrive.
- Retrieving List of All Emails of Microsoft Account using Google Apps Script
- Sending Outlook Emails using Microsoft Account with Google Apps Script
- OCR using Google Drive API
- Multipart-POST Request Using Google Apps Script
- Changing Values by Checking Duplicated Values of JSON for Javascript
- Straightening Elements in 2 Dimensional Array using Google Apps Script
- Batching Requests for Google Apps Script
- Transposing JSON Object using Google Apps Script
- Retrieve Difference Between 2 Dimensional Arrays using Google Apps Script
- About Updated Utilities.computeHmacSignature()
- Retrieving a Key with Maximum Value from Object
- Adding Query Parameters to URL using Google Apps Script
- Retrieving Screen Shots of Sites using Google Apps Script
- Retrieving Access Token for Service Account using Google Apps Script
- Parsing Query Parameters from URL using Google Apps Script
- Split Array by n Elements using Google Apps Script
- Processing Duplicated Rows of 2 Dimensional Arrays using Google Apps Script
- Retrieving Values with and without Duplicating from JSON Object using Google Apps Script
- Parsing HTML using Google Apps Script
- Examples of How to Derive a Signing Key for Signature Version 4 (AWS) for Google Apps Script
- Running Functions by Specifying Function Names with Web Apps for Google Apps Script
- URL Encode with Shift-JIS using Google Apps Script
- Retrieving Difference Between 2 Arrays using Google Apps Script
- Sample Scripts for Requesting to Web Apps by Various Languages
- Logs in Web Apps for Google Apps Script
- Workaround: Showing Log in Web Apps to Apps Script Dashboard using Javascript
- IMPORTANT: reduceRight with and without v8 runtime for Google Apps Script
- Inserting Text on Image using Google Apps Script
- Request of multipart/form-data with Simple Request Body using Google Apps Script
- Converting Texts to Bold, Italic and Bold-Italic Types of Unicode using Google Apps Script
- Xpath Tester using Web Apps Created by Google Apps Script
- Plotting Points on Image using Google Apps Script
- Creating PNG Image with Alpha Channel using Google Apps Script
- Executing Function with Minutes timer in Specific Times using Google Apps Script
- Converting from String to Hex, from Hex to Bytes, from Bytes to String using Google Apps script
- Simple Method for using ggsrun
- Simply Converting HTML to Plain Text using Google Apps Script
- Reducing Image Data Size using Google Apps Script
- Splitting and Processing an Array every n length using Google Apps Script
- Requesting to Gate API v4 using Google Apps Script
- Exporting Tabulator Data to Google Drive using Google Apps Script
- Shortening a Long URL using Firebase Dynamic Links API with Google Apps Script
- Retrieving Names of Month and Day of Week using Google Apps Script
- Retrieving Icons of each mimeType on Google Drive using Google Apps Script
- Requesting with Keeping Cookies using Google Apps Script (SessionFetch)
- Retrying UrlFetchApp by an Error using Google Apps Script (RetryFetch)
- Retrieving Batch Path for Batch Requests using Google Apps Script
- Using getBatchGet, batchCreateContacts, batchDeleteContacts, batchUpdateContacts of People API with Google Apps Script
- Workaround: Reflecting Latest Script to Deployed Web Apps Created by Google Apps Script without Redeploying
- Encrypting and Decrypting with AES using crypto-js with Google Apps Script
- Decrypting Salted Base64 of finance.yahoo.com using Google Apps Script
- Filtering JSON object using Google Apps Script
- Retrieving Start and End of Month in Year using Google Apps Script and Javascript
- Merging Multiple PDF Files as a Single PDF File using Google Apps Script
- Converting All Pages in PDF File to PNG Images using Google Apps Script
- Replacing U+00A0 with U+0020 as Unicode using Google Apps Script
- January 27, 2023: Decrypting Salted Base64 of finance.yahoo.com using Google Apps Script
- February 15, 2023: Decrypting Salted Base64 of finance.yahoo.com using Google Apps Script
- Issue of HTML form with Input tab of Type File with google.script.run
- Exporting Google Docs Files in PDF format with Batch Requests using Google Apps Script
- Split Binary Data with Search Data using Google Apps Script
- Directly Retrieving Values from XLSX data using SheetJS with Google Apps Script
- Workaround: Starting Animation GIF on Google Slide by Clicking
- Retrieving Release Notes of Google Apps Script and Google APIs from RSS using Google Apps Script
- Notifying New Release of Google APIs and Google Apps Script with Email using Google Apps Script
- Converting Various Formatted Images to PNG Format and JPEG format using Google Apps Script
- Exporting Specific Pages From a PDF as a New PDF Using Google Apps Script
- Management of PDF Metadata using Google Apps ScriptManagement of PDF Metadata using Google Apps Script
- Changing Order of Pages in PDF file using Google Apps Script
- Retrieving and Putting Values for PDF Forms using Google Apps Script
- Creating PDF Forms from Google Slide Template using Google Apps Script
- Embedding Objects in PDF using Google Apps Script
- Cooking PDF over Google Apps Script
- Add Header and Footer to Exported PDF from Google Spreadsheet using Google Apps Script
- Executing Google Apps Script with Service Account
- Inserting HTML including Javascript on Web Apps Created by Google Apps Script
- Retrieve Comments with Emoji Reactions from Google Documents, Google Slides, and Google Spreadsheets using Google Apps Script
- Automatically Creating Descriptions of Files on Google Drive using Gemini Pro API with Google Apps Script
- Returning Class Object of Google Apps Script with JSDoc
- Use Microsoft Docs Files (Word, Excel, and PowerPoint) with Document Service, Spreadsheet Service, and Slides Service of Google Apps Script
- Convert Contact URL to Resource Name for People API using Google Apps Script
- Empowering Everyone to Leverage Various Google APIs using Google Apps Script
- Adding Page Numbers to PDF using Google Apps Script
- Checking API Enabled with Advanced Google Services using Google Apps Script
- Enhancing HTML and Javascript Development in Script Editor of Google Apps Script: Overcoming Formatting Challenges
- Web App Log Visibility for Google Apps Script
- Downloading Files Under Specific Folder using Node.js
- Send mails from Gmail using Nodemailer
- Create Folder Tree of Google Drive using Node.js
- Directly Using Access Token by googleapis for Node.js
- Creating New Google Docs and Overwriting Existing Google Docs by Text with Node.js without using googleapis
- Retrieving Access Token using Service Account for Node.js without using googleapis
- Creating a Table to Google Document by Retrieving Values from Google Spreadsheet for Node.js
- Music Streaming Player for Discord with Google Drive using Node.js
- Simple Script of Resumable Upload with Google Drive API for Node.js
- Uploading Files of multipart/form-data to Google Drive using Drive API with Node.js
- Retrieving All Values from All Sheets from URL of 2PACX- of Web Published Google Spreadsheet using Node.js
- Downloading and Uploading File to Google Drive without Saving File with Stream and Resumable Upload using Node.js
- Simple Script of Resumable Upload with Google Drive API for Axios
- Sample Script for Resumable Upload to Google Drive using Axios with Node.js
- Creating and Deleting Multiple Events in Google Calendar by Batch Requests using Calendar API with Node.js
- Putting Multiple Hyperlinks to a Cell using Sheets API with Google Apps Script and Node.js
- A Versatile Approach to Uploading Files with Node.js: Integrating Gemini, Drive, YouTube, and Other APIs
- spreadsheets.values.batchUpdate using Golang
- Uploading CSV File as Spreadsheet and Modifying Permissions using Golang
- Using String Values to []googleapi.Field for Golang
- Retrieving Access Token using Service Account by Google's OAuth2 package for Golang
- Sorting for Slice using Golang
- Creating a Table to Google Document by Retrieving Values from Google Spreadsheet for Golang
- Resumable Uploading Files to Google Drive using Golang
- Setting Number Format of Cells on Google Spreadsheet using batchUpdate in Sheets API with golang
- Using Request Body of String JSON for Google APIs with googleapis of golang
- Updating Thumbnail of File on Google Drive using Python
- Uploading Files From Local To Google Drive by Python without Quickstart
- Creating a Table to Google Document by Retrieving Values from Google Spreadsheet for Python
- Simple Script of Resumable Upload with Google Drive API for Python
- Retrieving All Values from All Sheets from URL of 2PACX- of Web Published Google Spreadsheet using Python
- Replacing Template Texts with Array in Google Document using Docs API with Python
- Uploading Files to Google Drive with Asynchronous Process using Python
- Retrieving Access Token from Service Account using oauth2client and google-auth with Python
- Using Until Expiration Time of Access Token Retrieved By googleapis for Python
- Resumable Download of File from Google Drive using Drive API with Python
- File Upload and Download with File Convert For curl using Drive API
- Downloading Shared Files on Google Drive Using Curl
- Updating a File with Resumable Upload using Drive API
- Uploading Image Files to Google Photos using axios
- Retrieving Access Token for Service Account using Javascript
- Using Google API Client Library (gapi) for JavaScript with Service Account
- Retrieving Values from Publicly Shared Google Spreadsheet using API key with Javascript
- Automatically Creating Descriptions of Files on Google Drive using Gemini Pro API with Google Apps Script
- Flexible Labeling for Gmail using Gemini Pro API with Google Apps Script
- Expanding Error Messages of Google Apps Script using Gemini Pro API with Google Apps Script
- Inserting Generated Text to Google Documents, Google Spreadsheets, and Google Slides using Gemini Pro API with Google Apps Script
- Semantic Search using Gemini Pro API with Google Apps Script
- Categorization using Gemini Pro API with Google Apps Script
- Flexible Labeling for Gmail using Gemini Pro API with Google Apps Script Part 2
- Semantic Search using Corpus of Gemini API with Google Apps Script
- Applying Gemini Pro API to Flexible Templates using Google Apps Script
- Guide to Function Calling with Gemini and Google Apps Script
- Creating Image Bot using Gemini with Google Apps Script
- Similarity Viewer using Gemini API with Google Spreadsheet and Google Apps Script
- Enhanced Search using Gemini API
- Attempting Reverse Engineering with Gemini API and Google Apps Script
- Crafting Bespoke Output Formats with Gemini API
- Generating Texts using Files Uploaded by Gemini 1.5 API
- Analyzing Trends of Google Apps Script from Questions on Stackoverflow using Gemini 1.5 API
- Parsing Invoices using Gemini 1.5 API with Google Apps Script
- Specifying Output Types for Gemini API with Google Apps Script
- Batch Processing Powerhouse: Leverage Gemini 1.5 API and Google Apps Script for Efficient Content Workflows
- Taming the Wild Output: Effective Control of Gemini API Response Formats with response_mime_type
- Gemini API with JSON schema
- Leveraging Gemini 1.5 API for Automated Test Case Generation in Google Apps Script Reverse Engineering
- Taming the Wild Output: Effective Control of Gemini API Response Formats with response_schema
- Expanding Error Messages for Google Apps Script with Gemini 1.5 Flash
- Unlock Smart Invoice Management: Gemini, Gmail, and Google Apps Script Integration
- Uploading Large Files to Gemini with Google Apps Script: Overcoming 50 MB Limit
- Gemini API: Revolutionizing Content Generation with Direct PDF Input
- A Novel Approach to Learning: Combining Gemini with Google Apps Script for Automated Q&A
- Leveraging GCP for Seamless Google Apps Script Log Export and Analysis with Gemini API
- Expanding Gemini API's Capabilities: A Practical Solution for Web Content Summarization
- A Versatile Approach to Uploading Files with Node.js: Integrating Gemini, Drive, YouTube, and Other APIs