spreadsheet

Updated: GAS Library - DocsServiceApp

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.

Retrieving Named Functions from Google Spreadsheet using Google Apps Script

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.

Retrieving Cell Coordinates of Cells with Quote Prefix using Google Apps Script (Single Quote)

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.

Updated: GAS Library - DocsServiceApp

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.

Showing Specific Rows and Columns in Google Spreadsheet using Google Apps Script

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.

Detecting Cells with Quote Prefix in Google Spreadsheet using Google Apps Script

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

Report: Process Cost of Google Apps Script During Large Calculations by Formulas on Google Spreadsheet

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.

Benchmark: High-Efficiency Finding and Replacing Many Values in Google Spreadsheet with Low Process Cost using Google Apps Script

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.

Updating Destination Sheet by Source Sheet in Google Spreadsheet using Google Apps Script

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.

Moving Cell Detection on Google Spreadsheet using Google Apps Script

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.

Using RichTextValues with Custom Function on Google 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.

Report: Challenging Exporting Selected Cells on Spreadsheet as Image using Google Apps Script and Javascript

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

Clearing Discrete Cell Values on Multiple Sheets using Google Apps Script

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.

Creating User's Dashboard by Inputting Name and Password using Web Apps with Google Apps Script

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.

Checking Exchange Rate using GOOGLEFINANCE with Google Apps Script

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.

Report: Management of Images on Google Spreadsheet using Google Apps Script

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.

Removing Invalid Named Ranges from Google Spreadsheet using Google Apps Script

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.

Converting A1Notation to GridRange and vice versa using Google Apps Script without any Scopes

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.

Protecting Cells of Spreadsheet by Clicking Checkbox using Google Apps Script

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.

Parsing XML Data in Google Apps Script using IMPORTXML

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.

Report: Publishing Various Google Docs with Same URL using Google Apps Script

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.

Replacing Values in Cells on Google Spreadsheet using Google Apps Script

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.

Parsing JSON Data Array by Expanding Header Row using Google Apps Script

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

Retrieving Text Positions in Text Data using Google Apps Script

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.

Retrieving and Parsing XML data from Google Workspace Update Blog and Putting it to Google Spreadsheet using Google Apps Script

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.

Report: Obtaining Values from GOOGLEFINANCE using Google Apps Script

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

Expanding Rows in Google Spreadsheet using Google Apps Script

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.

Number of Requests for Sheets API using Google Apps Script

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.

Increasing Column Letter by One using Google Apps Script

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 ?

Analyzing Responses from Grid Items of Google Form using Google Apps Script

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.

Report: Handling 10,000,000 cells in Google Spreadsheet using Google Apps Script

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.

Merging Rows with Same Header Title 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 .

Merging Columns with Same Header Title 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 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.

Applicating Spread Syntax and Destructuring assignment to Google Spreadsheet with Google Apps Script

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.

Counter in Cell of Google Spreadsheet using Infinite Loop with Google Apps Script

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.

Report: Occurring and Resolving Infinite Loop on Google Spreadsheet using Google Apps Script

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().

Updated: GAS Library - RichTextApp

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

GAS Library - HtmlFormApp

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 Spreadsheet Service

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.

Workaround for Inserting Non-public image of Google Drive using IMAGE Function in a Cell on Google Spreadsheet using Google Apps Script

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.

Importing CSV Data by Keeping Number Formats of Cells on Google Spreadsheet using Google Apps Script

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.

Detecting Operations to Google Spreadsheet by Owner, Specific Users, and Anonymous Users using Google Apps Script

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.

Inverting Selected Ranges on Google Spreadsheet using Google Apps Script

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().

Checking whether Cells on Google Spreadsheet have Checkboxes using Google Apps Script

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.

Pseudo OnEdit Trigger for Google Spreadsheet without Simple and Installable Triggers using Google Apps Script

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.

Retrieving Data from Content-Type of 'text/event-stream' using Javascript and Google Apps Script

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

Converting Range ID to Range Object on Google Spreadsheet using Google Apps Script

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.

Letting Users Running Google Apps Script on Google Spreadsheet without both Authorizing Scopes and Showing Script

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.

Updating Values of Sheet A with Values of Sheet B using Google Apps Script

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 .

Report: Images put with IMAGE function on Google Spreadsheet

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.

File Picker using Google Apps Script and Javascript without 3rd party

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.

Converting Values of Google Spreadsheet to Object using Google Apps Script

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.

Updated: GAS Library - RichTextApp

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.

Taking Advantage of TextFinder for Google Spreadsheet

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.

Putting All Response Values from Google Form to Google Spreadsheet using Google Apps Script

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.

Large Decimal Numbers and Exponential Notation for Google Spreadsheet

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.

Compiling Continuous Numbers using Google Apps 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.

Retrieving List of All Emails of Microsoft Account using Google Apps Script

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.

Benchmark: Concurrent Writing to Google Spreadsheet using Form

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.

Retrieving Hidden Rows and Showing Rows by Filter View on Google Spreadsheet using Google Apps Script

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.

Benchmark: Process Costs for Retrieving 1st Empty Cell and 1st Non Empty Cell of Specific Column in Google Spreadsheet using Google Apps Script

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

Creating Colorful Buttons on Google Spreadsheet using Google Apps Script

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

Copying Protections for Spreadsheet 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.

Downloading Active Sheet in Google Spreadsheet as CSV and PDF file by Clicking Button

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.

Replacing Multiple Values in Google Spreadsheet with Low Process Cost using Google Apps Script

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.

Benchmark: Measuring Process Costs for Formulas in Cells on Google Spreadsheet using Google Apps Script

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.

Downloading Google Spreadsheet as XLSX and PDF file by Clicking Button

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.

User Runs Script for Range Protected by Owner using Google Apps Script

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.

Creating Multiple Buttons on Google Spreadsheet using Google Apps Script

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.

Retrieving All Values from All Sheets from URL of 2PACX- of Web Published Google Spreadsheet using Python

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.

Retrieving All Values from All Sheets from URL of 2PACX- of Web Published Google Spreadsheet using Node.js

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.

Retrieving All Values from All Sheets from URL of 2PACX- of Web Published Google Spreadsheet using Google Apps Script and Javascript

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.

Sorting Cells on Google Spreadsheet with Background colors using 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.

Setting Alternate Background Colors for Rows in Google Spreadsheet using Google Apps Script

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.

Running Specific Function When Specific Sheet is Edited on Google Spreadsheet

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.

Creating Spreadsheet with Custom Header and Footer using Google Apps Script

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.

GAS Library - DocsServiceApp

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.

Converting Range in Google Spreadsheet as Image using Google Apps Script

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.

Switching Buttons for Google Spreadsheet using Google Apps Script

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.

Using Request Body of String JSON for Google APIs with googleapis of golang

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.

Setting Number Format of Cells on Google Spreadsheet using batchUpdate in Sheets API with golang

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.

Adjusting Text Length to Fit in Cell Width on Google Spreadsheet using Google Apps Script

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.

Search Dialog Sample using TextFinder with Google Apps Script

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.

Workaround: Putting Multiple Hyperlinks to a Cell using Sheets API

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.

Workaround: Correctly Exporting Charts on Google Spreadsheet as Images using Google Apps Script

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.

Updated: GAS Library - 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.

Highlighting Row and Column of Selected Cell using Google Apps Script

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.

Disabling Buttons Put on Google Spreadsheet using Google Apps Script

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.

Enhanced Custom Function for Google Spreadsheet using Web Apps as Wrapper

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.

Detecting Quickly Checked Checkboxes on Google Spreadsheet using Google Apps Script

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.

Characteristics of Response for onSelectionChange

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.

Change Tab Detection on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps 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.

Updated Specification of Google Spreadsheet: Multiple Hyperlinks to a Cell

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.

Hiding and Deleting Rows and Columns on Google Spreadsheet using Google Apps Script

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.

Workaround for Retrieving Direct Links of All Sheets from URL of 2PACX- of Web Published Google Spreadsheet

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.

Updated: Expanding A1Notations using Google Apps Script

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.

Retrieving Overwrapped Cells Between 2 Ranges on Google Spreadsheet using Google Apps Script

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.

GAS Library - RichTextApp

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.

Rearranging Columns on Google Spreadsheet using Google Apps Script

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.

GAS Library - DateFinder

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().

Dynamically Updating Custom Menu of Google Spreadsheet using Google Apps Script

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.

Retrieving Values from Sheet Filtered by Slicer in Spreadsheet using Google Apps Script

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.

Automatic Recalculation of Custom Function on Spreadsheet Part 2

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.

Benchmark: Importing CSV Data to Spreadsheet using Google Apps Script

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.

Retrieving Values from Filtered Sheet in Spreadsheet using Google Apps Script

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.

Creating a Table to Google Document by Retrieving Values from Google Spreadsheet for Python

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.

Creating a Table to Google Document by Retrieving Values from Google Spreadsheet for Node.js

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.

Creating a Table to Google Document by Retrieving Values from Google Spreadsheet for Golang

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.

Protecting Cells of Spreadsheet that Users Copied from Your Google Drive to User's Google Drive using Google Apps Script

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.

GAS Library - GetEditType

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.

Possibility of Real Time Processes In a Cell on Spreadsheet using Google Apps Script

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

Limitations for Inserting Images to Google Docs

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.

Adding Title of vAxis to Embedded Chart on Spreadsheet using Google Apps Script

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.

Creating One-time Writing Cells 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.

Benchmark: Reading and Writing Spreadsheet using Google Apps Script

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.

Expanding A1Notations using Google Apps Script

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.

GAS Library - RangeListApp

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.

Retrieve Last of Specific Row and Column

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.

Limitation of Images for Inserting to Spreadsheet using Google Apps Script

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.

Retrieves All Named Ranges in Spreadsheet as a1Notation

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.

Append Values by Inserting Rows using Google Sheets API

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

Automatic Recalculation of Custom Function on Spreadsheet Part 1

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.

Measuring Execution Time of Built-In Functions for Google Spreadsheet

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().

Straightening Elements in 2 Dimensional Array using Google Apps Script

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.

Enhanced onEdit(e) using Google Apps Script

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.

spreadsheets.values.batchUpdate using Golang

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.

Retrieving Spreadsheet ID from Range using Google Apps Script

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.

Retrieving Values By Header Title for Spreadsheet

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.

Converting a1Notation to GridRange for Google Sheets API

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.

Pseudo Browser with Google Spreadsheet

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.

Search Route and Embedding Map using Custom Function on Spreadsheet

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.

Giving and Retrieving Parameters for Chart at GAS

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 } }; .

Embedding Animation GIF in A Cell on Spreadsheet

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.

OCR using Custom Function on Spreadsheet

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.

Embedding a Map to a Cell using Custom Function on Spreadsheet

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.

Embedding a Chart to a Cell using Custom Function on Spreadsheet

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.

Changing Line to Bars for Combo Chart using GAS

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.

Sending E-mail When Spreadsheet was Edited from Outside by Sheet API

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.

Retrieving User Information with Shared Spreadsheet

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.

Send E-mail with Excel file converted from Spreadsheet

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.

Download a CSV File from Spreadsheet Using Google HTML Service

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 <!

Making charts at spreadsheet

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 } }) .

Sample Array Script for Spreadsheet

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.

Event of onEdit() for Google spreadsheet

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