GasTips

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 sending multiple emails using the batch request with Gmail API using Google Apps Script. When multiple emails are sent using “GmailApp.sendEmail” and “MailApp.sendEmail”, a loop is used. But in this case, the process cost becomes high. In this post, I would like to introduce the sample script for reducing the process cost under this situation. Gmail API can be requested with the batch request.

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 sending Gmail with the title and body including Emoji using Google Apps Script. Sample script This sample script uses Gmail API. So please enable Gmail API at Advanced Google services. Ref const convert_ = ({ to, emailFrom, nameFrom, subject, textBody, htmlBody }) => { const boundary = "boundaryboundary"; const mailData = [ `MIME-Version: 1.0`, `To: ${to}`, nameFrom && emailFrom ? `From: "${nameFrom}" <${emailFrom}>` : "", `Subject: =?

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.temp.length > 0 && e == o.

Gists This is a sample script for sending Outlook emails using Microsoft account with Google Apps Script. Before you use this script, please install OnedriveApp which is Google Apps Script library. Ref And, please authorize your Microsoft account for using Microsoft Graph API. Ref Sample script function myFunction() { const obj = [ { to: [{ name: "### name ###", email: "### email address ###" }, , ,], subject: "sample subject 1", body: "sample text body", cc: [{ name: "name1", email: "emailaddress1" }, , ,], }, { to: [{ name: "### name ###", email: "### email address ###" }, , ,], subject: "sample subject 2", htmlBody: "<u><b>sample html body</b></u>", attachments: [blob], bcc: [{ name: "name1", email: "emailaddress1" }, , ,], }, ]; const prop = PropertiesService.

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.

OnedriveApp was updated to v1.2.0. v1.2.0 (October 4, 2021) 1 method for retrieving the access token and 7 methods for managing emails of Microsoft account were added. By this, the emails got to be able to be gotten and sent using Microsoft account using OnedriveApp with Google Apps Script. GitHub of OnedriveApp

OnedriveApp was updated to v1.1.2. v1.1.2 (September 29, 2021) A bug of method of uploadFile was removed. By this, the files except for Google Docs files can be uploaded to OneDrive. GitHub of OnedriveApp

Gists This is a sample script for directly submitting answers to Google Form using Google Apps Script. The sample Google Form is as follows. For this Google Form, this sample script submits the values of sample text, option2 and option1, option2, sample option to Google Form. Sample script For the multiple answers, it seems that it is required to send the values as the query parameter. I thought that the same key is used.

Gists This is a sample script for replacing the template texts with an array in Google Document using Google Apps Script. This is for Google Apps Script of this report. The sample input and output situations are as follows. In the current stage, when replaceAllText of Docs API is used with the sample value of ["updated text 1", "updated text 2", "updated text 3"], all values of {{oldText}} are replaced with the 1st value of updated text 1 in one batch request.

Gists This is a sample script for executing a function with the minutes timer in the specific times using Google Apps Script. For example, when this sample script is used, the following situation can be achieved. Execute a function every 10 minutes only in 09:00 - 12:00 and 15:00 - 18:00 for the weekday. When the above situation is shown as an image, it becomes as follows. In the above sample situation, a function is run every 10 minutes in the green ranges of 09:00 - 12:00 and 15:00 - 18:00.

Gists This is a sample script for simply editing the texts of texts boxes on Google Slides using Google Apps Script. The supposed situation is as follows. Google Slides has several text boxes of the same size and the same position. You want to retrieve the list of texts from the text boxes and want to change the texts using a simpler method. In this case, I thought that when the sidebar created by Google Apps Script is used for changing the texts, your goal might be able to be simply achieved.

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 changing the font of selected text to Google Sans on Google Document using Google Apps Script. Sample script Please copy and paste the following script to the script editor of Google Document And, when you use this script, please select a text in Google Document and run the script. By this, the font of selected text is changed to Google Sans. function myFunction() { const selection = DocumentApp.

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

This is a sample script for creating a PNG image with the alpha channel using Google Apps Script. The PNG image with the alpha channel is the image includes the transparent background. I have been looking for the method for creating such image using Google Apps Script. It is considered that when this will be achieved, it will be able to be used for various situations. For example, it is considered the following situations.

Gists I had confirmed a bug of new IDE about the time zone of Google Apps script project. When I created new Google Apps Script project using new IDE, the time zone of GAS project is always America/New_York. In my area, it's Asia/Tokyo. The time zone can be confirmed at appscript.json. So, I had reported this issue to Google issue tracker. Ref After I posted this, I had also confirmed this issue at Stackoverflow.

Gists This is a sample script for plotting the points on an image using Google Apps Script. Unfortunately, in the current stage, there are no methods for directly editing the image and putting the texts and some shapes in the methods for Google Apps Script. So in this case, it is required to use the workaround. Fortunately, I have already published a report about “Inserting Text on Image using Google Apps Script” in my blog.

Gists At March 15, 2021, one endpoint is created for one deployment. Ref By this, when you redeploy “Web Apps”, the endpoint is changed. Because the deployment ID is changed. It seems that this it the new specification. In this report, I would like to introduce the method for redeploying Web Apps without changing the URL of Web Apps for new IDE. Deploy Web Apps Open “New deployment” dialog with “Deploy” -> “New deployment”.

This is the document for linking Google Cloud Platform Project to Google Apps Script Project for New IDE. And also, several sample scripts using Google Apps Script API and Google Photos API are introduced. You can see the detail of this document at https://github.com/tanaikech/Linking-Google-Cloud-Platform-Project-to-Google-Apps-Script-Project-for-New-IDE.

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.

Parser for Sending HTML Form Object to Google Apps Script using google.script.run Overview This is a Javascript library for sending the HTML form object to Google Apps Script using google.script.run. Description HTML form object is parsed by this library, and the object can be sent to Google Apps Script using google.script.run. After the V8 runtime got to be able to be used for Google Apps Script, when the file input tag is included in the HTML form object, the object sent to Google Apps Script using google.

Gists In this post, I would like to introduce the xpath tester using Web Apps created by Google Apps Script. Demo Usage 1. Prepare Google Spreadsheet. Please create new Google Spreadsheet on your Google Drive. 2. Enable Sheets API. Please open the script editor at the created new Spreadsheet and enable Sheets API at Advanced Google services. 3. Prepare sample script. Please copy and paste the following script to the script editor on the created new Spreadsheet and save it.

Overview This is a sample script for achieving a simple photo gallery created by Google Slides and Web Apps using Google Apps Script. Description At Google, there is a great Google Photos. Ref Recently, I was required to have a simple photo gallery. At that time, I thought that when an independence photo gallery instead of Google Photos can be used, it will be useful. Also, this might be useful for other users.

Gists This is a sample script for creating the custom grid view of Google Slides as an image using Google Apps Script. Demo Usage In order to use this script, please do the following flow. 1. Install GAS library This sample script uses a library of DocsServiceApp. So please install DocsServiceApp. You can see the method for installing it at here. 2. Enable APIs This sample script uses 2 APIs of Drive API and Slides API.

BatchRequest was updated to v1.1.3. v1.1.3 (January 13, 2021) In order to give the access token from outside, the access token got to be able to be included in the object. By this, for example, you can use the access token retrieved by the service account. You can check this at https://github.com/tanaikech/BatchRequest.

Gists This is a sample script for adding the slide page link to the shape using Google Apps Script. When I use Google Slides, there is the case that I want to jump to the specific slide on the same Google Slides. And, I have the case that I want to jump from the last slide to the 1st slide. In those cases, I had manually added the slide page link to each shape.

Overview This is a report for safe-uploading files to Google Drive by HTML put in the external server using Google Apps Script. Description When you want to make the user upload a file to your own Google Drive using the HTML put in the external server of Google side, when the file size is smaller than 50 MB, this can be achieved without using the access token. Ref (When the HTML is put in the internal server of Google side, you can also use google.

Original post Superheroes are well known for wearing capes, fighting villains and looking to save the world from evil. There also are superheroes that quietly choose to use their super powers to explain technology to new users, maintain community forums, write blog posts, speak at events, host video series, create demos, share sample code and more. All in the name of helping other developers become more successful by learning new skills, delivering better apps, and ultimately enhancing their careers.

FilesApp was updated to v1.1.3. v1.1.3 (December 12, 2020) Access token got to be able to be given as an argument. When the access token is not given as the argument, ScriptApp.getOAuthToken() is used as the default access token. By this, the service account can be used. GitHub of FilesApp

New IDE for Google Apps Script has finally been released at December 7, 2020. Ref I think that the speed for displaying the log is faster than old one, and the code completion also got to be easy to see.

Gists This is a sample script for achieving the search of files by is:unorganized owner:me using Google Apps Script. In the current stage, unfortunately, the files cannot be directly retrieved by searching is:unorganized owner:me with Drive API and Drive service. So as the current workaround, all files are retrieved using the method of “Files: list” of Drive API with 'me' in owners and trashed = false, and the file list is retrieved from all file list using a script.

Gists This is the sample scripts for creating new event with Google Meet link to Google Calendar using various languages. When I saw the official document of “Add video and phone conferences to events”, in the current stage, I can see only the sample script for Javascript. But I saw the several questions related to this for various languages. So I published the sample scripts for creating new event with Google Meet link to Google Calendar using various languages.

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 converting the texts to the bold, italic, bold-italic types, underline and strike through of the unicode using Google Apps Script. In the current stage, at Google Docs (Spreadsheet, Document, Slides and so on), the rich texts cannot be directly managed for all places using Google Apps Script. But there are the places which can use the bold, italic and bold-italic fonts with the unicode.

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 Introduction Here, I would like to report the process costs for retrieving the values from the arrays for Spreadsheet using Google Apps Script (GAS). When Spreadsheet is used with Google Apps Script, we have the following situations. Retrieve values from the multiple rows in a column. Retrieve values from the multiple columns in a row. When the values are retrieved from above situations, it is required to retrieve the values from 1 dimensional array in the 2 dimensional array.

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 of the request of multipart/form-data with a simple request body using Google Apps Script. I hope that the users will easy to use Class UrlFetchApp by this report. This report is the updated post of “Multipart-POST Request Using Google Apps Script". Description I had already reported about this at this report. In that case, it was required to create a bit complicated request body to request multipart/form-data.

Gists This is a sample script for inserting a text on an image using Google Apps Script. Demo In this demonstration, “sample text” is inserted to the image. The image is from https://www.deviantart.com/k3-studio/art/Rainbow-painting-281090729. Preparation When you use this script, please install the following 2 Google Apps Script libraries. DocsServiceApp ImgApp And, please enable Slides API at Advanced Google services. Flow The flow of this sample script is as follows.

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 Introduction Here, I would like to report the process costs for searching values in Spreadsheet using Google Apps Script (GAS). When the values are searched in Google Spreadsheet, the following 3 patterns can be considered. Ref Retrieve all values using getValues, and the values are searched from the retrieved array. Use TextFinder. Use Query language. In these cases, it has already been found that the lowest process cost is to use the Query language.

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.

ImgApp was updated to v1.3.0. v1.3.0 (September 24, 2020) Added new method. Added editImage() This method is for editing images. In the current stage, the image can be cropped. And several images can be merged as an image. You can see the detail information here https://github.com/tanaikech/ImgApp

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.

FetchApp was updated to v1.0.2. v1.0.2 (September 19, 2020) From this version, when a blob is sent, the blob is sent to files. You can check this at https://github.com/tanaikech/FetchApp.

Gists This is a sample script for retrieving All URLs in Google Document using Google Apps Script. In this sample script, the method of “documents.get” in Google Docs API is used. By this, the URL can be retrieve using JSON.parse(). Sample script Before you use this script, please enable Google Docs API at Advanced Google Services. const documentId = "###"; // Please set the Google Document ID. const content = Docs.

Gists This is a sample script for using the values submitted from the HTML form using Google Apps Script and Javascript. In this case, the values include the files. Issue <form> Text: <input type="text" name="sampleText1" /><br /> Single file: <input type="file" name="sampleFile1" /><br /> <input type="submit" name="button" value="submit" onclick="main(this.parentNode)" /> </form> <script> function main(e) { google.script.run.sample(e); } </script> This is a simple sample script for sending the values of form to Google Apps Script.

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. Sample script const range = "B5:D10"; const [header, ...values] = SpreadsheetApp.getActiveSheet() .getRange(range) .getDisplayValues(); const table = Charts.newDataTable(); header.

Gists These are sample scripts for modifying the 1st-page header in Google Document using Google Apps Script. Unfortunately, in the current stage, the 1st-page header cannot be modified by Document service. In this case, it is required to use Google Docs API. Here, I would like to introduce 2 sample scripts for modifying the 1st page header using Docs API. When you use this, please enable Google Docs API at Advanced Google services.

Gists This is a sample script for uploading a file to Google Drive from the external HTML without the authorization. In this case, the client side can be used at the outside of Google. And as the server side, the Web Apps created by Google Apps Script is used. Usage Please do the following flow. 1. Create new project of Google Apps Script. Sample script of Web Apps is a Google Apps Script.

Gists This is a sample script for decoding a QR code put in Google Slides using Google Apps Script. In this case, Javascript is used at the opened dialog. And Canvas API and jsQR are used. So unfortunately, this method cannot be used with the time-driven trigger and the Google Apps Script project of the standalone type. Of course, this method can be also used for Google Document and Google Spreadsheet.

Gists This is a sample script for cropping images in the Google Slides using Google Apps Script. In the current stage, in order to crop the images in Google Slides, it is required to use replace(blobSource, crop) Because, although there is the “cropProperties” of “UpdateImagePropertiesRequest” in Slides API, unfortunately, in the current stage, this cannot be still used. This has already been reported. Ref About cropping using replace(blobSource, crop), I thought that how to use might be a bit difficult.

Gists This is an important point for using reduceRignt with and without v8 runtime for Google Apps Script. Sample script function myFunction() { var array = ["a", "b", "c", "d", "e"]; var res = array.reduceRight(function (ar, e, i) { ar.push([e, i]); return ar; }, []); Logger.log(res); } Result With V8 When V8 runtime is used, the following result is obtained. [["e",4],["d",3],["c",2],["b",1],["a",0]] Without V8 When V8 runtime is NOT used, the following result is obtained.

v1.0.3 (August 1, 2020) When the file size less than the default chunk is downloaded, an error occurred. This bug was removed. You can see the detail information here https://github.com/tanaikech/DownloadLargeFilesByUrl

Overview This is a Google Apps Script library for creating, updating and exporting Google Apps Script project of the standalone type using Drive API. In this case, Apps Script API is not used. Description I had reported “Drive API cannot create Google Apps Script project no longer” before. Ref About this, I had reported the future request. Ref At July 30, 2020, I could confirm that the Google Apps Script project of the standalone type got to be able to be created by multipart/form-data using Drive API again.

Gists I have reported “Drive API cannot create Google Apps Script project no longer”. Ref About this, I had reported the future request. Ref Today, I could confirm that the Google Apps Script project of the standalone type got to be able to be created by multipart/form-data using Drive API. This is a good news for me. By this, the following 2 patterns can be used from now. Pattern 1: Create new standalone GAS project by Apps Script API.

Gists I have already reported for showing the log to “Apps Script Dashboard” when it requests to the Web Apps. Ref In order to show the log to “Apps Script Dashboard” when it requests to the Web Apps, it is required to use the access token. But in the current stage, when the access token is used for XMLHttpRequest and fetch of Javascript in the request headers, the error related to CORS occurs.

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 report for retrieving the logs in Web Apps for Google Apps Script, when it requests to the Web Apps. Experimental condition 1. Sample script for Web Apps const doGet = (e) => { Logger.log(`GET method: ${JSON.stringify(e)}`); console.log(`GET method: ${JSON.stringify(e)}`); return ContentService.createTextOutput( JSON.stringify({ method: "GET", e: e }) ); }; const doPost = (e) => { Logger.log(`POST method: ${JSON.stringify(e)}`); console.log(`POST method: ${JSON.stringify(e)}`); return ContentService.createTextOutput( JSON.stringify({ method: "POST", e: e }) ); }; This Web Apps is deployed as Execute the app as: Me and Who has access to the app: Anyone, even anonymous.

Gists This is a sample script for converting the SVG image data to PNG image data using Google Apps Script. Unfortunately, in the current stage, there are no methods for directly converting the SVG to PNG in Google Drive service. But it can be achieved by Drive API. The sample script is as follows. Before you use this, please enable Drive API at Advanced Google services. Sample script function myFunction() { const svgFileId = "###"; // Please set the fileId of the SVG file.

Gists This is the statistics for the tag triggers on Stackoverflow. The tag of triggers was created at 2011-07-28. This statistics are retrieve from Stackoverflow using Stackexchange API. About 2020, the data is retrieved from 2020-01-01 to 2020-07-16. This statistics data was obtained at 2020-07-16. Measurement result Fig. 1: About triggers tag. Year vs. Total questions, Answered, Solved and Closed questions Fig. 2: About google-apps-script tag and triggers tag. Year vs.

Gists This is a sample script for retrieving “Users”, “Sessions” and “PageViews” of User Summary Report from Google Analytics using Google Apps Script. When you use this, please enable Analytics Reporting API at Advanced Google services. Sample script function myFunction() { const viewId = "###"; const startDate = "2020-01-01"; const endDate = "2020-06-01"; const resource = { reportRequests: [ { viewId: viewId, dateRanges: [{ startDate: startDate, endDate: endDate }], metrics: [ { expression: "ga:users" }, { expression: "ga:sessions" }, { expression: "ga:pageviews" }, ], }, ], }; const res = AnalyticsReporting.

Gists These are the sample scripts by the various languages for requesting to Web Apps created by Google Apps Script. curl Google Apps Script Javascript ajax Node.js axios angular go python php powershell Sample script for Web Apps Sample script for Web Apps is as follows. const doGet = (e) => ContentService.createTextOutput( JSON.stringify({ method: "GET", eventObject: e }) ).setMimeType(ContentService.MimeType.JSON); const doPost = (e) => ContentService.createTextOutput( JSON.stringify({ method: "POST", eventObject: e }) ).

Overview This is a Google Apps Script library for achieving the ownership-transfer of the specific folder including the files and sub-folders using Drive API. IMPORTANT: PLEASE BE CAREFUL THIS. At first, please read this section I cannot take responsibility for the problems occurred by this library. So when you use this library, please use it by according to your own decision and at your own responsibility. This GAS library transfers the ownership of files and folders.

At July 7, 2020, I have reported the transfer of owner of files got not to be able to be achieved with batch requests of Drive API. Ref But today, I could confirm that this got to be able to be achieved. But, I'm worry about the possibility that this might not be able to be used in the future again.

At 15, June 2020, I have reported the transfer of owner of files got to be able to be achieved with batch requests of Drive API. Ref And also, yesterday (06, July 2020), I could have confirmed that this could be worked. But, now (07, July 2020), the following response is returned. { "error": { "errors": [ { "domain": "global", "reason": "invalidSharingRequest", "message": "Bad Request. User message: \"You can't yet change the owner of this item.

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.

New section CORS in Web Apps was added to Taking advantage of Web Apps with Google Apps Script In this section, I would like to introduce CORS in Web Apps. As the result, in order to prevent the error related to CORS, it was found the following important points. It is required to return ContentService.createTextOutput() and ContentService.createTextOutput("done") from doGet and doPost. For POST method, the data is required to be converted to the string and sent to Web Apps.

Gists In this report, I would like to report for processing to create new file to the specific folder using Drive API. When the new file is created to the specific folder using Drive API, the property of parents with the value of folder ID is included in the request body of the method “Files: create”. About this process, I had thought that the file is directly created to the specific folder.

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 managing the texts on Google Slides using Google Apps Script. Recently, I got the request like this. I published this here, because I thought that this might be also useful for other users. Demo In this demonstration, the text of {{baz}} on Google Slides are searched and replaced to other text, and also, the text style is changed. Sample situation In this case, it supposes that there are 3 types of shapes in the slide.

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.

Gists This is a sample script for retrieving the difference between 2 arrays, which are the old values and the new values, using Google Apps Script. In my environment, I sometimes have the situation that it is required to retrieve the difference between 2 arrays. So I prepared this as a sample script. I think that this can be also used at Javascript and Node.js. If this was also useful for your situation, I'm glad.

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.

Today, I could confirm that the transfer of owner of files got to be able to be achieved with batch requests of Drive API. When I had tested this at January 31, 2020, an error of there is no function to change the owner of this item yet (currently under development) had occurred. But today, I could confirm that this got to be able to be achieved by the batch requests.

Overview These are the sample scripts of the batch requests for Drive API using Google Apps Script. Description When we want to manage the files and folders on Google Drive, we have 2 ways. One is the use of Drive service. Another is the use of Drive API. In the case of them, when we want to manage a lot of files and folders, unfortunately, both ways have no batch requests.

Overview This is the sample scripts for managing a lot of Google Calendar Events using the batch requests with Google Apps Script. Description When we want to manage the events of Google Calendar, we have 2 ways. One is the use of Calendar service. Another is the use of Calendar API. In the case of them, when we want to manage a lot of calendar events, unfortunately, both ways have no batch requests.

BatchRequest was updated to v1.1.2. v1.1.1 (June 12, 2020) Error handling for the input object was added. v1.1.2 (June 12, 2020) Removed a bug that when the returned value is empty, an error occurred. You can check this at https://github.com/tanaikech/BatchRequest.

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.

BatchRequest was updated to v1.1.0. v1.1.0 (June 10, 2020) New method of EDo() was added. This method is the enhanced Do() method. When this method is used, the result values from the batch requests are parsed. And also, the number of requests more than 100 can be used. In this case, the split of the number of requests is processed for the limitation of 100. You can check this at https://github.

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 When the method of “Files: list” in Drive API v3, the official document of includeItemsFromAllDrives and supportsAllDrives says as follows. Deprecated - Whether both My Drive and shared drive items should be included in results. This parameter will only be effective until June 1, 2020. Afterwards shared drive items are included in the results. (Default: false) Deprecated - Whether the requesting application supports both My Drives and shared drives.

RunAll was updated to v1.1.2. v1.1.2 (May 31, 2020) When the access token and project ID are not included in the object, getOAuthToken() and getScriptId(). By this, an error is removed. You can see the detail information here https://github.com/tanaikech/RunAll

FilesApp was updated to v1.1.2. v1.1.2 (May 29, 2020) A bug that all files were not retrieved from the shared drive was removed. GitHub of FilesApp

Gists This is a sample script for creating a shortcut on Google Drive using Google Apps Script. Sample script Before you run the script, please enable Drive API at Advanced Google services. function createShortcut(targetId, name, folderId) { const resource = { shortcutDetails: { targetId: targetId }, title: name, mimeType: "application/vnd.google-apps.shortcut", }; if (folderId) resource.parents = [{ id: folderId }]; const shortcut = Drive.Files.insert(resource); return shortcut.id; } // Please run this function.

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.

Overview This is Google Apps Script library for copying folder on Google Drive. Description I have sometimes the situation that it is required to back up the folder on Google Drive to Google Drive. But unfortunately, the method of makeCopy() of Class File and the method of Files: copy of Drive API cannot be used for directly copying the folder on Google Drive. So I created this as a library. This library can copy the folder on Google Drive.

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.

FilesApp was updated to v1.1.0. Shared drive got to be able to be used. From the version 1.1.0, the following modification was added. V8 is used. As the default setting, the file list is retrieved from both your Google Drive and the shared drive. By this, for example, when the folder ID in the shared Drive is used to ### of const res = FilesApp.createTree("###"), the folder tree of the shared Drive is retrieved, if you have the shared Drive.

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 Overview When // in template literal is used in a HTML file in script editor, it is used as a comment start. const sample = `//`; For example, when above script is used in a HTML file at the script editor, ;" of const sample =`//`; is used as the comment. Description I would like to explain about this bug using the following sample flow. Flow Create new Spreadsheet and open the script editor.

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 Description When V8 is enabled, Array.apply has the limitation for the number of elements. When it is over the limitation, an error like RangeError: Maximum call stack size exceeded occurs, while the issue didn't occur when V8 is disabled. In this case, this issue occurs at both Google Apps Script and Javascript. So please be careful this. Sample situation For example, when Array.prototype.push.apply is used for combining the arrays because the process cost of Array.

FetchApp was updated to v1.0.1. v1.0.1 (April 13, 2020) When V8 runtime is enabled, it was found that an error occurred. So this bug was removed. You can check this at https://github.com/tanaikech/FetchApp.

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.fromCharCode(temp + 65) + letter; column = (column - temp - 1) / 26; } return letter; }; const letterToColumn = letter => { let column = 0, length = letter.

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.

Gists March 22, 2020 Published. Kanshi Tanaike Introduction V8 engine got to be able to be used at Google Apps Script. By this, I have reported about the process costs with and without using V8. Ref It is considered that knowing the process costs for various methods will be useful for creating the applications with Google Apps Script. Here, I would like to introduce the process costs of each situations under V8.

Gists This is a sample script for retrieving the files and folders which have no parents in own Google Drive. When you use this script, please enable Drive API at Advanced Google services. Sample script const myFunction = () => { const token = ScriptApp.getOAuthToken(); const fields = decodeURIComponent( "nextPageToken,files(name,id,mimeType,parents)" ); const q = decodeURIComponent("'me' in owners and trashed = false"); let files = []; let pageToken = ""; do { const res = UrlFetchApp.

Web Application for searching Google Apps Script Library from Database was completed. So I published it as v1.0.0. Please check Google Apps Script Library Database.

Gists Today, I noticed that new Google Apps Script project of the standalone script type cannot be created by the method of Files: create in Drive API. From now, in order to manage the Google Apps Script project, only Google Apps Script API is required to be used. By this, the following issues are brought. When the new standalone GAS project is created in the specific folder by uploading the local script, the following flow is required to be run.

Overview FieldsBuilderForGoogleAPIs is a Web Application for building the fields value for using Google APIs. This is mainly used for developing the scripts for using Google APIs. Demo Description After the API explorer was updated, the fields of APIs cannot be created by the web interface. But it is important for using the fields property. Because when the fields property is used, only the required values can be retrieved. This leads to the low cost.

Overview This is a GAS library for retrieving and creating the albums and media items using Google Photo API using Google Apps Script (GAS). Description In the current stage, Google Photo API is not included in Advanced Google services. But in order to use Google Photo API with Google Apps Script, I created this as a GAS library. So in the current stage, in order to use this library, the following flow is required.

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 simple sample script for uploading a file using the file input tag of HTML. As the important point, the file is sent as the byte array for using Google Apps Script. By this, at Google Apps Script side, the byte array can be converted to a blob using a simple script. HTML & Javascript <input id="file" type="file" onchange="saveFile(this)" /> <script> function saveFile(f) { const file = f.

Overview This is for the Google Apps Script Library Database. Description Since Google Apps Script was released on August 19th, 2009, it is used by a lot of users. Ref1, Ref2 By this, now there are a lot of useful libraries of Google Apps Script (GAS) in all over the world. But when I want to search a GAS library, I always use Google search engine. Unfortunately, in the current stage, the libraries cannot be directly searched by a database.

ManifestsApp was updated to v1.0.4. v1.0.4 (February 12, 2020) “runtimeVersion” got to be able to be got and set. Now, ‘STABLE’, ‘V8’, ‘DEPRECATED_ES5’ can be used as the value of “runtimeVersion”. For example, you can enable V8 with the following script. var r = ManifestsApp.setProjectId(projectId).setRuntimeVersion("V8"); Logger.log(r); As one important point, when “STABLE” and “DEPRECATED_ES5” are used for the Google Apps Script project created before 2020 as the value of “runtimeVersion”, the error of Syntax error: Illegal character.

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.to ? -1 : 1; }); obj.

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.1 So users always have to pay attention to reducing the process cost of the scripts. Especially, it is very important to know the process cost for the array processing, because the array processing is often used for spreadsheet and Google APIs.

Gists In my environment, at February 7, 2020, when I opened the script editor of Google Apps Script, the following notification could be seen. By this, I could notice that finally, the V8 Runtime has already been added to Google Apps Script. I think that this will be also the great news for a lot of users including me. The detail of V8 Runtime can be seen at https://developers.google.com/apps-script/guides/v8-runtime.

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

Overview EncodeApp is a GAS library for retrieving the encoding set (charset) and doing URL encode with the specific encoding set using Google Apps Script (GAS). You can see the detail information here https://github.com/tanaikech/EncodeApp

Gists This is a sample script for achieving the URL encode with Shift-JIS using Google Apps Script. Unfortunately, there are no methods for directly achieving above in the methods of Google Apps Script. So it is required to prepare it as the script. In order to use Shift-JIS of the character set at Google Apps Script, it is required to use it as the binary data. Because, when the value

Gists Overview This is a sample script for deleting the last empty page which has only one paragraph including no values which is "" in the Google Document using Google Apps Script. Description As a sample situation, it supposes that there is the following Google Document. In this sample, the last page of Google Document has only one paragraph including no values which is "". When I try to delete only last page without modifying the previous page, I noticed that this cannot be achieved with only Google Document service.

Gists This is a sample script for retrieving the event ID from the event URL of Google Calendar using Google Apps Script. The event URL is like https://www.google.com/calendar/event?###. At the event URL, ### of https://www.google.com/calendar/event?### is not the event ID. So it is required to convert it. Sample script var eventUrl = "https://www.google.com/calendar/event?###"; var eventId = Utilities.newBlob(Utilities.base64Decode(eventUrl.split("=")[1])) .getDataAsString() .split(" ")[0]; Logger.log(eventId);

Gists Overview This is a simple method running Google Apps Script by the event notification from Google Calendar. Description There are several event triggers in Google Apps Script. Ref1, Ref2 There is an event trigger for Google Calendar. The official document says An installable calendar event trigger runs when a user's calendar events are updated—created, edited, or deleted.. Ref3 If I want to use the trigger when the event in the calendar is starts and finished, it is required to use Calendar API by preparing the URL for receiving from Google.

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.

GmailToList was updated to v1.0.1. v1.0.1 (December 17, 2019) New method of getAttachmentFiles() was added. The attachment files can be retrieved as blob using this method. You can see the detail information here https://github.com/tanaikech/GmailToList

Overview This is a library for exporting all messages of Gmail as a list using Google Apps Script (GAS). Description Recently, I have had a situation it had been required to backup all messages in own Gmail. In order to achieve this, I created a simple script. After I created it, I thought that when such situation might occur for other users and the script is published as a library, they might be useful.

Gists These are 3 sample scripts for moving a file to the specific folder in Google Drive using Google Apps Script. Sample script 1 In this script, only Drive Service is used. var sourceFileId = "###"; var destinationFolderId = "###"; var file = DriveApp.getFileById(sourceFileId); DriveApp.getFolderById(destinationFolderId).addFile(file); file .getParents() .next() .removeFile(file); Sample script 2 In this script, only Drive API at Advanced Google services. (In this case, it's Drive API v2.) var sourceFileId = "###"; var destinationFolderId = "###"; Drive.

Gists In this sample script, all pages in the Figma file are retrieved and the retrieved pages are put to new Google Slides as the image. Usage 1. Retrieve access token You can see the method for retrieving the access token at here. Although there is also OAuth2 for retrieving the access token, in your situation, I thought that the method for directly generating the access token on the site might be suitable.

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

ZipFolder was updated to v1.0.1. v1.0.1 (October 15, 2019) The option for preventing the duplicated filenames when the Google Docs is converted was added. You can see the detail information here https://github.com/tanaikech/ZipFolder

Overview This is a sample script for uploading multiple files with large size (> 50 MB) at the sidebar, dialog of Google Docs and Web Apps using the resumable upload of the asynchronous process with Javascript and Google Apps Script (GAS). Demo This is a demonstration of this script. As a demonstration, it uploads 5 files with the size of 100 MB to own Google Drive. When the files were selected and the upload button is clicked, those are uploaded by the resumable upload with the asynchronous process.

Overview This is a sample script for downloading files from Google Drive by the one time download method. Description When you download a file from Google Drive, in generally, the login and the access token are required. If you want to download the file without the authorization for the simple situation, the file is required to be publicly shared. But the file might not be able to be shared publicly, because of various reasons.

RunAll was updated to v1.1.1. v1.1.1 (September 20, 2019) When the number of 0 was used as the argument, null was returned. This bug was removed. Coffeescript as the source was updated. You can see the detail information here https://github.com/tanaikech/RunAll

Gists This is a sample script for modifying the revisions of a file on Google Drive using Google Apps Script. This script can be used for not only Google Docs files, but also the files except for Google Docs. Issue and workaround: Unfortunately, in the current stage, at Google Docs files, the revision of Google Docs cannot be directly changed by APIs with a script. So as one of several workarounds, I would like to propose to overwrite the Google Docs file using the exported data.

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 In this report, I would like to introduce the method for running functions by directly specifying the function names with Web Apps for Google Apps Script. Description It has already been known that the directly specified functions in the project can be run from the outside by enabling “API executable” and using the method of scripts.run in Google Apps Script API. In this case, the installation for using Apps Script API is a bit complicate.

RunAll was updated to v1.1.0. v1.1.0 (August 26, 2019) New method for achieving the parallel processing with Web Apps was added. You can see the detail information here https://github.com/tanaikech/RunAll

Gists This is a sample script for “Examples of How to Derive a Signing Key for Signature Version 4” using Google Apps Script. In order to use AWS SDKs, there are the sample scripts for the languages of Java, .NET (C#), Python, Ruby, JavaScript (Node.js). But the sample script of Google Apps Script is not prepared. I saw the question related to this at Stackoverflow. So I would like to also introduce the sample script here.

Gists This is a sample script for parsing HTML using Google Apps Script. When HTML data is converted to Google Document, the HTML data can be parsed and be converted to Google Document. In this case, the paragraphs, lists and tables are included. From this situation, I thought that this situation can be used for parsing HTML using Google Apps Script. So I could came up with this method. In the Sheet API, the HTML data can be put to the Spreadsheet with the PasteDataRequest.

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.

Overview This is a GAS library for unzipping a Zip file protected by a password using Google Apps Script. Description Recently, I had a situation that it is required to unzip a Zip file protected with the password. But unfortunately, in the current stage, the method of Utilities.unzip() cannot unzip such protected files. So when I had been looking for the other workarounds, I found zlib.js. Especially, it's unzip.min.js. This is created for Javascript.

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.

Gists Introduction At April 8, 2019, the specification of Google Apps Script Project was changed. You can see this at Google Cloud Platform Projects. The official document says as follows. Warning: Starting on or after April 8, 2019, the Google Cloud Platform Console won't be able to access the default GCP projects created for new Apps Script projects. Older, existing scripts may have default GCP projects that are still accessible, however.

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 fixing a value putting by a custom function of Spreadsheet using Google Apps Script. When a custom function is used, the value retrieved by the custom function of Spreadsheet is automatically updated by recalculating. So in the case that the value retrieved by the custom function is changed by the time, the value is also changed by automatically updating. In this sample script, I would like to introduce a method for fixing such values.

Gists This is a sample script for retrieving the values with and without duplicating from JSON object using Google Apps Script. Also this can be used by Javascript. Sample script var obj = [ { key1: "value1a", key2: "value1b" }, { key1: "value2a", key2: "value2b" }, { key1: "value5a", key2: "value5b" }, { key1: "value3a", key2: "value3b" }, { key1: "value1a", key2: "value1b" }, { key1: "value4a", key2: "value4b" }, { key1: "value5a", key2: "value5b" }, { key1: "value3a", key2: "value3b" } ]; var res = obj.

Gists This is a sample script for creating new table and putting values to cells using Google Docs API with Google Apps Script. Unfortunately, in the current stage, although I had been looking for the method for creating a table and putting the values in each cell at the official document, I couldn't find. Google Docs API is growing now. So such documents might be not prepared yet. By this situation, I investigated about the method for achieving this method.

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 This is a sample script for deleting pages of Google Document from the last page using Google Apps Script. There are no methods for directly deleting pages of Google Document. This is one of several workarounds. In this workaround, the following flow is used. Flow Retrieve paragraphs in the body of Document. Retrieve elements in each paragraph. The page break is included in the paragraph. Delete elements from last page in order.

Gists This is a sample script for retrieving total page of Google Document using Google Apps Script. There are no methods for directly retrieving the total page of Google Document. This is one of several workarounds. In this workaround, the total page is retrieved by converting to PDF format. var n = DriveApp.getFileById(id) .getBlob() .getDataAsString() .split("/Contents").length - 1; Logger.log("totalPages: %s", n); When you use this, please set the Google Document ID as id.

Gists Overview These are sample scripts for processing the duplicated rows of 2 dimensional arrays using Google Apps Script. Description When I use Google Spreadsheet and/or see Stackoverflow, I sometimes see the situation which is required to process the duplicated rows of 2 dimensional arrays. I thought that when the sample scripts for it have already prepared, they will be useful for other users including me. So I published this post.

Gists At April 8, 2019, the specification of Google Apps Script Project was changed. Various specification was changed. Please see the detail at Google Cloud Platform Projects. Here, I would like to introduce one change which might be useful for users. The official document says as follows. When you enable an advanced service for your script project in the Apps Script editor, it is automatically enabled in the default GCP project when the script project is saved.

Gists This is a sample script for backing up a project as a zip file. When you use this script, please install a GAS library of ProjectApp2. You can back up both the standalone script type and the container-bound script type. In this script, the blob of zip file can be retrieved from ProjectApp2. So you can also send it as email without creating a file. var projectId = "### fileId of project ###"; var blob = ProjectApp2.

Gists This is a sample script for splitting an array by n elements using Google Apps Script. Sample script 1: var limit = 3; var ar = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]; var res = []; while (ar.length > 0) res.push(ar.splice(0, limit)); Logger.log(res); // [[1.0, 2.0, 3.0], [4.0, 5.0, 6.0], [7.0, 8.0, 9.0], [10.0]] Above sample script is a simple. But at Google Apps Script, the process cost of “while” is higher than the for loop as shown in this report.

Gists This is a sample script for overwriting several Google Documents by 2 text files using Google Apps Script. Before you run this sample script, please install a GAS library of FetchApp. As a sample situation, it supposes to overwrite 2 existing Google Documents by 2 text files using the method of files.update Drive API v3. In the current stage, the batch request of Drive API cannot use the file media.

Gists This is a sample script for creating Google Document by converting PDF and image files with OCR using Google Apps Script. Before you run this sample script, please install a GAS library of FetchApp. function sample() { var fileId = "### fileId of PDF file and image files ###"; var metadata = { name: "sampleDocument", // Filename of created Google Document mimeType: MimeType.GOOGLE_DOCS // MimeType of Google Document }; var fileBlob = DriveApp.

Overview This is a GAS library for creating and requesting the type of multipart/form-data using Google Apps Script. This library enhances Class UelFetchApp of Google Apps Script. Description In order to fetch data from URL, there is Class UrlFetchApp in Google Apps Script. As the method for fetching, there is the method of fetch(url, params). In the current stage which was released this library, when user want to request with the type of multipart/form-data, the request body is required to be created by the user.

Gists This is a sample script for converting a lot of files to Google Docs (Spreadsheet, Document and Slides). Batch request can be used for converting files. In this sample script, the files are converted using the batch request. Batch request can request 100 API by one API call. This sample script uses the fetchAll method. So even if there are over 100 files, this script can process them. Sample script: Before you run the script, please set the variables at main().

ImgApp was updated to v1.2.2. v1.2.2 (April 6, 2019) By Google's update, the error of “Malformed multipart body.” occurs. This error was resolved. You can see the detail information here https://github.com/tanaikech/ImgApp

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 This is a sample script for deleting the positioned images on Google Document using Google Apps Script. In the current stage, unfortunately, there are no methods for deleting the positioned images in Class PositionedImage, yet. But when Google Docs API is used, the positioned images can be deleted. When you use this script, please enable Google Docs API at Advanced Google Services and API console. You can see how to enable them at here

Gists This is a sample script for modifying the searched text to the small capital letters using Google Apps Script. Unfortunately, in the current stage, there are no methods for modifying the part of texts to the small capital letters in Document Service, yet. But when Google Docs API is used, this can be achieved. When you use this script, please enable Google Docs API at Advanced Google Services and API console.

Overview This is a GAS library for notifying the change of number of comments, stars and forks of own Gists as an email using Google Apps Script. Description Recently, I noticed that when a comment was posted to own Gists, and the numbers of stars and forks of own Gists were changed, the notification mail is not sent. Also I knew that in the current stage, there are no official methods for notifying them, yet.

Gists Consumer (personal) version of Google+ is closed on April 2, 2019. By this, Apps Script community of Google+ is also closed. This is one of important communities for discussing. So in this post, I would like to introduce the other communities related to Google Apps Script. As the next community of Apps Script community of Google+, Google Apps Script Community was launched. This is also introduced at How to get help of official site like this.

Gists This is a sample script for parsing query parameters from an URL using Google Apps Script. Also this can be used at Javascript. The process cost becomes a bit lower than that of the script using the regular expression. Sample script function parseQuery(url) { var query = url.split("?")[1]; if (query) { return query.split("&") .reduce(function(o, e) { var temp = e.split("="); var key = temp[0].trim(); var value = temp[1].trim(); value = isNaN(value) ?

Gists This is a script for modifying the shading color of paragraph on Google Document using Google Apps Script. Recently, by releasing Google Docs API, the shading color got to be able to be modified using the script. Here, I would like to introduce a sample script for modifying the shading color of the paragraph on Google Document. At the current Document Service, the shading color cannot be modified yet. I think that this will be achieved in the future update.

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.

Overview This is a script for extracting files from a tar file using Google Apps Script. This script was created by native Google Apps Script. The following 3 situations gave me the motivarion for creating this script. Although I had been looking for the script for extracting files from a tar file from before, I have still not been able to find it. Unfortunately, there are no methods for extracting the files from the tar file in Google Apps Script.

ProcessApp was updated to v1.0.1. v1.0.1 (February 10, 2019) New method of getExecutionTimeOfProcessType() was added. This method retrieves the total execution time of all functions by filtering the process type. For example, the total execution time of Web Apps can be retrieved. You can see the detail information here https://github.com/tanaikech/ProcessApp

Overview This is a library for retrieving the process and information of Google Apps Script. Methods getExecutionTimeOfTrigger() : This method retrieves the total execution time of all functions executed by the time-driven trigger at owner's account. For example, you can know the total execution time of all functions executed by the time-driven trigger in 24 h. getDevUrl() : This method retrieves the endpoint of developer mode for Web Apps like https://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.

“Taking advantage of Web Apps with Google Apps Script” was updated. A section of “How to use dev mode from outside” was added. When you deploy Web Apps, you can see the link labeled latest code. The link is like https://script.google.com/macros/s/###/dev. When you access to the link of latest code using your browser under you login to Google, you can access to Web Apps with the dev mode. But if you want to access to Web Apps with the dev mode from outside, there are no documents for the method.

Gists When it starts a calculation, open a dialog box. When the calculation is finished, close the dialog and retrieve the calculated result. This is a sample script for achieving above flow. This sample script supposes to use the container-bound script of Spreadsheet. When you use this, please run the function of run(). Sample script: function doSomething(e) { // Scripts for calculating. Utilities.sleep(3000); // This is a sample wait time.

Gists This is a sample script for closing the existing sidebar using Google Apps Script. When the sidebar is opened, in order to close the sidebar, the sidebar can be closed by running google.script.host.close() with the script of sidebar. This is the general case. If you want to close the opened sidebar, such functions are not prepared. So I thought this workaround. The flow of this workaround is as follows.

Gists This is a sample script for uploading multiple files from local PC to Google Drive using Google Apps Script. The dialog, sidebar and Web Apps can be used as the GUI interface. Sample 1 In this sample, the following flow is run. Select files at browser. Upload the files every file. Save each file in Google Drive. When you use this, please copy and paste the Google Apps Script and HTML to the script editor, and run the HTML using the dialog, sidebar and Web Apps.

Gists This is a sample script for summarizing Slides as thumbnails. For example, it supposes a Slides including 15 pages. When this script is run, it summarizes 6 pages to one page as images. I created this because there are no methods for directly achieving this. This is useful for myself. If this is also useful for you, I'm glad. The flow of this workaround is as follows. Flow: Copy the original Slides file as a temporary file.

Gists This is a sample script for retrieving the access token for Service Account using Google Apps Script. The flow for using this script is as follows. At first, please create the Service Account and retrieve JSON file. Put Scopes, private_key and client_email to the script. Run the script. var private_key = "#####"; // private_key of JSON file retrieved by creating Service Account var client_email = "#####"; // client_email of JSON file retrieved by creating Service Account var scopes = ["https://www.

v1.0.1 (December 5, 2018) When the filename have not been retrieved, an error have occurred. This bug was removed. You can see the detail information here https://github.com/tanaikech/DownloadLargeFilesByUrl

ImgApp was updated to v1.2.1. v1.2.1 (November 5, 2018) Efficiency of each loop was enhanced by this benchmark. You can see the detail information here https://github.com/tanaikech/ImgApp

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.

Overview ArrangeStackingOrder is a GAS library for arranging the stacking order of page elements on Google Slides using Google Apps Script (GAS). Demo This is a demonstration of this library when this is used as a Google Slides Addon. Description Do you have situations that you want to arrange the stacking order of page elements on Google Slides using GAS? I had it before. At that time, I could achieve it by creating a simple script.

Gists Kanshi Tanaike Overview This is a report about the possibility of asynchronous process using event triggers. This is for Google Apps Script (GAS). Description onEdit() which is a simple trigger is often used as a trigger when the values are modified on Spreadsheet. When users want to use the script including some methods which are required to be authorized as the onEdit event, a installable trigger of onEdit is used.

RangeListApp was updated to v1.0.1. v1.0.1 (September 13, 2018) New method of “expandA1Notations” was added. This method can expand the a1Notations. For example, A1:C2 is expanded to "A1","B1","C1","A2","B2","C2". **You can check this at https://github.

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.

FilesApp was updated to v1.0.1. Removed a bug. When there are files and folders without the parents, an error occurred. In this version, this issue was removed. GitHub of FilesApp

Overview DownloadLargeFilesByUrl is a GAS library for downloading large files from URL to Google Drive using Google Apps Script (GAS). Description I had been thinking of about whether a large file from an URL can be downloaded to Google Drive using GAS. When I have tried to download such large files, I noticed the following limitations. These limitations are due to the specification of GAS. When users download a file from URL using GAS, at the most users, it retrieves the blob using UrlFetchApp.

Gists This is a sample script for replacing text to image for Google Document using Google Apps Script (GAS). There is a method for replacing text to text at Class Text of DocumentApp. But there are not methods for replacing text to image. So I created this sample script. Demo : This sample image was created by k3-studio. Usage : replaceTextToImage(body, replaceText, image, width); body : body of document. You can set by DocumentApp.

Gists News At October 11, 2019, I published a Javascript library to to run the resumable upload for Google Drive. When this is used, the large file can be uploaded. You can also use this js library. Description This is a sample script for uploading files to Google Drive using Javascript. The files are uploaded by Drive API v3. gapi.client.drive.files.create() can create an empty file on Google Drive. But it cannot directly upload files including contents.

Gists Benchmark: Decreasing Loop for Array Processing using Google Apps Script 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. 1 So users always have to pay attention to reducing the process cost of the scripts. Especially, it is very important to know the process cost for the array processing, because the array processing is often used for spreadsheet and Google APIs.

Gists This is a sample script for retrieving screen shots of sites using Google Apps Script. In order to retrieve the screen shot, here, I used PageSpeed API. When you use this, please copy and paste the following script, and set an URL you want to retrieve a screen shot. var siteUrl = "### URL you want to retrieve a screen shot. ###"; var url = "https://www.googleapis.com/pagespeedonline/v4/runPagespeed?screenshot=true&fields=screenshot&url=" + encodeURIComponent(siteUrl); var res = UrlFetchApp.

OnedriveApp was updated to v1.1.1. I have to apologize you and all users. I had forgot that it added setProp(). I could notice about this by reporting at here. I would like to report because I could remove this bug. GitHub of OnedriveApp

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.

I updated the report of “Benchmark: Loop for Array Processing using Google Apps Script”. In this update, “reduce” was added. If this report is useful for your situation, I'm glad. **You can check this at https://gist.

Overview This is a Google Apps Script for notifying with email when Netatmo was down. Description I'm measuring the surrounding environment using Netatmo. There were little that my Netatmo was down so far. But recently, my Netatmo is sometimes down. It is considered that the reason is due to the thermal runaway, because the recent Japan is very hot. When Netatmo was down, users can know it by logging in using the browser and/or running the mobile application.

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.

This is a script for sending an email when users got comments at Stackoverflow. I had wished I could get an email when I got a comment at Stackoverflow. Although I investigated about this, it seems that I can get the email every 3 hours. For this situation, I wished I could get it much earlier. I thought that this can be achieved using GAS and Stack Exchange API. So I created this script.

Gists This is for adding the query parameters to the URL. This sample script is prepared by ES5. So this can be also used for Javascript. When I created an endpoint with some query parameters, I had used the scripts of various patterns every time. Today, I prepared this sample script to unify them. If this is also useful for you, I'm glad. Sample script : String.prototype.addQuery = function(obj) { return this + Object.

Gists Benchmark: Conditional Branch using Google Apps Script 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.1 So users always have to pay attention to reducing the process cost of the scripts. Especially, it is very important to know the process cost for the array processing, because the array processing is often used for spreadsheet and Google APIs.

ManifestsApp was updated to v1.0.3. v1.0.3 (July 11, 2018) By Google's update, “sheets” was added to manifests for installing the configuration of Macro. By this, this library was updated. You can see the added methods (getSheets(), setSheets()) at Usage. If you set “sheets”, please put the value of “sheets” as the resource like below sample. {"macros": [{"menuName": "QuickRowSum", "functionName": "calculateRowSum"}]} Don't put {"sheets": {"macros": [{"menuName": "QuickRowSum", "functionName": "calculateRowSum"}]}} **You can check this at https://github.

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.

Here, CLI tools, libraries, Add-ons, Reports, Benchmarks and Sample Scripts for taking advantage of Google Apps Script which are publishing in my blog, Gists and GitHub are summarized. If these are useful for you, I'm glad. **You can check this at https://github.

Gists There are no prepared methods for retrieving the number of lines in the Google Document. So I thought this workaround. If the end of each line can be detected, the number of lines can be retrieved. So I tried to add the end markers of each line using OCR. At Google Documents, when a sentence is over the page width, the sentence automatically has the line break. But the line break has no \r\n or \n.

Gists Benchmark: Search for Array Processing using Google Apps Script 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.1 So users always have to pay attention to reducing the process cost of the scripts. Especially, it is very important to know the process cost for the array processing, because the array processing is often used for spreadsheet and Google APIs.

Gists Overview This is a sample script which can achieve the resumable conversion from the large CSV-file to several spreadsheets by splitting the CSV file using Google Apps Script (GAS). Description Is there a situation that you want to convert a CSV file with the large size (> 50 MB) to Spreadsheet? When such large CSV file is converted to Spreadsheet, you will experience the error. The reason is the size and/or also it may be due to the total cells (> 2,000,000 cells) of CSV file.

Gists By the Google’s update at June 19, 2018, finally, Utilities.computeDigest(), Utilities.computeHmacSha256Signature() and Utilities.computeHmacSignature() got to be able to use the byte arrays. By this, using only native Google Apps Script, the result can be retrieved without using jsSHA. So Cryptopia API can be created using only Google Apps Script. If this is useful for you, I'm glad. Sample script : When you use this, at first, please input the requiring values.

Gists By the Google's update at June 19, 2018, finally, Utilities.computeDigest(), Utilities.computeHmacSha256Signature() and Utilities.computeHmacSignature() got to be able to use the byte arrays. By this, using only native Google Apps Script, the result can be retrieved without using jsSHA. When I used the updated them, the response speed is much faster than that of jsSHA. It is considered that this may be optimized for Google Apps Script. As a sample, it shows 2 samples as follows.

Overview FilesApp is a GAS library for retrieving file and folder list in Google Drive using Google Apps Script (GAS). Also this can create a tree from all files and folders in Google Drive. Description When I create some applications using Google Drive, there are often the case which is required to retrieve the file list and folder list. I had prepared the script each time for each case so far.

Gists This sample script is for retrieving a key with the maximum value from an object. This can be also used by Google Apps Script. var obj = {"a": 5, "b": 4, "c": 3, "d": 2, "e": 1}; var res = Object.keys(obj).reduce(function(a, c) { return obj[a] > obj[c] ? a : c; }); Logger.log(res); // >>> a

Gists Overview This is a sample script for easily retrieving the reformatted scripts without comments in a project using Google Apps Script (GAS). Description When I create GAS script, if the format of script is not correct, the script editor lets me know about it. By this, I can find that the script editor and/or Google Drive checks the format of scripts. I had wished if I could use this function.

Gists Overview This is a method for removing Third-party Apps with Account Access using a script. Demo Description When users create a script in a project and run the script, if the methods which are required to use scopes are included, users have to authorize to use the scopes using the browser. By authorizing it, users can use the script. The authorized projects can be seen at Third-party Apps with Account Access.

News At October 11, 2019, I published a Javascript library to to run the resumable upload for Google Drive. When this is used, the large file can be uploaded. You can also use this js library. Resumable Upload of Multiple Files with Asynchronous Process for Google Drive Overview This is a sample script for uploading files with large size (> 50 MB) at Web Apps using Google Apps Script (GAS).

Gists This sample script retrieves the difference elements between 2 dimensional arrays using Google Apps Script. In Google Apps Script, 2 dimensional arrays are often used at Google Docs and Google APIs. And from my recent report, it has already found that the process cost of filter() is the lowest in the other loop methods. So I use the script like this. var ar1 = [["a1", "b1", "c1"], ["a2", "b2", "c2"], ["a3", "b3", "c3"], ["a4", "b4", "c4"], ["a5", "b5", "c5"]]; var ar2 = [["a2", "b2", "c2"], ["a5", "b5", "c5"], ["a1", "b2", "c3"]]; var res = ar1.

Overview This is a library for running Batch Requests using Google Apps Script (GAS). Description When users use Google's APIs, one quota is used for one API call. When the batch request is used, several APIs can be called by one quota, although there are some limitations in the batch request. For example, in GAS, Drive API can be used be DriveApp. In this case, the quota is not used for using Drive API.

Gists This is a sample script which works the same action with the CLEAN method of VBA. The CLEAN method of VBA removes the characters of 0-31, 127, 129, 141, 143, 144, 157. Although I had looked for such method for Google Apps Script, I couldn't find it. So I created this. If this is useful for you, I'm glad. function cleanForGAS(str) { if (typeof str == "string") { var escaped = escape(str.

Overview This is a report to take advantage of Web Apps with Google Apps Script (GAS). Description There is Web Apps as one of applications using Google Apps Script (GAS). I sometimes use this Web Apps. But I have only a little the information for the specification of Web Apps. So in order to take more advantage of Web Apps, I investigated and summarized about this. The aim of this report is to become one of the basic information for creating various applications using Web Apps with GAS.

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.

Overview This is a library for running the concurrent processing using only native Google Apps Script (GAS). Description Have you ever thought about the concurrent processing using only native Google Apps Script (GAS)? So far, I had run the concurrent processing using golang, javascript and python. But the script cannot be used by the trigger event, because these are not native GAS. Recently, it was found that the fetchAll method added by the Google's update at January 19, 2018 is worked by the asynchronous processing.

Gists By Google's update at January 19, 2018, fetchAll method was added to the UrlFetch service. When I saw the usage, I couldn't find the detail information about the actual running state. So I investigated about it. As the result, it was found that the fetchAll method is worked by the asynchronous processing. The returned data is reordered by the order of requests. By this, it was also found that if you want to retrieve the data from the several URL, the process cost of UrlFetchApp.

Gists Benchmark: Loop for Array Processing using Google Apps Script July 26, 2018 Updated. Result of reduce was added. 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.1 So users always have to pay attention to reducing the process cost of the scripts. Especially, it is very important to know the process cost for the array processing, because the array processing is often used for spreadsheet and Google APIs.

Gists Introduction There are event objects at Google Apps Script. Typically, users which use Spreadsheet often use onEdit(event). Here, I would like to introduce the process costs for the event objects using this onEdit(event). When onEdit(event) is used for the spreadsheet, event of onEdit(event) has the following structure. { "authMode": {}, "range": { "columnStart": 1, "rowStart": 1, "rowEnd": 1, "columnEnd": 1 }, "source": {}, "oldValue": "old sample text", "user": { "nickname": "sampleName", "email": "sample email" }, "value": "sample text" } In this structure, for example, the range of active cell is "range": {"columnStart": 1, "rowStart": 1, "rowEnd": 1, "columnEnd": 1 }.

Overview This is a script for converting strings from NFD (Normalization Form Decomposition) to NFC (Normalization Form Composition) using Google Apps Script. Description Here, I would like to introduce a script for the unicode normalization using Google Apps Script. There are the characters with ゙ which is the voiced dot and the characters with ゚ which is the semi-voiced dot in Japanese language. When these are used for some applications,

RearrangeScripts was updated to v2.1.1 v2.1.1 (Match 2, 2018) Added a notification to turn on the switch for using Google Apps Script API. This was reported to issuetracker. https://issuetracker.google.com/issues/74048155 Acknowledgements: Alan Wells Add-on GitHub

Gists This is a sample script for transposing JSON object using Google Apps Script. Input data : [ {"key1":"a1","key2":"a2","key3":"a3","key4":"a4","key5":"a5"}, {"key1":"b1","key2":"b2","key3":"b3","key4":"b4","key5":"b5"}, {"key1":"c1","key2":"c2","key3":"c3","key4":"c4","key5":"c5"}, {"key1":"d1","key2":"d2","key3":"d3","key4":"d4","key5":"d5"}, {"key1":"e1","key2":"e2","key3":"e3","key4":"e4","key5":"e5"} ] Output data : { "key1": ["a1", "b1", "c1", "d1", "e1"], "key2": ["a2", "b2", "c2", "d2", "e2"], "key3": ["a3", "b3", "c3", "d3", "e3"], "key4": ["a4", "b4", "c4", "d4", "e4"], "key5": ["a5", "b5", "c5", "d5", "e5"] } Script : At first, keys have to be defined by yourself, because the order of json is not decided.

Gists This is a sample script for opening a site with new window using Google Apps Script. It is possible to open the site inside the opened dialog box using iframe. But in my situation, I had to open the site as new window. So I created this. As a sample application, it can think of like this. When the special keyword was inputted, open sites and files in Google Drive as a help window.

v2.1.0 (February 3, 2018) From this version, it got to be able to use the projects of both standalone script type and container-bound script type. Add-on GitHub

Gists These are sample scripts for adding a label to a message using message ID for Gmail. Sample 1 This sample adds a label to a thread using message ID. In this case, all messages in the thread have the label. Even if it adds a label to a message in the thread using addLabel(), all messages in the thread have the label, becauce addLabel can only be used for the thread.

Gists Description : This sample script is for retrieving emails which replied for received mails. Because there are no samples which confirm whether the owner (me) replied to the received mails, I created this. The point is as follows. When there are more than 2 messages in a thread, there might be a possibility to have replied. For more than 2 messages in a thread The email address of “from” for the 1st message is the sender's address.

ManifestsApp was updated to v1.0.2. v1.0.2 (January 29, 2018) ProjectApp2 is published, and got to be able to use both standalone script type and container-bound script type. By this, this library also got to be able to be used for the both projects. For this update, please enable Apps Script API. Please check “How to install”. **You can check this at https://github.

Overview This is a GAS project library for Google Apps Script (GAS). This library can be used for the projects of both standalone script type and container-bound script type. Description There are Class SpreadsheetApp and Class DocumentApp for operating spreadsheet and document, respectively. But there is no Class for operating GAS project. If there is such Class ProjectApp, GAS project can be directly operated by GAS script. I thought that this will lead to new applications, and created ProjectApp.

Gists Pattern 1 This is a sample script for copying GAS project to a container-bound script of Google Docs (Spreadsheet, Document and Form (and Slides)). The project is created as a new project. In order to use this sample, please do the following installation flow. If you use this sample script, at first, please test using a new project and new Google Docs. By this, please understand the work of this script.

Gists There is the bathing requests in the Google APIs. The bathing requests can use the several API calls as a single HTTP request. By using this, for example, users can modify filenames of a lot of files on Google Drive. But there are limitations for the number of API calls which can process in one batch request. For example, Drive API can be used the maximum of 100 calls in one batch request.

Gists This sample script is for using Zaif API by Google Apps Script. The following go script is a sample at Zaif API. package main import ( "fmt" "time" "strconv" "crypto/hmac" "crypto/sha512" "io/ioutil" "net/http" "encoding/hex" "net/url" "strings" ) var key = "<your_key>" var secret = "<your_secret>" func main() { uri := "https://api.zaif.jp/tapi" values := url.Values{} values.Add("method", "get_info") values.Add("nonce", strconv.FormatInt(time.Now().Unix(), 10)) encodedParams := values.Encode() req, _ := http.NewRequest("POST", uri, strings.NewReader(encodedParams)) hash := hmac.

Gists This sample script is for using Bittrex API by Google Apps Script. The following PHP script is a sample at bittrex.com. $apikey='xxx'; $apisecret='xxx'; $nonce=time(); $uri='https://bittrex.com/api/v1.1/market/getopenorders?apikey='.$apikey.'&nonce='.$nonce; $sign=hash_hmac('sha512',$uri,$apisecret); $ch = curl_init($uri); curl_setopt($ch, CURLOPT_HTTPHEADER, array('apisign:'.$sign)); $execResult = curl_exec($ch); $obj = json_decode($execResult); When this is converted to GAS, the script is as follows. function main() { var apikey = '#####'; // Please input your key. var apisecret = '#####'; // Please input your secret.

Gists This sample script is for using Binance API by Google Apps Script. This script encryptes “signature” like samples. In this script, “Example 1: As a query string” is used, and it retrieves “All orders (SIGNED)” by “GET”. function main() { var key = '#####'; // Please input your key. var secret = '#####'; // Please input your secret. var api = "/api/v3/allOrders"; // Please input API Endpoint you want.

ShapeApp for Google Slides was published as an add-on application When you use Google Slides, have you ever thought about creating and updating shapes on Slides by inputting parameters, and arranging selected shapes? I have thought about them. Recently, since Class SlidesApp was added to GAS, it came to be able to easily to create various applications for Slides. So I created this. This application is add-on application which was made of GAS.

Gists Experiment It has investigated the differences between the documents of “Search for Files” for v2 and v3. The following table shows the comparison of v2 and v3 for “Valid fields for files.list”. The column of DriveApp.searchFiles() means whether the query can be used for DriveApp.searchFiles(). v2 v3 Difference DriveApp.searchFiles() title name different v2: succeed, v3: fail fullText fullText same mimeType mimeType same modifiedDate modifiedTime same lastViewedByMeDate viewedByMeTime different v2: succeed, v3: fail trashed trashed same starred starred same parents parents same owners owners same writers writers same readers readers same sharedWithMe sharedWithMe same properties properties same properties with visibility='PRIVATE’ appProperties different v2: succeed, v3: fail visibility visibility same Result As the result, it is considered that DriveApp.

Gists This sample script is converted this sample script (javascript) to Google Apps Script. The point for converting is signature as shown in the following sample script. At Bitfinex API, after "/api/" + apiPath + nonce + rawBody is encrypted using HMAC SHA-384, the data of byte array is converted to HEX. In Google Apps Script, there is no the method for this. The data which was encrypted by Utilities.

RearrangeScripts was updated. Added “About” to the menu of add-on. By this, users can see the help document easily. Add-on GitHub

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.

RearrangeScripts was published as an add-on application Recently, I have reported RearrangeScripts for rearranging scripts in a GAS project. At that time, I got messages and mails from many developers. They said that you should publish this as an add-on. So, this was released. Now you can search “RearrangeScripts” as an add-on for Spreadsheet. If this is helpful for you, I'm happy. Add-on GitHub Demo

Gists This is a document for explaining the difference between given values and retrieved values for shapes on Google Slides. When a shape is created to a slide using Slides API, most users give the size of height and width as pt. When the size is retrieved from the created shape as pt, the size is often difference from the given size. For example, when a square shape is created by giving the height and width of 100 pt, the size which is retrieved from the created square becomes 99.

Gists This is a sample script for retrieving clientId using Google Apps Script. var accessToken = ScriptApp.getOAuthToken(); var url = "https://www.googleapis.com/oauth2/v3/tokeninfo?access_token=" + accessToken; var params = { method: "post", headers: {"Authorization": "Bearer " + accessToken} }; var res = UrlFetchApp.fetch(url, params).getContentText(); var clientId = JSON.parse(res).azp; Logger.log(clientId)

Gists This is a sample script for downloading a file using a button of dialog box on Google Docs (Spreadsheet, Document and Slides). Please use this sample script at script editor on Google Docs (Spreadsheet, Document and Slides). And please set file ID in the script. FLow : The flow of this sample script is as follows. Run dialog(). Open a dialog. When users click a download button, retrieve file ID at GAS side.

Gists Introduction Recently, I have introduced a GAS library and a CLI tool for rearranging files in GAS project. Those are RearrangeScripts and ggsrun. Because today, I found a new way for rearranging files, I would like to introduce it although there is a restriction. By the recent Google-update, users become able to create folders in GAS project. The new way uses this. At first, please see the demonstration GIF animation.

Gists This sample script is for retrieving an instance of user-interface environment for Spreadsheet, Document and Slides. When I create applications which use user interface (for example, sidebar, dialog and so on), the user interface can be used for Spreadsheet, Document and Slides. If the application doesn't use the methods depend on Spreadsheet, Document and Slides, this script can give 3 choices to users. function getUi() { var ui; try { ui = SpreadsheetApp.

Gists This is a sample script for combining and mixing 2 objects. Each object is an array which included a dictionary type. When the key of the dictionary object is the same, the values are mixed. This can be also used for Google Apps Script. Input var obj1 = [ {"key1": ["value1a1", "value1a2"]}, {"key1": ["value1aa1", "value1aa2"]}, {"key2": ["value2a1", "value2a2"]}, {"key3": ["value3a1", "value3a2"]}, ]; var obj2 = [ {"key1": ["value1b1", "value1b2"]}, {"key3": ["value3b1", "value3b2"]}, {"key3": ["value3bb1", "value3bb2"]}, {"key4": ["value4b1", "value4b2"]}, ]; Output [ {"key1": ["value1a1", "value1a2", "value1b1", "value1b2", "value1aa1", "value1aa2"]}, {"key2": ["value2a1", "value2a2"]}, {"key3": ["value3a1", "value3a2", "value3b1", "value3b2", "value3bb1", "value3bb2"]}, {"key4": ["value4b1", "value4b2"]} ] Sample script : Javascript : function mixture(obj1, obj2) { Array.

Gists Introduction By recent Google update (Google update at October 24, 2017), various new winds to GAS developers were blown. There is “Manifests” as one of the new winds. “Manifests” makes us manage the project using JSON. Especially, the special scopes which have to use OAuth2 process can be used by only setting them to the Manifests. I think that this is the largest modification. However, when scopes are added to a project using Manifests, users who use the project can use only added scopes.

Gists This sample script is for retrieving the size (width and height) of a table in Google Slides using Google Apps Script. There are no methods for directly retrieving the table size using SlidesApp yet. So I thought of a workaround using Slides API. When the slide information is retrieved using Slides.Presentations.Pages.get() of Slides API, the information of tables is also included. In the information, the height and width of table are also included.

Gists By recent Google updated, Class SlideApp is added to Google Slides. SlideApp will be bring a lot of applications. Here, I would like to introduce 2 samples. 1. Sidebar function showSidebar() { var html = HtmlService .createHtmlOutput('Hello, world! <input type="button" value="Close" onclick="google.script.host.close()" />') .setTitle('My custom sidebar') .setWidth(300); SlidesApp.getUi().showSidebar(html); } 2. Copy slides in existing Slide to a new Slide This sample script create a new Slide with slides you want to copy.

Overview This is a GAS application for rearranging Google Apps Scripts (GAS) in a project which can be seen at the script editor. Description Have you ever thought about rearranging Google Apps Scripts in a project which can be seen at the script editor? I also have thought about it. Finally, I could find the workaround to do it. And recently, I have given this function to ggsrun which is a CLI tool.

Overview This is a library for zipping a folder using Google Apps Scripts. Description When users manually download a folder on Google Drive, users can download all files in the folder as a zip file using the web interface. There are zip tools in Class Utilities of Google Apps Script. However, the zip tools cannot create a zip file from a folder. And it cannot retrieve all files included any folders in a folder.

Overview This is a Manifests library for Google Apps Scripts. Description By recent update of Google, Manifests was added to Google Apps Script Project. At the moment I saw the detail, I thought that this Manifests will blow a new wind for a lot of GAS developers. So I created this. This library makes users easily access Manifests using Google Apps Script. If this was useful for you, I'm glad.

Overview This is a GAS project library for Google Apps Script (GAS). Description There are Class SpreadsheetApp and Class DocumentApp for operating spreadsheet and document, respectively. But there is no Class for operating GAS project. If there is the Class ProjectApp, GAS project can be directly operated by GAS script. I thought that this will lead to new applications, and created ProjectApp. On the other hand, as a CLI tool for operating GAS project, there has already been ggsrun.

Gists This is a sample script for uploading local file to Google Drive without the authorization using HTML form. A selected file in your local PC using HTML form is uploaded to Google Drive and saved to Google Drive. When you use this, at first, please deploy Web Apps. The script is doPost() of following scripts. Script : Google Apps Script function doPost(e) { var data = Utilities.base64Decode(e.parameters.data); var blob = Utilities.

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.

OnedriveApp was updated to v1.1.0. From this version, retrieving access token and refresh token became more easy. GitHub of OnedriveApp

Gists This is a sample script for selecting files in Google Drive using HTML select box for Google Apps Script. Feature Feature of this sample. It is a simple and space saving. When the folder is selected, the files in the folder are shown. When the file is selected, the ID of file is retrieved. Users can use this ID at GAS. When a folder is opened, all files in the folder are cached.

Gist This sample script is for uploading image files to Slack using Incoming Webhooks by Google Apps Script. When users try to upload image files to Slack using Incoming Webhooks, it has been known that although the access token is required to directly upload them, Incoming Webhooks can upload them by using the tag of image_url. In this sample script, it uploads image files (BMP, GIF, JPEG and PNG) on Google Drive to Slack using Incoming Webhooks.

Gists This sample script is for retrieving files with filename included special characters using Google Apps Script. The files are used on Google Drive. The files with filename of special characters cannot be retrieved using DriveApp.getFilesByName(). This workaround solved this. As a query parameter, name contains 'filename with special characters' is used. This contains is very important. name='filename with special characters' cannot retrieve such files. Today, it was found that name contains 'filename with special characters' is the workaround.

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.getSheet().getParent().getId(); >>> id ---> 123456789abcdefg

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.name = "getValueByKey"; function getValueByKey(sheetname) { var alldata, e, header, ss; try { ss = SpreadsheetApp.

OnedriveApp was updated to v1.0.2. Moved the instance of PropertiesService.getScriptProperties() to outside of this library. When there is the PropertiesService.getScriptProperties() inside the library, it was found that the parameters that users set was saved to the library. So this was modified. I'm sorry that I couldn't notice this situation. GitHub of OnedriveApp

OnedriveApp was updated to v1.0.1. Added a method for retrieving access token and refresh token using this library. By added this method, OneDrive APIs can be used by only this library. GitHub of OnedriveApp

Gists This sample script is for changing values by checking duplicated values of JSON for Javascript. Please see the following script. There is an array with a JSON data with 3 keys and 3 values. It is found that the values for each element duplicate. These duplicated values are changing by adding numbers. I use this for managing filenames. This script also can be used for Google Apps Script. If this was useful for you, I'm glad.

ImgApp was updated to v1.2.0. New method was added. 3. updateThumbnail() Overview This method is for updating thumbnail of files on Google Drive using images you selected. Description For example, zip files don't have the thumbnail on Google Drive. An icon is shown as the thumbnail. For the most files, Google Drive can create automatically each thumbnail. But there are sometimes files which cannot be created the thumbnail. Zip file is also one of them.

This is a library of Google Apps Script for using Microsoft OneDrive. Feature This library can carry out following functions using OneDrive APIs. Retrieve file list on OneDrive. Delete files and folders on OneDrive. Create folder on OneDrive. Download files from OneDrive to Google Drive. Upload files from Google Drive to OneDrive. Demo You can see the detail information here https://github.com/tanaikech/OnedriveApp

Gists This sample script is for the interconversion between Google Docs (document, spreadsheet and presentation) and Microsoft Docs (word, excel and powerpoint). The feature is to convert them without Advanced Google Services. Since Advanced Google Services is not used for this, if you publish your script with this script, you are not necessary to explain how to install Advanced Google Services. This script converts between Google Docs and Microsoft Docs using UrlFetchApp.

Gist Overview This GAS sample is for retrieving access token to use OneDrive APIs using Google Apps Script. In this script, the authorization code is automatically retrieved. Demo Usage In order to use this, both accounts of Google and OneDrive (MSN) are required. Google side Copy and paste the sample script to your script editor. You can use the standalone script for this. Deploy Web Apps. On the Script Editor File -> Manage Versions -> Save New Version Publish -> Deploy as Web App -> At Execute the app as, select “your account” -> At Who has access to the app, select “Only myself” -> Click “Deploy” -> Copy URL of “latest code” (This is important!

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 These sample scripts are for requesting multipart post using Google Apps Script. In most cases, the multipart request is used for uploading files. So I prepared 2 sample situations as follows. For each situation, the request parameters are different. Upload a file from Google Drive to Slack. Convert an excel file to Spreadsheet on Google Drive using Drive API v3. Multipart post is required for these situations.

Gist This is a sample script for retrieving images on Spreadsheet. Unfortunately, there are no methods for retrieving directly images on spreadsheet using GAS. So I use the method which retrieves URL from =image(URL) and retrieves the image from the URL. In this case, =image(URL) has to be in the cell. Images embedded by insertImage() cannot be retrieved. At first, please confirm them. Sample script : var cell = "A1"; // Cell address with the function of "=image()" var filename = "samplename"; // Output filename var image = SpreadsheetApp.

This sample is for retrieving array coordinates of duplicated elements. Script : var inputdata = ["a", "b", "b", "c", "d", "c", "e", "a", "f", "g"]; var dic = {}; var result = []; inputdata.forEach(function(e, i){ if (dic[e]) { result[i] = 'Duplicated'; } else { result[i] = null; } dic[e] = "temp"; }); Logger.log(JSON.stringify(result)) Logger.log([i for (i in result) if(result[i]=='Duplicated')]) Result : [null,null,"Duplicated",null,null,"Duplicated",null,"Duplicated",null,null] [2, 5, 7]

Gists This is a sample of Google Apps Script. This script is for retrieving all files and folders under a folder on Google Drive. All files and folders in the specific folder can be retrieved. If you want to retrieve file list with all files and folders on Google Drive, please use DriveApp.getRootFolder().getId() as folderId. When there are a lot of files in the folder, it may be over the limitation time to execute script.

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.

Gist This is a sample script for downloading files from Google Drive under no authorization using browser. By using this sample, you can make other users download files from your Google Drive. Even if the other users are not Google users, they can download the files. Demo This is a demonstration for downloading files from Google Drive under no authorization using browser. From the top document, You can see that an user who is not owner of Google Drive is downloading files.

ImgApp was updated to v1.1.0. New method was added. 2. doResize() Overview This method is for resizing images. Description Unfortunately, there are no methods to resize images at Google Apps Script. As a workaround, there is a method that it imports the image in Google Document and resizes the image using setWidth() and setHeight(). But in this method, the resized blob cannot be retrieved. So although I had thought of other workaround, I had not been able to find it.

1. getSize() Overview This method is for retrieving the width and height of image as the unit of pixel. Description Unfortunately, there are no methods to directly retrieve the image size at Google Apps Script. As a workaround, there is a method that it imports the image in Google Document and retrieves the size using getWidth() and getHeight(). But in this method, it uses much time and resources on Google. So I thought of retrieving the information of image at the binary level, and created this.

Gists This sample is for retrieving access token for Google APIs. I created this for studying newStateToken(). Preparation In order to use this sample, please do as follows. Deploy and launch Web Apps for retrieving redirect uri On the Script Editor File -> Manage Versions -> Save New Version Publish -> Deploy as Web App -> At Execute the app as, select “your account” -> At Who has access to the app, select “Only myself” -> Click “Deploy” -> Click “latest code” (By this click, it launches the authorization process.

SOUWA means summing in Japanese. SOUWA can sum string elements in an array at the high speed. The speed of SOUWA with the pyramid algorithm is about 380 times faster than that of the standard method. New algorithm for summing array elements was developed for SOUWA. You can see the detailed report of this library at here. If you are interested in this, I'm glad. It was updated to v1.0.2. Please check it out.

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 } }; .setOption('hAxis', parameters) Retrieve Parameters From Chart For the chart created by above parameters, in order to retrieve the parameters, it uses following script.

Description There are a limit executing time for Google Apps Script (GAS). It's 6 minutes. So users have to pay attention to the process cost of the script. GAS can use JavaScript 1.7. This means to be able to be used comprehension for GAS. In this report, the process cost for the comprehension has been investigated. The normal for loop was used as the competitor. As a result, it was found that the comprehension can be used one of methods for reducing the process cost.

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 retrieves all files in a folder with spreadsheet. When there are some folders in the folder with spreadsheet, this script can retrieve all files in all folders. This script has to be a container-bound script for spreadsheet. Script : function getFileList(){ var folderlist = (function(folder, folderSt, results){ var ar = []; var folders = folder.getFolders(); while(folders.hasNext()) ar.push(folders.next()); folderSt += folder.getId() + "#_aabbccddee_#"; var array_folderSt = folderSt.split("#_aabbccddee_#"); array_folderSt.pop() results.push(array_folderSt); ar.

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.

This sample script converts from Microsoft Docx File on Google Drive to Google Spreadsheet, and converts to HTML file. Drive APIs v2 and v3 are used for this. Please set as follows. Drive API v2 Drive API v3 “Drive API v2” can be used at Google Apps Script by enabling Drive API of Advanced Google services and of Google API Console. How to use it is as follows.

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 script changes slack status using GAS. If you want to change it on time you decided, it can be achieved by installing this method as a trigger. In order to use this, the required scope is users.profile:write. function setSlackStatus(token, user, status_text, status_emoji) { return UrlFetchApp.fetch( 'https://slack.com/api/users.profile.set', { method: 'post', payload: { token: token, user: user, profile: JSON.stringify({status_text: status_text, status_emoji: status_emoji}) }, muteHttpExceptions: true } ).getContentText(); } function main() { var res = setSlackStatus( '### Your access token ###', '### User ID ###', 'sample', ':coffee:' ); Logger.

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 converts a spreadsheet to excel file, and overwrites the excel file to the existing excel file. When you use this script, at first, please confirm whether Drive API is enabled at Google API console.Because the existing excel file is overwritten, the file name and file ID are not changed. function overWrite(src_spreadsheetId, dst_excelfileId) { var accesstoken = ScriptApp.getOAuthToken(); return UrlFetchApp.fetch( "https://www.googleapis.com/upload/drive/v3/files/" + dst_excelfileId + "?uploadType=multipart", { method: "PATCH", headers: {Authorization: "Bearer " + accesstoken}, contentType: "application/vnd.

Converting Spreadsheet to PDF This sample script converts from a spreadsheet to a PDF file which has all sheets in the spreadsheet. When you use this, please enable Drive API at Google API console. var spreadsheetId = "#####"; var folderId = "#####"; var outputFilename = "#####"; DriveApp.getFolderById(folderId) .createFile(UrlFetchApp.fetch( "https://www.googleapis.com/drive/v3/files/" + spreadsheetId + "/export?mimeType=application/pdf", { method: "GET", headers: {Authorization: "Bearer " + ScriptApp.getOAuthToken()}, muteHttpExceptions: true }) .getBlob()) .setName(outputFilename);

Recently, I had been looking for creating an image from coordinate data. Unfortunately I have never found them. So I made this. This Google Apps Script (GAS) library creates an image file from coordinate data. You can see the detail information at https://github.com/tanaikech/CreateImg. There is a part where I would like to improve in this library. That's convByteSlice(). I think that there is the method to be faster about the part.

Here, I would like to introduce a comprehension of GAS. Input : var data = [[[0], [1], [2], [3]], [[4], [5], [6], [7]]]; Output : [[0.0, 2.0], [0.0, 2.0]] Pattern 1 var a = []; for (var i=0; i<data.length; i++) { var temp = []; for (var j=0; j<data[i].length; j++) { if (data[i][j][0] % 2 == 0) temp.push(j); } a.push(temp); } Logger.log(a) Pattern 2 var b = []; data.forEach(function(e1){ var temp = []; e1.

These scripts can be executed on Script Editor. But, in order to use these, you have to enable Drive API of Advanced Google services and of Google API Console. “Drive API v2” can be used at Google Apps Script by enabling Drive API of Advanced Google services and of Google API Console. How to use it is as follows. In the script editor, select Resources > Advanced Google services

This is a sample GAS script to create an Excel file, which was downloaded from web, as Spreadsheet. By using Drive API, it can be achieved without access token. Script : function downloadFile(fileURL, folder) { var filename = fileURL.match(".+/(.+?)([\?#;].*)?$")[1]; var response = UrlFetchApp.fetch(fileURL); var rc = response.getResponseCode(); var blob = response.getBlob(); var resource = { "mimeType": "application/vnd.google-apps.spreadsheet", "parents": [{id: folder}], "title": filename }; var res = Drive.Files.insert(resource, blob); var fileInfo = [rc, res.

There are a lot of APIs on Google. When we use Google Drive APIs, they usually have “fields” as a resource. The parameter “fields” gives various information which is selected to us. This is one of important parameters. And this can be used at Google Apps Script (GAS) although that version is v2. About how to use it, there are some documents. But it is difficult to find how to use it at GAS.

This is a sample script of GAS for converting file name to file id on Google Drive. Drive API is used for this. So please enable Drive API at Advanced Drive Services and Google API Console. function nameToId(filename){ return [i.id for each (i in Drive.Files.list({q: "title='" + filename + "' and trashed=false"}).items)]; }

Overview In this article, files can be downloaded without authorization. Description When we download files from Google Drive, it usually has to use Drive API. In order to use Drive API, access token is required. If you want to make your friends download files from your Google Drive, the authorization process is to take time. Also Web Link for each files can be used. But it has to set for each files.

This is a sample script for creating a folder tree including all folders in Google Drive. For each element, parent folder and sub folder are retrieved. Script : var results = (function(folder, folderSt, results){ var ar = []; var folders = folder.getFolders(); while(folders.hasNext()) ar.push(folders.next()); folderSt += folder.getName() + "(" + folder.getId() + ")#_aabbccddee_#"; var array_folderSt = folderSt.split("#_aabbccddee_#"); array_folderSt.pop() results.push(array_folderSt); ar.length == 0 && (folderSt = ""); for (var i in ar) arguments.

This is a script to export a CSV file from spreadsheet and make an user download it. When the users download it, they can download by push a button made by this script. In order to use this script, put both HTML and script in a GAS project. html : This file name is “download.html”. <!DOCTYPE html> <html> <body> Download CSV? <form> <input type="button" value="ok" onclick="google.script.run .withSuccessHandler(executeDownload) .saveAsCSV();" /> </form> </body> <script> function executeDownload(url) { window.

This is a script to send e-mail with a xlsx file converted from spreadsheet as an attachment file. Access token is necessary to use this script. function excelSender() { var accesstoken = "[your accesstoken]"; var sheetID = "[sheet id]"; var xlsxName = "[output xlsx file name]" var params = { "headers" : {Authorization: "Bearer " + accesstoken}, "muteHttpExceptions" : true }; var dUrl = "https://www.googleapis.com/drive/v3/files/" + sheetID + "/export?mimeType=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" var xlsxlFile = UrlFetchApp.

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

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

File upload using HTML form in GAS project Rule Following scripts have to be made into a project of Google Apps Script. Deploy the GAS project as a web application. Ref After updated the script, it has to be updated as a new version. Form.html : <html> <body> <form> <input type="file" name="imageFile"> <input type="button" value="ok" onclick="google.script.run.upload(this.parentNode)"> </form> </body> </html> GAS : function doGet() { return HtmlService.

This is a sample script for retrieving cells without blank cells. Figure 1 shows the sample spreadsheet. In this sheet, the row 14 has one space. Fig. 1: Sample spreadsheet. Data is retrieved as follows. var data = SpreadsheetApp .getActiveSpreadsheet() .getActiveSheet() .getRange('a1:a30') .getValues(); 1. Retrieving cells with spaces and no blank cells. var Result = [i for each (i in data)if (i)].

Abstract I have already reported that the pyramid method is one of very effectively algolithms for summing string elements in an array using Google Apps Script (GAS). This report describes the adaptability of the pyramid method to any languages except for GAS. c++ (g++), Go, Java, Javascript on Node.js, Python and Ruby were chosen as the sample languages. In those languages, there are languages which have the distinctive commands for summing the array elements.