spreadsheet

Gists Abstract A new library, MimeTypeApp, simplifies using Gmail messages and attachments with the Gemini API for tasks like text analysis. It converts unsupported formats for seamless integration with Google Apps Script and Gemini. Introduction Recently, I published MimeTypeApp, a Google Apps Script library that simplifies parsing Gmail messages, including attachments, for use with the Gemini API. Ref This library addresses a key challenge: Gmail attachments come in various MIME types, while the Gemini API currently only accepts a limited set for processing.

Gists Description Recently, I reported on a workaround for effectively working with Google Sheets tables using Google Apps Script: Ref. This approach addressed limitations in directly retrieving table data and ranges within Apps Script. In this follow-up report, I’m excited to provide a sample script that leverages this workaround to export your valuable Google Sheets tables directly as PDF files. This functionality empowers you to easily share and distribute your data in a clear and universally accessible format.

Gists Abstract Google Sheets’ new Tables feature enhances data organization but lacks direct management via Apps Script. This report proposes a workaround solution using Apps Script until native support arrives. Introduction Google Sheets recently introduced a new feature called Tables. Ref Tables offer a powerful way to organize and manage your data by transforming unformatted ranges into structured datasets with automatic headers, filtering options, and data validation capabilities. This not only improves the readability and maintainability of your spreadsheets but also allows for seamless integration with existing Google Sheets functions.

Gists Abstract 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. Introduction 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.

Gists Abstract Gemini excels at text generation with RAG for large datasets, but smaller ones benefit from prompting or data upload. This report explores using Gemini 1.5 Flash/Pro with RAG on medium-sized, Google Spreadsheet-stored datasets for improved accuracy and effectiveness. Introduction Gemini’s text generation capabilities have seen significant advancements with the Retrieval-Augmented Generation (RAG). This approach excels for large datasets, where embedding data and querying the model leads to high-quality answers.

Abstract This post introduces a Google Apps Script solution that automates the creation, sharing, and monitoring of multiple Google Spreadsheets, providing a more efficient and streamlined approach to managing user data. Introduction I’ve often encountered requests from clients who need to manage multiple Google Spreadsheets for various users, often by copying a template spreadsheet. In these situations, I typically propose the following approach: Create a Template Spreadsheet: This spreadsheet serves as a blueprint, containing essential elements like custom functions implemented using Google Apps Script.

Gists Overview This script demonstrates how to insert an animated GIF over cells in a Google Sheet using Google Apps Script. Description I recently received a request to create a Google Apps Script for inserting animated GIFs into cells on a Google Sheet. I previously published a sample script on my blog on June 6, 2017. Ref In that script, the animation GIF was inserted using a public link. This new script leverages data URLs, which simplifies the process for using GIFs stored in Google Drive.

RichTextAssistant was updated to v1.0.1. v1.0.1 (April 22, 2024) From oshliaer’s report, a bug was removed. In the current stage, when RichTextValueBuilder is used, it seems that when setTextStyle is used after setLinkUrl, the style of the hyperlink is removed while the link is kept. So, it is required to set setLinkUrl after setTextStyle. You can see the detail information here https://github.com/tanaikech/RichTextAssistant

Gists Abstract Consolidate scattered cell references (A1Notation) in Google Sheets for efficiency. This script helps select cells by background color or update values/formats, overcoming limitations of large range lists. Introduction When working with Google Spreadsheets, there might be a scenario where you need to process scattered A1Notations (cell addresses in the format “A1”). This could involve selecting cells with specific background colors, updating cell values, or modifying cell formats. One approach to handle scattered A1Notations is to create a range list containing the individual cell coordinates and activate it.

Gists Overview This Google Apps Script helps identify and analyze regions of colored cells in a Google Sheet. Description Recently, I encountered a situation where I needed to identify colored cell regions in Google Sheets. For instance, consider the following spreadsheet: The region enclosed by the red cells (B2:D4) is a rectangle. In this case, the closed region can be easily identified using a simple script in Google Sheets. However, the region enclosed by the blue cells (H3, I2, J2,,,) is more complex.

Gists Abstract This report addresses the challenge of appending values to specific columns in Google Sheets when columns have uneven last rows. It offers a Google Apps Script solution with a sample script and demonstration image, enabling efficient and flexible data manipulation. Introduction Google Apps Script is a versatile tool that allows for seamless management of various Google Workspace applications, including Docs, Sheets, Slides, Forms, and APIs. Its ability to automate tasks within Google Sheets is particularly powerful.

Gists Abstract This report presents a workaround for copying a sheet, including its charts, from Google Spreadsheet “A” to Google Spreadsheet “B” using Google Apps Script. Description In this report, I would like to introduce a workaround for the following goal: Copying a sheet including charts from Google Spreadsheet “A” to Google Spreadsheet “B” using a script. In general, when Sheets API is used, all objects of the Spreadsheet can be copied easily.

Gists Abstract Google Apps Script automates tasks like managing protections in Google Spreadsheets. These protections control user access to specific cells. While scripts exist for this purpose, users still encounter challenges, prompting this report. The report aims to introduce techniques for managing protections using sample scripts, helping users understand and implement this functionality. Introduction Google Apps Script is a powerful tool that enables seamless management of Google Documents, Spreadsheets, Slides, Forms, APIs, and more.

Gists Abstract The Gemini API enables both content generation and semantic search, managing data effectively. This report introduces a Gemini-powered similarity viewer for easy visualization of complex text similarity scores, using Google Spreadsheet and Apps Script. Introduction The Gemini API unlocks new possibilities, extending its capabilities beyond content generation to encompass semantic search. Within this context, the API excels at efficiently managing data within corpora. While semantic search provides valuable similarity scores (chunkRelevanceScore) for text pairs, interpreting these numerical values can be cumbersome.

Gists Updated on April 26 From this X and this blog, it seems that in the current stage, this situation can be resolved using Sheets API. The sample script of Google Apps Script is as follows. Before you test this, please enable Sheets API at Advanced Google services. function myFunction() { // Please set your Spreadsheet ID. const spreadsheetId = "###"; const requests = [{ updateSpreadsheetProperties: { properties: { importFunctionsExternalUrlAccessAllowed: true }, fields: "importFunctionsExternalUrlAccessAllowed" } }]; Sheets.

DocsServiceApp was updated to v1.2.2 v1.2.2 (January 30, 2024) Remove a bug in ExcelApp. When the inserted image had no data, an error occurred. This issue was removed. You can see the detail information here https://github.com/tanaikech/DocsServiceApp

Gists Abstract Google Apps Script automates tasks (even offline) and builds web apps using spreadsheets as databases. This report presents a basic dining reservation system to illustrate key aspects of web app development with Apps Script, HTML, and Javascript. Introduction Google Apps Script is one of the powerful automation tools for achieving the automation process. When Google Apps Script can be used for the situation, even when users are away from their computers, automation can continue thanks to cloud computing.

Gists Description When the generated text can be automatically inserted into the cursor position of Google Document, Google Spreadsheet, and Google Slide, it will be useful for users. This report introduces sample scripts for achieving this. Sample scripts Here, I would like to introduce 3 sample scripts for a Google Document, a Google Spreadsheet, and a Google Slide. Create an API key These sample scripts request Gemini Pro API using an API key.

Gists Abstract One day, you might have a situation where you are required to manage rich texts in Google Spreadsheet using Google Apps Script. In this report, I would like to introduce the basic technique for managing rich texts with Google Apps Script. Introduction At Google Spreadsheet, rich texts can be used as a cell value. The rich texts can be also managed by Google Apps Script. Ref When I saw the official document related to the rich texts with Google Apps Script, I’m worried that it might be difficult a little for users to manage the rich texts using Google Apps Script.

Gists Abstract One day, you might have a situation where you are required to make users edit the protected cells using Google Apps Script. This report introduces a workaround for achieving this situation. The key factors for achieving this are as follows. 1. Run the script as the owner of Spreadsheet even when the script is run by a user. 2. In order to run the script as the owner, the installable triggers and the Web Apps are used.

Gists Description At Google Spreadsheet, custom functions created by Google Apps Script can be used. Ref When the custom function is used, the users can create a function for expanding the built-in functions for Spreadsheet. One day, you might have a situation for executing the custom function using an array. For example, under the situation that your custom function uses a single value instead of an array as the argument, when you want to use an array for each argument of the custom function, it is required to modify the original script of the custom function or the function calling the custom function.

Gists Description At Google Spreadsheet, the cells can be merged as one cell. But, when the Spreadsheet including the merged cells is used with Google Apps Script, the script becomes a bit complicated. Also, I sometimes find some questions like this situation on Stackoverflow. In this report, I would like to introduce a technique for easily using the Spreadsheet including the merged cells with Google Apps Script. Principle Before it introduces the sample scripts, I would like to introduce the principle for using the Spreadsheet for the merged cells.

Gists Abstract One day, you might have a situation where you are required to make the shared users upload a file and text using a dialog or sidebar on Google Spreadsheet to your Google Drive and Spreadsheet without authorization by the users. This report introduces a solution for achieving this situation. Introduction Google Spreadsheet can run Javascript on a dialog and a sidebar. Ref These can be used as a strong tool for working on Spreadsheet.

Gists Abstract One day, you might have a situation where you are required to update a sheet using Google Apps Script when the cell values retrieved by IMPORTRANGE are changed. This report introduces a workaround for achieving this situation. Introduction Google Apps Script can be executed by several triggers. Ref When a cell in a Google Spreadsheet is manually edited, a function of Google Apps Script can be executed by detecting this edit.

Gists Abstract This report introduces the method for retrieving the Emoji reactions from the comments in Google Docs files (Google Documents, Google Slides, and Google Spreadsheets) using Google Apps Script. Introduction Recently, the Emoji reactions have been implemented in the comments on Google Docs files (Google Documents, Google Slides, and Google Spreadsheets). Ref With this implementation, the collaborative work has been higher. Here, it is considered that when the Emoji reactions can be retrieved from the Google Docs files, the statistics of the reactions will be also more useful for increasing collaboration.

HtmlFormApp was updated to v1.0.2. v1.0.2 (October 17, 2023) The 2nd argument row of appendFormData(object, row) was added. This is from this suggestion. When row is used, the value is put into the specific row of the Spreadsheet. In this case, please set the value of row more than 1. In this case, the submitted row can be forcefully put into the specific row of Google Spreadsheet.

Gists This is a sample script for copying the date object between Google Spreadsheets with the different time zones using Google Apps Script. One day, you might have a situation in which it is required to copy the date object between Google Spreadsheets with the different time zones using Google Apps Script. In this post, I would like to introduce the sample scripts for achieving this. Sample situation Here, the sample situation is declared.

Gists This is a sample script for moving the selected cell to the top left on Google Spreadsheet to focus it using Google Apps Script. One day, there might be a case where you are required to focus the specific cell on Google Spreadsheet to help edit cells. In this post, I would like to introduce a sample script for achieving this. Sample script Please copy and paste the following script to the script editor of Google Spreadsheet, and save the script.

Gists Description In this report, I would like to introduce a sample script for efficiently deleting rows by conditions on Google Spreadsheet using Google Apps Script. Recently, I had a situation for being required to achieve this situation. In my report, it has already known that when Sheets API is used, the rows can be efficiently deleted by a condition. Ref However, in that case, Sheets API couldn’t be used. Under this situation, I came up with a method.

Gists This is a sample script for checking the overwrapped cells of multiple ranges on Google Spreadsheet using Google Apps Script. When applications are developed, there might be a case that it is required to confirm whether 2 ranges on Google Spreadsheet are overwrapped. In this post, I would like to introduce a sample script for achieving this. Method: getOverwrappedCells The following script is a method of getOverwrappedCells. This is the main script of this post.

Gists This is a sample script for automatically refreshing the basic filter on Google Spreadsheet using Google Apps Script. Description A sample situation is as follows. In this sample, the basic filter is set to columns “B” and “D”. Column “B”: When the checkbox is checked, the row is hidden. Column “D”: When the cell value is multiples of 3, the row is hidden. In this case, the custom function =MOD(E2,3)<>0 is used.

Gists This is a sample script for copying a Google Spreadsheet including a container-bound script by removing the container-bound script using Google Apps Script. When you want to copy a Google Google Spreadsheet including a container-bound script by removing the container-bound script using Google Apps Script, this could be achieved by using “get” and “create” methods of Sheets API before. The sample script is as follows. const obj = Sheets.Spreadsheets.get( SpreadsheetApp.

Gists This is a sample script for converting the relative reference to the absolute reference and vice versa of A1Notation on Google Spreadsheet using Google Apps Script. Description A1Notation is used in the cells on Google Spreadsheet. As the 1st sample, it supposes that a formula of =A1 is put into a cell “B1”. Under this condition, when the cell “B1” is copied to “B2” and “C1”, the cells “B2” and “C1” have the formulas of =A2 and =B1, respectively.

Gists This is a sample script for retrieving the release notes of Google Apps Script and Google APIs from RSS using Google Apps Script. Recently, the release notes of Google Apps Script and Google APIs have been published as RSS. By this, the data got to be able to be easily retrieved using XmlService of Google Apps Script. Knowing the latest release notes will be useful for developing the applications. So, I would like to introduce the sample script for retrieving this information.

Gists Abstract 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.

HtmlFormApp was updated to v1.0.1. v1.0.1 (May 29, 2023) When multiple files are uploaded, each URL was set as the hyperlink. You can see the detail information here https://github.com/tanaikech/HtmlFormApp

Gists This is a sample script for converting Google Spreadsheet to an HTML table using Google Apps Script. There is the case that it is required to convert a sheet in a Google Spreadsheet to an HTML table. For example, there might be a situation that a sheet in a Google Spreadsheet is sent as an email including an HTML table. And, there might be a situation in which a sheet in a Google Spreadsheet is published to an HTML page including the converted HTML table.

Gists This is a sample script for converting the values on Google Spreadsheet as unpivot (reverse pivot) using Google Apps Script. Sample script 1: const SAMPLE1 = ([[, ...header], ...values]) => header.flatMap((h, i) => values.map((t) => [h, t[0], t[i + 1]])); In the sample, the source data is “A1:D8”. And, a custom function of =SAMPLE1(A1:D8) is put into “F1”. When this script is used, the result showing the above image (most left table to middle table) is obtained.

Gists Abstract 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.

Gists These are sample scripts for putting an image into a cell of a Spreadsheet using Google Apps Script. Sample 1 In this sample, the image is put into a cell using thumbnailLink retrieved by Drive API. So, when you test this, please enable Drive API at Advanced Google services. The image is put into cell “A1”. function sample1() { const fileId = "###"; // Please set the file ID of the PNG image file on Google Drive.

Gists This is a workaround for automatically installing the OnEdit trigger to the copied Google Spreadsheet using Google Apps Script. The sample situation for this workaround is as follows. You have a Google Spreadsheet. Your Spreadsheet is shared with a user as the writer. Your Spreadsheet has a button for executing a script for copying the active Spreadsheet. Your Spreadsheet has a function installedOnEdit for executing by the installable OnEdit trigger.

Gists In this post, I would like to introduce a sample script for putting Time-based One-time Password (TOTP) value into Google Spreadsheet using Google Apps Script. In this sample script, I used a Javascript library of https://github.com/hectorm/otpauth . In the current stage, Google Apps Script can run with V8 runtime. By this, it seems that this library can be used with Google Apps Script. Sample script function myFunction() { const secret = "ABCDEFGHIJKLMN23"; // Please set your secret here.

Gists In the current stage, Google Spreadsheet can use rich texts in cells. The rich texts can be also managed by Google Apps Script. But, I thought that creating a script for editing the existing rich text in the cell might be a bit complicated. Because, for example, in the current stage, when the text of the rich text of a cell is changed using a script, all text styles are cleared.

Overview This is a GAS library for supporting editing RichText in Google Spreadsheet using Google Apps Script. Description There is RichTextApp in my published libraries. RichTextApp can be used mainly for converting RichText to Google Documents and vice versa. This library RichTextAssistant will support editing the rich text in Google Spreadsheets using Google Apps Script. Google Spreadsheet can use rich text as the cell value using Google Apps Script. But, I thought that when I created a script for editing the existing rich text in the cell, it might be a bit complicated.

Gists This is a sample script for the folder picker using jsTree with Google Apps Script and Javascript. I have already published “File Picker using Google Apps Script and Javascript without 3rd party”. In this post, jsTree is used. Usage 1. Install Google Apps Script library. In this script, “FilesApp” of my Google Apps Script library is used. So, please install it. You can see how to install it at here.

Gists This is a sample script for importing Microsoft Excel (XLSX) data to Google Spreadsheet using a custom function with Google Apps Script. Usage 1. Install SheetJS library. Please copy the script of the SheetJS library from https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js, and paste the script to the script editor of Google Spreadsheet, and save the script. In this case, I would like to recommend the following flow. Add a new script to the script editor.

Gists In Google Spreadsheet, when a single quote is added to the top letter of the cell value, the cell is used as the text value. About detecting this, I have already reported in this post in my blog. In this post, I would like to introduce a sample script for removing the single quote at the top character of the cell value. Sample script: function sample() { const sheetName = "Sheet1"; // Please set your sheet name.

Gists This is a sample script for retrieving the start and end row numbers of the same values in a column on Google Spreadsheet using Google Apps Script. There is a case in that I want to retrieve the rows of the same values in a column on Google Spreadsheet using Google Apps Script. In this post, I would like to introduce a simple sample script for achieving this. Sample script function myFunction() { const sheet = SpreadsheetApp.

Gists This is a sample script for putting the values of all Spreadsheets in a folder to the master Spreadsheet with a low process cost using Google Apps Script. There is a case in that I want to collect the values from multiple Spreadsheets and put the values into the master Spreadsheet. When this situation is achieved by Google Apps Script, as the general method, the values are required to be retrieved from each Spreadsheet in a loop.

Gists This is a workaround for retrieving the hyperlink from the cell of a number value using Google Apps Script. As a sample situation, it supposes that a cell “A1” has a number value like 123, and a hyperlink of https://tanaikech.github.io is set to the cell. In order to retrieve the hyperlink from the cell, it is required to use the methods of getRichTextValue() and getRichTextValues(). But, in the current stage, when the cell value is a number value, when the RichText is retrieved by getRichTextValue(), null is returned.

Gists In the current stage, by the current specification, Google Apps Script cannot be directly run on Google Spreadsheet created by Service Account. But, there is a case in that we want to use the OnEdit trigger on the Spreadsheet that the service account is the owner. In this post, I would like to introduce the method for achieving this. Recently, I published “Using OnEdit Trigger to Google Spreadsheet by Hiding Google Apps Script from Other Users”.

Gists This is a method for using OnEdit Trigger to Google Spreadsheet by hiding Google Apps Script from other users. A sample flow for achieving this is as follows. Flow 1. Create a new Google Spreadsheet. Please create a new Google Spreadsheet. In this flow, this Google Spreadsheet is used for testing the script. And, please copy the Spreadsheet ID. This spreadsheet ID is used. In this case, even when Spreadsheet has no container-bound script, this goal can be achieved.

Gists This is a sample script for rearranging columns on Google Spreadsheet using Google Apps Script. Sample script In this sample script, the initial columns of “header1”, “header2”, “header3”, “header4” and “header5” are rearranged to “header2”, “header5”, “header1”, “header4”, “header3”. This result can be seen at the above image. As an important point, in this script, the header titles in the 1st row are used. Please be careful about this.

Gsits This is a sample script for retrieving the values from a publicly shared Google Spreadsheet using an API key with Javascript. Sample script 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.

Gists In this post, I would like to introduce 2 sample scripts for copying and deleting the dimension groups in Google Spreadsheet using Google Apps Script. Unfortunately, in the current stage, all dimension groups cannot be copied by one action, and also, all dimension groups cannot be deleted by one action. In this post, these can be achieved using Google Apps Script. These sample scripts use Sheets API. So, please enable Sheets API at Advanced Google services.

Gists This is a workaround for detecting to edit Google Spreadsheet using Sheets API with the service account. It has already been found that when Google Spreadsheet is edited using Sheets API, this can be detected by the installed OnChange trigger. For example, when a Spreadsheet is edited using Sheets API with the access token retrieved from your Google account, the event object of the installable OnChange trigger includes your email address and nickname.

Gists I have submitted a report of “Workaround: Putting Multiple Hyperlinks to a Cell using Sheets API” before. At that time, there are no direct methods for setting multiple hyperlinks to the part of text in a cell. But, recently, textFormatRuns was added to Sheets API. By this, multiple hyperlinks got to be able to be set to the part of text in a cell. In this report, I would like to introduce a sample script for this.

Gists 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. Conditions When the values of column “A” of Array2 are existing in column “A” of Array1, the rows of Array1 are updated by that of Array2. When the values of column “A” of Array2 are not existing in column “A” of Array1, the rows of Array2 are appended to Array1.

Overview 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. DocsServiceApp was updated to v1.2.0 v1.2.0 (September 29, 2022) Added a new method of getNamedFunctions(). This method can retrieve the named functions from Google Spreadsheet. You can see the detail information here https://github.

Gists This is a sample script for retrieving the named functions from Google Spreadsheet using Google Apps Script. Recently, the named functions got to be able to be used in Google Spreadsheet. Ref When several named functions are added, I thought that I wanted to retrieve these functions using a script. But, unfortunately, in the current stage, it seems that there are no built-in methods (SpreadsheetApp and Sheets API) for directly retrieving the named functions.

Gists This sample script retrieves the cell coordinates of cells with the quote prefix. In Google Spreadsheet, when a single quote is added to the top letter of the cell value, the cell is used as the text value. When we want to search the cells with the quote prefix in Spreadsheet, unfortunately, in the current stage, this cannot be achieved using Spreadsheet service (SpreadsheetApp) and Sheets API. In this method, such cells can be retrieved.

Overview 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. DocsServiceApp was updated to v1.1.0 v1.1.0 (September 28, 2022) Added a new method of getQuotePrefixCells(). This method can detect the cells with the quote prefix cells. You can see the detail information here https://github.

Gists This is a sample script for showing the specific rows and columns in Google Spreadsheet using Google Apps Script. When you export a Google Spreadsheet as a PDF file, you might have a case where you want to export the specific rows and columns in a sheet. In this post, I would like to introduce the sample script for achieving this. Script Please copy and paste the following scripts to the script editor of Google Spreadsheet.

Gists This is a sample script for detecting cells with the quote prefix in Google Spreadsheet using Google Apps Script. For example, when a value is put to a cell by adding a single quote ' as the top character, the cell value is used as the string value. This is the current specification. Under this condition, when the cells with the value of the quote prefix are tried to be detected, unfortunately, in the current stage, it seems that there is no method for directly achieving this in the methods of Spreadsheet service (SpreadsheetApp).

Gists Today, I found a question ( https://stackoverflow.com/q/73540735 ) in Stackoverflow by Max Makhrov. When I saw this question, I thought that this is a good raising question. In this question, it has reported that when the Spreadsheet includes the formulas of the large calculation, when a Google Apps Script is run during the calculation of the formulas is running, the processing time of the script becomes long. I have also experienced this before.

Gists 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.

Gists This is a sample script for updating the destination sheet by the source sheet in Google Spreadsheet using Google Apps Script. The sample situation is as follows. Sample script function myFunction() { const sheetNames = ["Sheet1", "Sheet2"]; // Retrieve values from source and destination sheets. const ss = SpreadsheetApp.getActiveSpreadsheet(); const [srcSheet, dstSheet] = sheetNames.map((s) => ss.getSheetByName(s)); const [srcValues, dstValues] = [srcSheet, dstSheet].map((s) => s.

Gists This is a sample script for detecting whether the specific cells on Google Spreadsheet are manually moved using Google Apps Script. In this case, the named range, OnChange trigger, and PropertiesService are used. Usage: 1. Create a named range. As a sample, please create a named range to the cells “A2:B2” as “sampleNamedRange1”. Ref 2. Prepare sample script. Please copy and paste the following script to the script editor of Spreadsheet.

Gists In this post, I would like to introduce the method for using RichTextValue with a custom function of Google Apps Script. This sample is for this thread in Stackoverflow. In this thread, the OP’s goal is as follows. Put a text to a cell. In this case, use a hyperlink in a part of the text. This is required to be achieved using a custom function. In the current stage, in order to reflect the hyperlink in a part of the text, it is required to use setRichTextValue of Google Apps Script.

Gists Updated on January 25, 2024 Overview: This is a report for challenging exporting the selected cells on Spreadsheet as an image using Google Apps Script and Javascript. Description: This report is based on this question by Max Makhrov. When I saw this question, I remembered that there are many questions for asking this in Stackoverflow. And, I thought that when this is achieved, it will be useful for the owner of this question and a lot of users.

Gists This is a sample script for clearing the discrete cell values on multiple sheets using Google Apps Script. There might be a case where you want to clear the values of the discrete cells in the multiple sheets using Google Apps Script. In this post, I would like to introduce the efficient script for achieving this. Sample script 1 Please copy and paste the following script to the script editor of the Google Spreadsheet you want to use.

Gists This is a sample script for creating the user’s dashboard by inputting the user name and password using Web Apps with Google Apps Script. In this case, Google Spreadsheet is used as a database of the dashboard. Usage 1. Create a Google Apps Script project. In order to use Web Apps, please create a new Google Apps Script project. In this case, please create a new Spreadsheet and open the script editor of Spreadsheet.

Gists This is a sample script for checking the exchange rate using GOOGLEFINANCE with Google Apps Script. Recently, I have published a report of “Report: Obtaining Values from GOOGLEFINANCE using Google Apps Script”. Ref In this post, I would like to introduce a sample script for checking the exchange rate using Google Apps Script. Sample script Please copy and paste the following script to the script editor of Spreadsheet. // When this script is run, a trigger for executing "checkCurrency" function is installed.

Gists 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. Ref At January 19, 2022, Class CellImageBuilder and Class CellImage have been added to Spreadsheet Service. Ref 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.

Gists This is a sample script for removing the invalid named range of #REF from Google Spreadsheet using Google Apps Script. Issue and workaround For example, there are 2 sheets of “Sheet1” and “Sheet2” in a Google Spreadsheet. A new named range of sample is created for the range of Sheet1!A1, and remove the sheet of “Sheet1”. By this flow, when the named range list is checked by UI on Spreadsheet, sample has #REF.

Gists This is a sample script for converting A1Notation to GridRange and vice versa using Google Apps Script without any scopes. A1Notation and GridRange are often used with Sheets API. I have posted a sample script for converting A1Notation to GridRange before. Ref But, in that case, I used the method of Spreadsheet service (SpreadsheetApp). By this, in order to use the script, it is required to authorize the scopes. In this sample script, A1Notation can be converted to GridRange and vice versa with no scopes.

Gists This is a sample script for protecting the cells of a Spreadsheet by clicking a checkbox using Google Apps Script. You might have a situation where you want to protect the cells when a user checks a checkbox. This sample script is for achieving this situation. The demonstration of this sample script is as follows. This demonstration is for a user. You can see that when the checkbox is checked, the checkbox and the right side of the checkbox are protected.

Gists This is a sample flow for parsing XML data in Google Apps Script using IMPORTXML. Recently, it seems that ContentService.MimeType.XML has been removed by the Google side. By this, in the current stage, the XML data cannot be directly loaded by the Web Apps URL with IMPORTXML. From this current situation, I would like to introduce a workaround. In this workaround, the XML data in Google Apps Script is parsed by IMPORTXML of the built-in function of Google Spreadsheet.

Gists 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. Usage 1. Create a Google Docs. First, as a simple sample, please create a new Google Spreadsheet. And please copy the URL like https://docs.

Gists This is a sample script for replacing values in the cells on Google Spreadsheet using Google Apps Script. Sample situation The sample situation is as follows. Sample script This sample script used Sheets API. So, please enable Sheets API at Advanced Google services. function myFunction() { const obj = { sample1: "ab", sample2: "cd", sample3: "ef", sample4: "gh", sample5: "ij", sample6: "kl", sample7: "mn", sample8: "op", sample9: "qr", }; const ss = SpreadsheetApp.

Gists This is a sample script for parsing JSON data array by expanding the header row using Google Apps Script. Sample script function myFunction() { const obj = [ { key1: "value1", key2: "value2", key3: "value3" }, { key4: "value1", key5: "value2", key6: "value3" }, { key7: "value1", key8: "value2", key9: "value3" }, ]; const headers = Object.keys( obj.reduce((o, e) => (Object.keys(e).forEach((k) => (o[k] = true)), o), []) ); const values = [headers, .

RichTextApp was updated to v1.4.0 v1.4.0 (May 25, 2022) Added a new method of ReplaceTextToRichText. In this method, the text in a cell is converted to the richtext. You can see the detail information here https://github.com/tanaikech/RichTextApp

Gists This is a sample script for retrieving the text positions in the text data using Google Apps Script. For example, in order to set the rich text style the part of text from the text data, this sample script will be useful. Sample situation 1 The sample situation is as follows. sample1, sample2, sample3, sample4, sample5 sample1, sample2, sample3, sample4, sample5 sample1, sample2, sample3, sample4, sample5 In this sample, the text positions of sample2 and sample5 are retrieved from this sample text data.

Gists This is a sample script for retrieving and parsing the XML data from Google Workspace Update Blog and putting it to Google Spreadsheet using Google Apps Script. At Google Workspace Update Blog, the XML data is provided. By this, the retrieved XML data is parsed with XmlService, and the data is put to Google Spreadsheet. Recently, I got a request for this. So I created this sample script. When this was useful for your situation, I’m glad.

Gists 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. When I had tested this before, I had got the value of #N/A. About retrieving the values from GOOGLEFINANCE function on Google Spreadsheet, I had known “Historical GOOGLEFINANCE data no longer accessible outside of Google Sheets”.

Gists This is a sample script for expanding the rows in Google Spreadsheet using Google Apps Script. The sample situation is as follows. Sample situation Input Output Sample script function myFunction() { const expandedColumns = [2, 3, 4, 5]; // Please set the expanded columns you expect. const delimiter = "\n"; // Please set the delimiter. const srcSheetName = "Sheet1"; // Please set the source sheet name. const dstSheetName = "Sheet2"; // Please set the destination sheet name.

Gists This is a report for checking the number of requests for Sheets API. I had contact about the quota for Sheets API. So, in order to explain this, I used the following simple sample scripts. Sample 1 This sample puts a value of “sample” to a cell “A1” using the batchUpdate method. This request body includes one request. When this script is run, one API quota is used. function sample1() { const spreadsheetId = "###"; const sheetId = 0; const requests = [{ "updateCells": { "rows": [ { "values": [ { "userEnteredValue": { "stringValue": "sample" } } ] } ], "range": { "sheetId", "startRowIndex": 0, "endRowIndex": 1, "startColumnIndex": 0, "endColumnIndex": 1 }, "fields": "userEnteredValue.

Gists This is a sample script for increasing the column letter by one using Google Apps Script. Sample 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 ?

Gists This is a sample script for analyzing the responses from Grid Items of Google Form using Google Apps Script. In this sample situation, all responses are retrieved from Grid Items of Google Form, and the average values of each row of each question are calculated. And, the result is put on the Spreadsheet. Sample situation Input: Sample Google Form The sample Google Form is as follows. Output: Sample Spreadsheet The sample output is as follows.

Gists Introduction 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.

Gists This is a sample Google Apps Script for processing the values in Google Spreadsheet. In this sample situation, each row is merged using the same header title. In this sample script, the sample input and output situations are as follows. Sample situation Input: Output: Sample script In this sample script, this sample can be used as the custom function. function SAMPLE(values) { const headers = [ ...new Set( values .

Gists This is a sample Google Apps Script for processing the values in Google Spreadsheet. In this sample situation, each column are merged using the same header title. In this sample script, the sample input and output situations are as follows. Sample situation Input: “Sheet1” Output: “Sheet2” Sample script function myFunction() { const srcSheetName = "Sheet1"; // This sheet is "Input" situation. const dstSheetName = "Sheet2"; // This sheet is "Output" situation.

Gists Introduction 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.

Gists This is a sample script of a counter in a cell of Google Spreadsheet using the infinite loop with Google Apps Script. Recently, I have reported about the infinite loop on Google Spreadsheet. Ref This sample script achieves a counter in a cell using the infinite loop. Sample script This sample script is a test script for counting the number using the infinite loop. Please be careful this. Please copy and paste the following script to the script editor of Google Spreadsheet and save it.

Gists 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. Ref Sample script: Occurring infinite loop This sample script is a test script for confirming the infinite loop. Please be careful this. Please copy and paste the following script to the script editor of Google Spreadsheet and save it. And, please install OnChange trigger to the function onChange().

RichTextApp was updated to v1.3.1 v1.3.1 (February 8, 2022) Modified the method of DocumentToSpreadsheet. Before this update, the paragraphs with only line breaks have been ignored. From this version, such paragraphs are included in the result value of the cell. You can see the detail information here https://github.com/tanaikech/RichTextApp

Overview This is a Google Apps Script library for parsing the form object from HTML form and appending the submitted values to the Spreadsheet. Description There is Google Form in the Google service. Google Form can parse the submitted data and put it in the Spreadsheet. But when we want to use the custom form, it is required to use the HTML form on Web Apps, dialog, and sidebar. In this case, it is required to prepare Javascript and Google Apps Script for parsing the form object from the HTML form and appending the parsed values to Spreadsheet.

On January 19, 2022, 2 classes of CellImageBuilder and CellImage have been added to the Spreadsheet Service. CellImageBuilder: https://developers.google.com/apps-script/reference/spreadsheet/cell-image-builder CellImage: https://developers.google.com/apps-script/reference/spreadsheet/cell-image Unfortunately, in the current stage, it seems that the image manually put to a cell without using the URL cannot still be retrieved. When an image is manually put from URL and Google Drive to a cell, this image cannot be retrieved. When an image is put using setSourceUrl method with a script, this image and URL cannot be retrieved.

Gists This is a workaround for inserting the non-public image of Google Drive using IMAGE function in a cell on Google Spreadsheet using Google Apps Script. When an image on Google Drive is inserted to a cell of Spreadsheet using =IMAGE(URL) function, the image of URL is required to be publicly shared. But, there is the case that the image cannot be publicly shared. This workaround might be able to be used for this situation.

Gists This is a sample script for importing a CSV data by keeping the number formats of cells on Google Spreadsheet using Google Apps Script. When a CSV data is manually put using the default UI using the browser, it seems that the number formats of all cells cannot be kept. In order to import the CSV data to the cells with keeping the number formats, it is required to use a script as a workaround.

Gists This is a report for detecting the operations to Google Spreadsheet by the owner, the specific users, and the anonymous users using Google Apps Script. When the Spreadsheet is shared with the specific users and/or the anonymous users, when these users can be identified, it will be useful. In this report, I would like to introduce the method for identifying the users who are the owner, the specific users, and the anonymous users.

Gists This is a sample script for inverting the selected ranges on Google Spreadsheet using Google Apps Script. I have the case that I want to invert the selected ranges on Google Spreadsheet. This sample script can be achieved this goal using Google Apps Script. Sample script Please copy and paste the following script to the script editor of Google Spreadsheet, and save the script. And, please select the cells and run the function main().

Gists This is a sample script for checking whether the cells on Google Spreadsheet have checkboxes using Google Apps Script. When the checkboxes are used in Google Spreadsheet, there is the case that it is required to know whether the cells have the checkboxes. This sample script can be used for such the situation. Sample script 1 This sample script can check whether all cells in “A1:B10” have the checkboxes. When all cells in “A1:B10” have the checkboxes, res is true.

Gists This is a sample script for achieving the pseudo OnEdit trigger for Google Spreadsheet without the simple and the installable triggers using Google Apps Script. Today, I saw a question at Stackoverflow. The goal of this question is as follows. There is a Google Spreadsheet created by a service account. Goal is to use OnEdit trigger on this Spreadsheet. I thought a workaround for achieving this goal.

Gists This is a sample script for retrieving the data from Content-Type of ’text/event-stream’ using Javascript and Google Apps Script. In the current stage, UrlFetchApp of Google Apps Script cannot be retrieved the data from Content-Type of ’text/event-stream’. This sample script can be used for achieving this as a workaround. This sample script uses EventSource. So this script uses a dialog on Google Docs files (This sample uses Google Spreadsheet.).

Gists This is a workaround for converting the range ID to the range object on Google Spreadsheet using Google Apps Script. When the named range is put to a cell as the hyperlink as follows, the hyperlink is like #rangeid=123456789. When this link is clicked, it moves to the cells of the named range. So it is considered that this value of #rangeid=123456789 includes the information about the range of the named range.

Gists This is a sample workaround for letting users running Google Apps Script on Google Spreadsheet without both authorizing the scopes and showing the script. The flow of this workaround is as follows. Create Web Apps created by Google Apps Script and deploy it as Web Apps. As the returned value, the XML data is returned. Your script can be included in this script. User put a formula of =IMPORTML("WebApps URL", "xpath") to a cell.

Gists This is a sample script for updating the values of “Sheet A” with the values of “Sheet B” using Google Apps Script. I often see this situation at Stackoverflow and other sites. So, in this post, I would like to introduce the sample script using Google Apps Script. Sample script function myFunction() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const [src, dst] = ["Sheet1", "Sheet2"].map((e) => ss.getSheetByName(e)); const obj = src .

Gists This is a report about images put with “=IMAGE(IMAGE_URL)” function on Google Spreadsheet. Experiment When “=IMAGE(IMAGE_URL)” is put to a cell “A1” on Spreadsheet, the image is shown in the cell as shown in the following image. For this situation, when the following script is run, const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); const range = sheet.getRange("A1"); range.copyTo(range, { contentsOnly: true }); The following result is obtained. In this case, the formula is removed and an image can be seen as shown in the following image.

GitHub This is a sample script for the file picker using Google Apps Script and Javascript without 3rd party. I had created the same sample script before. Ref But, in the case of that script, jQuery is used. And, only Google Drive of own account could be used. In this sample script, 3rd party of jQuery is not used, and also, not only Google Drive of your own account, but also Google Drive of the service account can be used.

Gists This is a sample script for converting the values of Google Spreadsheet to an object using Google Apps Script. Sample script function myFunction() { const sheetName = "Sheet1"; const [headers, ...rows] = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getDataRange().getValues(); const res = rows.map((r) => headers.reduce((o, h, j) => Object.assign(o, { [h]: r[j] }), {})); console.log(res); } When this script is run, the above sample image can be retrieved. In this sample script, the 1st row of the sheet is used as the header row.

RichTextApp was updated to v1.3.0 v1.3.0 (October 20, 2021) Added a new method of RangeToHTMLTableForSpreadsheet. In this method, the range on Google Spreadsheet is converted to a HTML table. Using this method, for example, you can send the specific range in the Spreadsheet as an email by including a HTML table. You can see the sample HTML table at https://jsfiddle.net/oq9x458e/. I used this method for this thread of Stackoverflow.

Gists There is Class TextFinder in Spreadsheet service for Google Apps Script. Ref The TextFinder can search and replace the texts in the Spreadsheet using Google Apps Script. There is the method for createTextFinder in Class Spreadsheet, Class Sheet and Class Range. When these methods are used, you can search and replace the texts for all sheets in a Spreadsheet, the specific sheet, and the specific range in the specific sheet.

Gists This is a sample script for putting all response values from Google Form to Google Spreadsheet using Google Apps Script. Sample script Please copy and paste the following script to the script editor of Google Spreadsheet and set the variables of formId and sheetName. function myFunction() { const formId = "###"; // Please set the Google Form ID. const sheetName = "Sheet1"; // Please set the sheet name of sheet you want to put the values.

Gists 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. Sample script At first, please create new Spreadsheet and open the script editor. And please copy and paste the following script.

Gists 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"]. Sample script const compilingNumbers = (ar) => { const { values } = [...new Set(ar.sort((a, b) => a - b))].reduce( (o, e, i, a) => { if ( o.temp.length == 0 || (o.

Gists This is a sample script for retrieving the list of all emails of Microsoft account and putting them to Google Spreadsheet using Google Apps Script. I updated OnedriveApp to v1.2.0 by adding 1 method for retrieving the access token and 7 methods for managing emails of Microsoft account. By this, the emails got to be able to be gotten and sent using Microsoft account using OnedriveApp with Google Apps Script.

Gists Published: September 15, 2021 Updated: September 17, 2021 From the discussions, added data by changing the wait time of LockService for Web Apps. Kanshi Tanaike Introduction When the users try to write to a Spreadsheet using a form, the developers have to consider the concurrent submission from the form. For example, when multiple users submit the data with the form simultaneously, all data are possibly not to be saved to the Spreadsheet.

Gists This is a sample script for retrieving the hidden rows and showing rows by the filter view on Google Spreadsheet using Google Apps Script. In the current stage, there are no methods for directly retrieving the hidden rows and showing rows by the filter view in Spreadsheet service (SpreadsheetApp). And, isRowHiddenByFilter of Class Sheet cannot be used for the filter view. But, fortunately, when Sheets API is used, the filter view can be retrieved and created.

Gists Introduction 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

Gists This is a sample script for creating the colorful buttons on Google Spreadsheet on Google Apps Script. In order to achieve this, I have been looking for the method for creating the PNG image with the alpha channel using Google Apps Script. Recently, finally, I could find it. By this, the goal of this report got to be able to be achieved by the report of “Creating PNG Image with Alpha Channel using Google Apps Script”.

Gists This is a sample script for copying the protections for Spreadsheet using Google Apps Script. When several protections of the sheet protection and the range protection are set to a Google Spreadsheet and the Spreadsheet is copied using the script and the manual copy with the browser, unfortunately, the protections of ranges are not copied. And also, the protections of sheets can be copied. But, the editor emails are not included.

Gists This is a sample script for downloading the active sheet in Google Spreadsheet to the local PC as a CSV file and a PDF file when a button on the side bar and the dialog is clicked. This is created with Google Apps Script and HTML&Javascript. In this post, the script of the previous post was modified. Sample script Please create new Google Spreadsheet and copy and paste the following scripts to the script editor.

Gists This is a sample script for replacing the multiple values with various values in Google Spreadsheet with the low process cost using Google Apps Script. In this script, the batchUpdate method of Sheets API is used. So the request can be done by one API call. When Spreadsheet services is used for this situation, the values are retrieved from the sheet and replaced the retrieved values, and then, the updated values are put to the sheet.

Gists Description 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.

Gists This is a sample script for downloading Google Spreadsheet to the local PC as a XLSX file and a PDF file when a button on the side bar and the dialog is clicked. This is created with Google Apps Script and HTML&Javascript. Sample script Please create new Google Spreadsheet and copy and paste the following scripts to the script editor. And please run openSidebar(). By this, the side bar is opened to the Spreadsheet.

Gists There is a situation that it wants to make users run a script for the range protected by the owner using Google Apps Script. This is a sample script that an user runs a script for the range protected by the owner using Google Apps Script. Demo This demonstration shows the following situations. Spreadsheet is shared with an user. The cell “A1” is protected by the owner.

Gists This is a sample script for creating the multiple buttons on Google Spreadsheet using Google Apps Script. Recently, I have got several contacts about this. I thought that when this is published, it might be useful for other users. So I published this sample script. Sample script Please copy and paste the following script to the script editor of the container-bound script of Google Spreadsheet. And, please set the variables and run the function createButtons.

Gists This is a sample script for retrieving all values from all sheets from URL of 2PACX- of Web Published Google Spreadsheet using Python. In this post, it supposes that the Google Spreadsheet has already been published for Web. Ref Flow The flow of this method is as follows. Retrieve XLSX data from the URL of web published Google Spreadsheet as BytesIO data. The URL is like https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml. XLSX data is parsed with openpyxl.

Gists This is a sample script for retrieving all values from all sheets from URL of 2PACX- of Web Published Google Spreadsheet using Node.js. In this post, it supposes that the Google Spreadsheet has already been published for Web. Ref Before you use this sample script, please install SheetJS js-xlsx. Flow The flow of this method is as follows. Retrieve XLSX data from the URL of web published Google Spreadsheet as the buffer data.

Gists This is a sample script for retrieving all values from all sheets from URL of 2PACX- of Web Published Google Spreadsheet using Google Apps Script and Javascript. In this post, it supposes that the Google Spreadsheet has already been published for Web. Ref Flow The flow of this method is as follows. From the client side, send the URL of web published Google Spreadsheet to the Web Apps created by Google Apps Script.

Gists This is a sample script for sorting the cells on Google Spreadsheet with the background colors using Google Apps Script. Unfortunately, in the current stage, it seems that sort(sortSpecObj) of Class Range cannot directly sort by the background colors of cells. But when Sheets API is used, this goal can be achieved. Here, “SortRangeRequest” of the method of “spreadsheets.batchUpdate” in Sheets API is used. Flow The flow of this sample script is as follows.

Gists This is a sample script for setting alternate background colors for rows in Google Spreadsheet using Google Apps Script. It has already been known when the conditional formatting rule and custom function are used, this can be simply achieved. Ref In this report, I would like to introduce the method for using Google Apps Script. Sample script In this sample script, the values of column “A” are checked. function myFunction() { const colors = { color1: "#f4cccc", color2: "#d9ead3" }; const sheet = SpreadsheetApp.

Gists This is a sample Google Apps Script for running the specific function when the specific sheet is edited. Sample script Please copy and paste the following script to the container-bound script of Spreadsheet and set sheets object. // When the cells are edited, this function is run by the fire of event trigger. function onEdit(e) { // Please set the sheet name and function as follows. const sheets = { Sheet1: functionForSheet1, // Sheet1 is the sheet name.

Gists In order to print and export as PDF file, this is a sample script for converting Spreadsheet to Spreadsheet which has the custom header and footer. In this sample script, DocsServiceApp, which is Google Apps Script library, is used. And, in this case, the Spreadsheet with the custom header and footer is created as new Spreadsheet. Before you use this script, please install DocsServiceApp and enable Drive API at Advanced Google services.

Overview 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. Description The Google services, which are Document service, Docs API, Spreadsheet service, Sheets API, Slides service and Slides API, are growing now. But, unfortunately, there are still the processes that they cannot done.

Another approach 10 Aug 2022: Report: Challenging Exporting Selected Cells on Spreadsheet as Image using Google Apps Script and Javascript Old approach Gists This is a sample script for converting a range in Google Spreadsheet as an image data using Google Apps Script. Unfortunately, there are no methods for directly converting the range in Google Spreadsheet as an image data in the built-in functions. So in this case, as a workaround, Charts Service is used.

Gists These are the sample scripts for achieving the switching buttons for Google Spreadsheet using Google Apps Script. The management of images using Spreadsheet service is growing now. But, in the current stage, in order to achieve the switching buttons, it needs a little ingenuity. In this report, I would like to introduce 4 kinds of the switching buttons. Pattern 1 In this pattern, the drawing is used as the button.

Gists This is a sample script for directly using the request body of the string JSON for Google APIs with googleapis of golang. At googleapis for golang, when Google API is used, it is required to create the request body like this sample script. I have several contacts for creating about such request body. I thought that such script might be a bit difficult for users. I thought that when the string JSON object is directly used for this, it might be useful.

Gists This is a sample script for setting the number format of cells on Google Spreadsheet using batchUpdate in Sheets API with golang. In this case, googleapis for golang is used. The script of the authorization can be seen at the official document. Sample script In this script, the number format of the column “A” is changed to yyyy-mm-dd hh:mm:ss. And, please include https://www.googleapis.com/auth/spreadsheets to the scopes. sheetId := 12345678 // Please set the sheet ID which is not Spreadsheet ID.

RichTextApp was updated to v1.2.0 v1.2.0 (July 3, 2020) Added the method of AutoResizeFontForSpreadsheet. This method can automatically adjust the text length to fit in the cell width by changing the font size. You can see the detail information here https://github.com/tanaikech/RichTextApp

Gists This is a sample script for adjusting the text length to fit in the cell width on Google Spreadsheet using Google Apps Script. In this case, in order to fit to the cell width, the font size is changed. Issue and workaround: Unfortunately, in the current stage, there are no methods for automatically resize the font size for fitting in the cell width in the Spreadsheet service. So in this case, it is required to think of the workaround.

Gists This is a sample script for the search dialog using TextFinder with Google Apps Script. If this sample script could help to indicate the possibility of TextFinder, I’m glad. Demo In this demonstration, the value of test is searched. When "NEXT" is clicked, the next searched value is activated. When "PREVIOUS" is clicked, the previous searched value is activated. The search can be done for all sheets in the Google Spreadsheet.

Gists This is a current workaround for putting the multiple hyperlinks to a cell using Sheets API. Description Recently, at Spreadsheet service, the multiple hyperlinks got to be able to be put to a cell. Ref In this case, it can be achieved using RichTextValue. On the other hand, at Sheets API, in the current stage, there are no methods for directly putting the multiple hyperlinks to a cell. And also, such methods have not been added.

Gists This is a sample script for correctly exporting the charts on Google Spreadsheet as the images using Google Apps Script. In the current stage, using Google Apps Script, when the charts on Google Spreadsheet are exported as the images, it seems that the exported images are not the same with the original one on Google Spreadsheet. About this, today, I could notice that I had answered for 2 questions. Q1, Q2 And also, I had already been reported this at the issue tracker.

RichTextApp was updated to v1.1.3 v1.1.3 (June 17, 2020) The variable name for the error processing was not correct. The bug was removed. Added a sample script for using this library. You can see the detail information here https://github.com/tanaikech/RichTextApp

RichTextApp was updated to v1.1.2 v1.1.0 (June 16, 2020) Add new method of RichTextToHTMLForSpreadsheet. The method of RichTextToHTMLForSpreadsheet can convert the rich texts in the cells to the HTML format. v1.1.1 (June 16, 2020) About the method of RichTextToHTMLForSpreadsheet, I forgot to convert hyperlinks to HTML. This was modified. v1.1.2 (June 16, 2020) When one row and several columns are used as the range, only 1st column is returned.

Gists This is a sample script for highlighting the row and column of the selected cell using Google Apps Script. For this, the OnSelectionChange event trigger is used. Demo Sample script Please copy and paste the following script to the script editor of Spreadsheet. And, please select a cell. By this, the script is run by the OnSelectionChange event trigger. function onSelectionChange(e) { const range = e.range; const sheet = range.

Gists Description This is a sample script for disabling the buttons put on Google Spreadsheet using Google Apps Script. When a script is run by clicking a button on Google Spreadsheet, there is the case that you don’t want to make users run the script in duplicate. This sample script achieves this situation. Demo In this demonstration, 2 types of buttons are used. Those are the drawing and image, respectively. When the button is clicked, the worker of 10 seconds is run.

Overview This is a proposal of the enhanced custom function for Google Spreadsheet using Web Apps as the wrapper. Demo Description When the custom function is used, in the current specification, the most methods except several methods (for example, one of them is UrlFetchApp.) that the authorization is required cannot be used. So for example, when the filenames in the folder are retrieved from the folder name, unfortunately, this cannot be directly achieved.

Gists Abstract 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.

Gists Abstract 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 of onSelectionChange. Demo Experiment Sample script In order to investigate the response speed, I used the following sample script.

Gists onSelectionChange has been released at April 22, 2020. But this couldn’t be used at the released day. But now, I could confirm that this got to be able to be used. So in order to test this event trigger, I prepared a simple sample script. This is a sample script for detecting the change tab on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script. Demo Usage Please copy and paste the following script to the container-bound script of Google Spreadsheet, and save the script.

Gists Recently, it seems that the specification of Google Spreadsheet was updated. Before this, when a cell has only one hyperlink. In this case, the hyperlink was given to a cell using =HYPERLINK("http://www.google.com/", "Google") as following figure. But by the recent update, a cell got to be able to have multiple hyperlinks as following figure. In this case, the hyperlinks are set by the RichTextValue object. In this report, I would like to introduce the method for setting and retrieving the multiple URLs for a cell.

Gists These are the sample scripts for hiding and deleting rows and columns on Google Spreadsheet using Google Apps Script. I sometimes see the questions for hiding and deleting rows and columns on Spreadsheet at Stackoverflow. So here, I would like to introduce the sample scripts for this. In this case, when the process costs of the scripts created by using Spreadsheet service and Sheets API are compared, the cost of script created by Sheets API is much lower than that of script created by Spreadsheet service.

Gists This is a sample script for retrieving the direct links of all sheets from the URL like https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml of the web published Google Spreadsheet. This sample script can be used for the following situation. The Spreadsheet is published to Web and the URL like https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml is known You are not the owner of Google Spreadsheet. You don’t know the Spreadsheet ID and Sheet IDs. Under above situation, unfortunately, the direct links of each sheet cannot be directly retrieved.

Gists This sample script is for expanding a1Notations using Google Apps Script. This was updated from this sample script. Sample script function expandA1Notations_(a1Notations, maxRow, maxColumn) { maxRow = maxRow || "1000"; maxColumn = maxColumn || "Z"; // Ref: https://stackoverflow.com/a/21231012/7108653 const columnToLetter = column => { let temp, letter = ""; while (column > 0) { temp = (column - 1) % 26; letter = String.

The drawing objects on Google Spreadsheet got to be able to be managed by the update at April 2, 2020. Ref By this update, we can assign the function to the drawing objects and remove the drawing objects. The following sample is for removing the drawing objects on the active sheet. Ref SpreadsheetApp.getActiveSheet() .getDrawings() .forEach(e => e.remove());

Gists This is a sample script for retrieving the overwrapped cells between 2 ranges on Google Spreadsheet using Google Apps Script. Please use this with enabling V8. const getOverwrappedRanges_ = (rangeList1, rangeList2) => { if ( rangeList1.toString() != "RangeList" || rangeList2.toString() != "RangeList" ) { throw new Error("Input RangeList object."); } // Ref: https://stackoverflow.com/a/21231012/7108653 const columnToLetter = column => { let temp, letter = ""; while (column > 0) { temp = (column - 1) % 26; letter = String.

Overview 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). Description Google Spreadsheet can use the rich text as the cell value. But I thought that it is difficult for me to directly edit the rich text in a cell. So I wanted to copy the rich text, that I edited at the Google Document, to the cell of Google Spreadsheet.

Gists This is a sample script for rearranging the columns on Google Spreadsheet using Google Apps Script. Sample script In this sample script, the columns are rearranged with an array including the rearranged column indexes. function rearrangeColumns(sheet, ar) { var obj = ar.reduce(function(ar, e, i) { return ar.concat({ from: e + 1, to: i + 1 }); }, []); obj.sort(function(a, b) { return a.to < b.

Overview 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). Description There is the Class TextFinder for searching the text from cells of the Spreadsheet using the Google Apps Script. But in this case, the date object in the cell is used as the string. Namely, the values for searching are used as the same with the values retrieved by getDisplayValues().

Gists This is a sample script for dynamically updating the custom menu of Google Spreadsheet using Google Apps Script. Demo In this demonstration, when the Spreadsheet is opened, 5 functions added to the custom menu. You can see that when a column is added and deleted, the custom menu is updated. Issue and workaround for this goal Unfortunately, in the current stage, when a function is added to the custom menu with addItem method, the argument cannot been able to be used.

Gists Overview This is a sample script for retrieving values from a sheet filtered by Slicer in Spreadsheet using Google Apps Script. Description By the update of Google side at November 6, 2019, Class Slicer was added. And also, for Sheets API, AddSlicerRequest and UpdateSlicerSpecRequest were added. By this, Slicer of Spreadsheet got to be able to be managed with Google Apps Script and other languages. Here, I would like to introduce the method for retrieving values from a sheet filtered by Slicer in Spreadsheet using Google Apps Script.

Gists Description I have already reported about “Automatic Recalculation of Custom Function on Spreadsheet Part 1” at here. Here, I would like to introduce other workaround for forcibly recalculating the custom functions and built-in functions using Class TextFinder. Class TextFinder has added at April 5, 2019. By this, this workaround can be proposed. Sample scripts Pattern 1 If you want to refresh all functions of all sheets in a Spreadsheet, you can use the following script.

GetEditType was updated to v1.0.1. v1.0.1 (October 25, 2019) Updated: This answer was reflected. You can see the detail information here https://github.com/tanaikech/GetEditType

Gists Kanshi Tanaike Introduction Please be careful! This result can be only used for Google Apps Script. There are a limit executing time for Google Apps Script (GAS). That is 6 minutes for Consumer and Google Apps free edition, and 30 minutes for G Suite and Early Access. 1 So many users always have to pay attention to reducing the process cost of scripts. So it is very important to know the process cost of various situations.

Gists This is a sample script for retrieving values from filtered Sheet in Spreadsheet using Google Apps Script. When the values are retrieved the filtered sheet by the basic filter, if setValues() and setDisplayValues() are used, all values without the filter are retrieved. In this script, I would like to introduce the method for retrieving the values from the filtered sheet using Google Apps Script. In order to retrieve the values from the filtered sheet, one method has already been proposed.

Gists This is a sample script for creating a table to Google Document by retrieving values from Google Spreadsheet for Python. Before you use this script, please install python library of gdoctableapppy. $ pip install gdoctableapppy Sample script: This sample script uses Service Account. In this sample script, the values are retrieved from Sheet1!A1:C5 of Spreadsheet, and new table is created to the Document using the values. from google.oauth2 import service_account from gdoctableapppy import gdoctableapp from googleapiclient.

Gists This is a sample script for creating a table to Google Document by retrieving values from Google Spreadsheet for Node.js. Before you use this script, please install Node.js module of node-gdoctableapp. $ npm install --save-dev gdoctableapp or $ npm install --global gdoctableapp Sample script: This sample script uses Service Account. In this sample script, the values are retrieved from Sheet1!A1:C5 of Spreadsheet, and new table is created to the Document using the values.

Gists This is a sample script for creating a table to Google Document by retrieving values from Google Spreadsheet for Golang. Before you use this script, please install go library of go-gdoctableapp. $ go get -v -u github.com/tanaikech/go-gdoctableapp Sample script: This sample script uses Service Account. In this sample script, the values are retrieved from Sheet1!A1:C5 of Spreadsheet, and new table is created to the Document using the values. package main import ( "encoding/json" "fmt" "io/ioutil" "log" "net/http" "os" gdoctableapp "github.

Gists This is the method for protecting cells of Spreadsheet that users copied from your Google Drive to user’s Google Drive using Google Apps Script. Situation: This method supposes the following situation. You want to make users copy a Spreadsheet on your Google Drive to user’s Google Drive. - Your Spreadsheet has several protected ranges. - Your Spreadsheet is shared with the user. - User doesn’t have own folder shared with you.

Overview GetEditType is a GAS library for retrieving the edit types of the OnEdit event trigger of Spreadsheet using Google Apps Script (GAS). Description In the case that the OnEdit event trigger (simple and installable triggers) is used at Spreadsheet, when users manually edited the cell of Spreadsheet, the trigger is fired. At this time, there is the case that I want to know the edit type. For example, I would like to know about the following edit types.

Gists This is a sample script for investigating the possibility of the real time processes in a cell on Google Spreadsheet using Google Apps Script. As a sample situation, it tried the real time clock in a cell on Google Spreadsheet using Google Apps Script. Demo: Usage: When you use this script, please do the following flow. Copy and paste the following script to the script editor (the container-bound script of Spreadsheet).

Gists When an image is inserted to Google Docs (Spreadsheet, Document and Slides) using the method of insertImage using Google Apps Script, there is the case that the error occurs. The error messages are “server error” and “invalid image data”. Here, I would like to introduce the limitations for inserting images to Google Docs. As the result, it was found that the limitation is due to both the mimeTypes and the area of image rather than the file size.

Gists When a chart is created by using EmbeddedChartBuilder of Spreadsheet service, the title of vAxis which is put by setOption("vAxis", {title: "y axis"}) doesn’t work. It is considered that this is a bug. Because I have confirmed that this had worked fine. Ref But this specification had been changed. So I would like to introduce the method for adding the title of vAxis when a chart is created using Google Apps Script.

Gists This sample script is for creating one-time writing cells using Google Apps Script. At first, it supposes the following situation. A Spreadsheet is shared with users. The owner of Spreadsheet is you. After users put a value to a cell, you don’t want to make users edit the cell again. Namely, you want to protect the cell. This sample script achieves above situation. Preparation Before you use this script, please do the following flow.

Gists Benchmark: Reading and Writing Spreadsheet using Google Apps Script October 18, 2018 Updated. In order to compare with Advanced Google Service, a result of Sheets API by UrlFetchApp was added to Appendix. Kanshi Tanaike Introduction Please be careful! This result can be only used for Google Apps Script. There are a limit executing time for Google Apps Script (GAS). That is 6 minutes for Consumer and Google Apps free edition, and 30 minutes for G Suite and Early Access.

Gists This is a sample script for expanding a1Notations using Google Apps Script (GAS). In this script, for example, “A1:E3” is expanded to “A1, B1, C1, D1, E1, A2, B2, C2, D2, E2, A3, B3, C3, D3, E3”. When each cell in “A1:E3” is checked, this script might be able to be used. If this was useful for your situation, I’m glad. Script: function expandA1Notation(a1Notations) { var columnToLetter = function(column) { var temp, letter = ''; while (column > 0) { temp = (column - 1) % 26; letter = String.

Overview RangeListApp is a GAS library for retrieving, putting and replacing values for Spreadsheet by a range list with a1Notation using Google Apps Script (GAS). Description There is Class RangeList as one of classes for Spreadsheet. There is setValue(value) in Class RangeList as a method. setValue(value) puts value to the cells of range list. Recently, when I used this method, I noticed that the following situations what I want cannot be achieved.

Gists This is a sample script for retrieving the last coordinate of the specific row and column. When the methods of getLastRow() and getLastColumn() of Class Range for Spreadsheet are used, the last coordinates of the vertical and horizontal data range can be retrieved. When users want to retrieve the last coordinates of each row and column, there are no methods. So I created this script. I think that there are several scripts for this situation.

Gists Introduction 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.

Gists This is a sample script for Google Apps Script (GAS). This script retrieves all named ranges in Spreadsheet. The names and range of the retrieved named ranges are output as the keys and the values of JSON object, respectively. The sample output is {"name1": "Sheet1!A1:B2", "name2": "Sheet2!B1:C2",,,}. The name of named range has to be only one in the spreadsheet. This was used. Sheets.Spreadsheets.get() of Sheets API can retrieve all named ranges.

Gists In the case appending values to cell by inserting rows, when sheets.spreadsheets.values.append is used, the values are appended to the next empty row of the last row. If you want to append values to between cells with values by inserting row, you can achieve it using sheets.spreadsheets.batchUpdate. When you use this, please use your access token. Endpoint : POST https://sheets.googleapis.com/v4/spreadsheets/### spreadsheet ID ###:batchUpdate Request body : In this request body, it appends the data of “sample1, sample2, sample3” to “A1:A3” of the sheetId of “1234567890”.

Gists In this report, I would like to introduce a workaround for automatically recalculating custom functions on Spreadsheet. 1. Situation The sample situation is below. This is a sample situation for this document. There are 3 sheets with “sheet1”, “sheet2” and “sheet3” of sheet name in a Spreadsheet. Calculate the summation of values of “A1” of each sheet using a custom function. Sample script of the custom function is as follows.

Gists This sample script is for measuring the execution time of built-in functions for Google Spreadsheet. Unfortunately, there are not measurement tools for retrieving the execution time of built-in functions. So I thought of about a workaround. Flow : Import a value to a cell. The value is anything good, because this is used as a trigger. Please do this by yourself. Custom functions cannot use setValue(). So I used onEdit().

Gists 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.

Gists onEdit(e) which is used for the Edit event on Spreadsheet has the old value as e.oldValue. The specifications for this are as follows. When an user edited a single “A1” cell, e of onEdit(e) shows hoge for e.oldValue and fuga for e.value. When an user edited the “A1:A2” multiple cells, e.oldValue and e.value of onEdit(e) are not shown anything. When an user copied and pasted from other cell, e.

Gists Flow : In my sample script, the script was made using the Quickstart. The flow to use this sample script is as follows. For Go Quickstart, please do Step 1 and Step 2. Please put client_secret.json to the same directory with my sample script. Copy and paste my sample script, and create it as new script file. Run the script. When Go to the following link in your browser then type the authorization code: is shown on your terminal, please copy the URL and paste to your browser.

Here, it introduces an application of Retrieving Spreadsheet ID from Range using Google Apps Script. Please check this. https://github.com/tanaikech/getSpreadsheetByRange

Gists This is a sample script for retrieving spreadsheet ID from a range using Google Apps Script. I sometimes want to retrieve spreadsheet ID from ranges. In such case, I always use this. Range -> Retrieve Sheet using getSheet() -> Retrieve Spreadsheet using getParent() -> Retrieve spreadsheet ID var id = "123456789abcdefg"; var sheet = "Sheet"; var cells = "a1:b10"; var range = SpreadsheetApp.openById(id).getSheetByName(sheet).getRange(cells); var id = range.

Gists This is a sample script for retrieving values by header title for Spreadsheet. This is created by Google Apps Script. The main script is as follows. Main script : When the instance is retrieved, all data of the sheet is analyzed. So when the each value is retrieved, the speed is fast. function GetValueByKey(sheetname) { return new getValueByKey(sheetname); }; (function(r) { var getValueByKey; getValueByKey = (function() { getValueByKey.

Gists When it uses Google Sheets API v4, GridRange is used for it as the range property. These sample scripts are for converting from a1Notation to GridRange. You can chose from following 2 scripts. Both scripts can retrieve the same result. Script 1 : This is from me. function a1notation2gridrange1(sheetid, a1notation) { var data = a1notation.match(/(^.+)!(.+):(.+$)/); var ss = SpreadsheetApp.openById(sheetid).getSheetByName(data[1]); var range = ss.getRange(data[2] + ":" + data[3]); var gridRange = { sheetId: ss.

Gist Overview This is a sample script for creating the pseudo browser using Google Spreadsheet. Description I unexpectedly noticed this. I think that this is for off-line browsing using HTML data. So there are many limitations. At first, please confirm them. Limitations It cannot move from opened site to other outside site. If the outer site is opened as a new wind, your own browser is opened and move there.

This sample script is for searching route between place A and B and embedding a map by custom function on Spreadsheet. I think that this method is one of various ideas. Problem When the map is embedded to a cell on spreadsheet as an image, the function =IMAGE() is suitable for this situation. However, Class Maps, setFormula() for importing =IMAGE() and DriveApp.createFile() for creating images from maps also cannot be used for custom functions.

This sample script is for retrieving parameters from a chart. The chart created by both Google Apps Script and manually operation can be used. Creates Chart When a chart is created, it supposes following parameters. var parameters = { "title": "x axis", "fontName": "Arial", "minValue": 0, "maxValue": 100, "titleTextStyle": { "color": "#c0c0c0", "fontSize": 10, "fontName": "Roboto", "italic": true, "bold": false } }; .

This sample script is for embedding animation GIF in a cell using custom function on Spreadsheet. I think that this method is one of various ideas. Problem There are some limitations. Images of jpeg and png can be embedded in a cell using =IMAGE(). But when animation GIF is embedded using it, GIF is not played. insertImage() can insert the animation GIF to sheet. But it is not imported to one cell.

This sample script performs OCR and imports resultant text to a cell using custom function on Spreadsheet. Drive API has a function to do OCR. It was used for this sample. I think that this method is one of various ideas. Problem When OCR is performed and imported the result to a cell on spreadsheet, there are some limitations. DriveApp, UrlFetchApp, setFormula() cannot be used for custom functions. Solution In order to avoid these limitations, I used Web Apps.

This sample script embeds a map to a cell using custom function on Spreadsheet. I think that this method is one of various ideas. Problem When the map is embeded to a cell on spreadsheet as an image, the function =IMAGE() is suitable for this situation. However, setFormula() for importing =IMAGE() and DriveApp.createFile() for creating images from maps also cannot be used for custom functions. Solution In order to avoid these limitations, I used Web Apps.

This sample script embeds a chart to a cell using custom function on Spreadsheet. I think that this method is one of various ideas. Problem When you want to create a chart and embed it to a cell using custom functions, you notice that insertChart() cannot be used. There are some limitations for using custom functions. But insertChart() creates floating charts. So in order to embed a chart to a cell, the function =IMAGE() is suitable for this situation.

Sample data This is a sample data for this sample script. The column B was created by the normal distribution formula, and the column C was created by multiplying random number for column B. A, B, C 1.0, 0.0001, 0.0000 1.5, 0.0009, 0.0006 2.0, 0.0044, 0.0037 2.5, 0.0175, 0.0133 3.0, 0.0540, 0.0236 3.5, 0.1296, 0.0533 4.0, 0.2420, 0.0073 4.5, 0.3522, 0.2468 5.0, 0.3990, 0.0843 5.5, 0.3522, 0.3352 6.0, 0.2420, 0.2201 6.

This sample script sends an e-mail, when spreadsheet was edited from outside by Sheet API v4. When you use this sample, please create a container bound script with spreadsheet which is edited by Sheet API. And please input your e-mail and run firstly a method of createTrigger(). By this, a trigger is installed as onChange(). After this, edit spreadsheet from outside by Sheet API v4. When when spreadsheet was edited from outside by Sheet API v4, I used sendEmail() as a sample, because script editor is closed.

This sample script retrieves the user information which is editing the shared spreadsheet. It was found as follows. User information retrieving by Class Session is the owner and users which installed triggers by themselves. When each user installs a trigger, user information retrieving by Class Session losts the accuracy. So user information has to be retrieved using a temporally installed trigger. Using onOpen(), it cannot directly install triggers and authorize.

This sample script sends an e-mail with an Excel file exported from Spreadsheet as an attachment file. function excelSender() { var sheetID = [Sheet ID]; var xlsxName = [Excel file name]; var params = { "headers" : {Authorization: "Bearer [Retrieved AccessToken]"}, "muteHttpExceptions" : true }; var dUrl = "https://www.googleapis.com/drive/v3/files/" + sheetID + "/export?mimeType=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" var xlsxlFile = UrlFetchApp.fetch(dUrl, params).getBlob().setName(xlsxName); MailApp.sendEmail({ to: [Mail address], subject: "sample subject", body: "sample body", attachments: [xlsxlFile] }); } Is ScriptApp.

Here, I introduce how to download a CSV file from spreadsheet using Google HTML Service. Using “onOpen()”, it addes menu for launching a dialog. After launching the dialog, “getFileUrl()” is launched by pushing a button. “getFileUrl()” exports a CSV file and outputs download URL. The CSV file is downloaded by “executeDownload()”. Please put both HTML and GAS to a GAS project.HTML : download.html <!

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var chart = sheet.newChart() .setChartType(Charts.ChartType.LINE) .asLineChart() .addRange(sheet.getRange('a1:a21')) .addRange(sheet.getRange('b1:b21')) .addRange(sheet.getRange('c1:c21')) .setColors(["green", "red"]) .setBackgroundColor("black") .setPosition(5, 5, 0, 0) .setPointStyle(Charts.PointStyle.MEDIUM) .setOption('useFirstColumnAsDomain', true) .setOption('height', 280) .setOption('width', 480) .setOption('title', 'Sample chart') .setOption('hAxis', { title: 'x axis', minValue: 0, maxValue: 20, titleTextStyle: { color: '#c0c0c0', fontSize: 20, italic: false, bold: false }, textStyle: { color: '#c0c0c0', fontSize: 12, bold: false, italic: false }, baselineColor: '#c0c0c0', gridlines: { color: '#c0c0c0', count: 4 } }) .

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.

About Event Objects For example, it thinks the situation of input text of ’test’ to ‘A1’ on a sheet. When you use only ‘onEdit(e)’ without an installing trigger, ’e’ has following parameters. {authMode=LIMITED, range=Range, source=Spreadsheet, user=, value=test} In this case, the event cannot send an e-mail because of ‘authMode=LIMITED’. When you use “onEdit(e)” with an installing trigger of “Edit”, ’e’ has following parameters. {authMode=FULL, range=Range, source=Spreadsheet, value=test, triggerUid=#####} In this case, the event can send an e-mail because of ‘authMode=FULL’.