Gists
Abstract This report outlines a Google Apps Script solution for directly exporting web-published Google Docs to PDF. By circumventing limitations in published URLs, the script enables convenient PDF generation without manual intervention.
Introduction Google Sheets and Google Docs offer the convenient feature of web publishing, providing readily accessible URLs for sharing. Ref
Google Sheets: https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml Google Docs: https://docs.google.com/document/d/e/2PACX-###/pub These URLs utilize a unique ID (###) for each document.
Gists
Abstract A new library, MimeTypeApp, simplifies using Gmail messages and attachments with the Gemini API for tasks like text analysis. It converts unsupported formats for seamless integration with Google Apps Script and Gemini.
Introduction Recently, I published MimeTypeApp, a Google Apps Script library that simplifies parsing Gmail messages, including attachments, for use with the Gemini API. Ref This library addresses a key challenge: Gmail attachments come in various MIME types, while the Gemini API currently only accepts a limited set for processing.
Gists
Abstract This is a Google Apps Script library for converting files from various MIME types to a specified target MIME type. The library accepts both file IDs and blobs as input values.
Introduction Recently, I encountered a scenario where I needed to convert files of various MIME types to a specific target MIME type. While converting files with known source MIME types is relatively straightforward, the process becomes more complex when the source MIME type is unknown.
GeminiWithFiles was updated to v2.0.3 v2.0.3 (November 19, 2024)
I modified the specification of setFileIdsOrUrlsWithResumableUpload. From v2.0.3, when you use this method, please include propertiesService: PropertiesService.getScriptProperties() into the initial object as follows. Because, when PropertiesService.getScriptProperties() is used in the library, the values are put into the library. When I created Ref and Ref, I supposed that the script is used by copying and pasting instead of the library.
Gists
Description Recently, I reported on a workaround for effectively working with Google Sheets tables using Google Apps Script: Ref. This approach addressed limitations in directly retrieving table data and ranges within Apps Script. In this follow-up report, I’m excited to provide a sample script that leverages this workaround to export your valuable Google Sheets tables directly as PDF files. This functionality empowers you to easily share and distribute your data in a clear and universally accessible format.
Gists
Abstract Google Sheets’ new Tables feature enhances data organization but lacks direct management via Apps Script. This report proposes a workaround solution using Apps Script until native support arrives.
Introduction Google Sheets recently introduced a new feature called Tables. Ref Tables offer a powerful way to organize and manage your data by transforming unformatted ranges into structured datasets with automatic headers, filtering options, and data validation capabilities. This not only improves the readability and maintainability of your spreadsheets but also allows for seamless integration with existing Google Sheets functions.
Gists
Abstract Google Apps Script offers Document service for basic document tasks and Google Docs API for advanced control, requiring more technical expertise. This report bridges the gap with sample scripts to unlock the API’s potential.
Introduction Google Apps Script provides two powerful tools for managing Google Documents: the Document service (DocumentApp) and the Google Docs API. Ref, Ref While the Document service offers a user-friendly interface for common document manipulation tasks within Apps Script, it has limitations.
Gists
Abstract This report showcases a practical application of Google Apps Script, demonstrating how new JavaScript methods can be used to create a script that automatically transfers selected rows between sheets in a Google Sheet.
Introduction JavaScript, a fundamental pillar of contemporary web development, has experienced a significant rise in popularity due to its versatility and widespread adoption. As JavaScript’s influence has expanded, so too has Google Apps Script, a cloud-based scripting language constructed on the V8 JavaScript engine.
Gists
Abstract Gemini excels at text generation with RAG for large datasets, but smaller ones benefit from prompting or data upload. This report explores using Gemini 1.5 Flash/Pro with RAG on medium-sized, Google Spreadsheet-stored datasets for improved accuracy and effectiveness.
Introduction Gemini’s text generation capabilities have seen significant advancements with the Retrieval-Augmented Generation (RAG). This approach excels for large datasets, where embedding data and querying the model leads to high-quality answers.
GeminiWithFiles was updated to v2.0.2 v2.0.2 (September 26, 2024)
As the option for generationConfig, the properties response_schema and temperature were added. You can see the detail information here https://github.com/tanaikech/GeminiWithFiles
Gists
Abstract This report presents a method to train AI to effectively generate content from smaller, structured datasets using Python. Gemini’s token processing capabilities are leveraged to effectively utilize limited data, while techniques for interpreting CSV and JSON formats are explored.
Introduction In the era of rapidly advancing artificial intelligence (AI), the ability to analyze and leverage large datasets is paramount. While RAG (Retrieval Augmented Generation) environments are often ideal for such tasks, there are scenarios where content generation needs to be achieved with smaller datasets.
Gists
Abstract This report improves Gmail email labeling with Gemini API using JSON schema and leverages advancements in Gemini 1.5 Flash for faster processing.
Introduction As Gemini continues to evolve, existing scripts utilizing its capabilities can be revisited to improve efficiency and accuracy. This includes the process of flexible labeling for Gmail emails using the Gemini API. I have previously explored this topic in two reports:
December 19, 2023: Demonstrating Gmail label selection based solely on prompts.
Abstract This post introduces a Google Apps Script solution that automates the creation, sharing, and monitoring of multiple Google Spreadsheets, providing a more efficient and streamlined approach to managing user data.
Introduction I’ve often encountered requests from clients who need to manage multiple Google Spreadsheets for various users, often by copying a template spreadsheet. In these situations, I typically propose the following approach:
Create a Template Spreadsheet: This spreadsheet serves as a blueprint, containing essential elements like custom functions implemented using Google Apps Script.
Gists
Abstract This report presents a method to optimize AI-generated scripts for processing costs using Gemini and Google Apps Script. By incorporating external knowledge from sources like StackOverflow, we demonstrate the effective generation of efficient scripts that minimize overhead while maintaining desired outcomes. This approach can be considered a dynamic pseudo-RAG technique.
Introduction The proliferation of generative AI, exemplified by Google Gemini, has led to a surge in AI-generated scripts. This trend is evident in the growing number of questions on platforms like StackOverflow that involve AI-generated scripts.
UtlApp was updated to v1.0.7. v1.0.7 (September 4, 2024)
Following 3 methods were added. snake_caseToCamelCase: This method is used for converting a string of the snake case to the camel case. camelCaseTosnake_case: This method is used for converting a string of the camel case to the snake case. createFormDataObject: This method is used for creating the form data to HTTP request from an object. You can see the detail information here https://github.
Gists
Abstract A script using resumable upload with file streams is proposed to enhance file handling within the Gemini Generative AI API for Node.js. This script allows uploading from web URLs and local storage, efficiently handles large files, and offers potential reusability with other Google APIs.
Description The @google/generative-ai library provides a powerful way to interact with the Gemini Generative AI API using Node.js. This enables developers to programmatically generate creative text formats, translate languages, write different kinds of creative content, and answer your questions in an informative way, all powered by Gemini’s advanced AI models.
Gists
Abstract This study proposes a workaround to address the Gemini API’s current inability to directly process web content from URLs. By utilizing Google Apps Script, the method extracts relevant information from a specified URL and feeds it into the API for summarization. This approach offers a solution for generating comprehensive summaries from web-based content until the API’s limitations are resolved.
Introduction While Gemini API offers powerful text generation capabilities, it currently faces limitations in directly accessing and processing web content from URLs.
Gists
Abstract Linking a Google Apps Script project to a GCP project enables you to export logs from the Class console to Logs Explorer for simplified analysis and debugging. By overcoming the limitations of in-script logging methods, this report outlines a method for exporting logs using the Cloud Logging API with Google Apps Script.
Introduction While developing applications with Google Apps Script, the Class console is a valuable tool for debugging individual components.
Gists
Overview This sample script demonstrates uploading multiple files using split asynchronous processes with resumable upload. It leverages JavaScript and HTML within Google Spreadsheets.
Description In my previous report, “Resumable Upload of Multiple Files with Asynchronous Process for Google Drive”, I presented an approach for uploading files asynchronously.
This script builds upon that concept, introducing a method for uploading multiple files with split asynchronous processes that utilize resumable upload.
Here’s the process breakdown:
Gists
Abstract This report proposes a novel learning method using Gemini to automate Q&A generation, addressing the challenges of manual Q&A creation. By integrating with Google tools, this approach aims to enhance learning efficiency, accessibility, and personalization while reducing costs.
Introduction Mastering a new subject often demands a significant time commitment. A proven strategy for efficient learning is through question-and-answer (Q&A) practice. This method typically involves constructing a dataset of pertinent Q&A pairs and subsequently engaging in repeated practice until desired proficiency levels are achieved.
GeminiWithFiles was updated to v2.0.1 v2.0.1 (August 4, 2024)
From this version, codeExecution can be used. Ref You can see the detail information here https://github.com/tanaikech/GeminiWithFiles
UnlockSmartInvoiceManagementWithGeminiAPI was updated to v1.0.3. v1.0.3 (August 3, 2024)
On August 3, 2024, I upated GeminiWithFiles (https://github.com/tanaikech/GeminiWithFiles). In this version, PDF data can be processed with Gemini API without async/await. So, I updated UnlockSmartInvoiceManagementWithGeminiAPI. You can see the detail information here https://github.com/tanaikech/UnlockSmartInvoiceManagementWithGeminiAPI
GeminiWithFiles was updated to v2.0.0 v2.0.0 (August 3, 2024)
From this version, the following changes were made. PDF data can be directly used. Ref By this, PDFApp is not required to be used. By this, the script can be used without async/await. As the default, functions: {} is used. So, the default function calling was removed. Because in the current stage, JSON output can be easily returned using a JSON schema and response_mime_type.
Gists
Abstract Gemini API now enables direct PDF processing for content generation, eliminating image conversion and reducing costs. This report provides a sample script to demonstrate this new capability and its potential applications.
Introduction Gemini API has recently introduced the ability to directly process PDF data for content generation, significantly enhancing its capabilities. Previously, to utilize PDF data for content creation, it was necessary to convert each PDF page into a separate image format.
Gists
Description Great news for fans of both Google Docs and Markdown! Google Docs recently acquired the ability to export documents directly into the markdown format. Ref
This functionality extends beyond the user interface, with early indications suggesting the Google Drive API might also be capable of converting between Google Docs and Markdown. I confirmed that this could also be achieved by Drive API. This opens exciting possibilities for automated workflows.
UnlockSmartInvoiceManagementWithGeminiAPI was updated to v1.0.2. v1.0.2 (July 23, 2024)
On July 23, 2024, I noticed that PDF data could be directly parsed by Gemini API. It is considered that this is due to the update by the Google side. So, I updated setBlobs([blob], true) to setBlobs([blob], false) of the method parseInvoiceByGemini_. By this modification, the PDF blob is directly used with Gemini API. Ref You can see the detail information here https://github.
Gists
Abstract Uploads in Google Apps Script are limited to 50 MB, hindering work with large datasets. This report introduces a script with uploadType=resumable to overcome this limit, enabling uploads over 50 MB to Gemini and other services.
Introduction This report explores the limitations of data upload size using Google Apps Script and introduces a script to overcome these limitations. In the current stage, Gemini API can generate content using the uploaded data to Gemini.
GeminiWithFiles was updated to v1.0.7. v1.0.7 (July 4, 2024)
From this version, when doCountToken: true and exportTotalTokens: true are used in the object of the argument of geminiWithFiles, the total tokens are returned. In this case, the returned value is an object like {returnValue: "###", totalTokens: ###}. Ref You can see the detail information here https://github.com/tanaikech/GeminiWithFiles
TriggerApp was updated to v1.0.3. v1.0.3 (June 26, 2024)
The calculation for increasing the month was modified. A new scenario 8 was added. In scenario 8, you can see how to use the month-end. Ref You can see the detail information here https://github.com/tanaikech/TriggerApp
Gists
Abstract This report provides a Google Apps Script to retrieve all files, including those within subfolders, for a designated folder. It addresses the challenges of retrieving files within deeply nested folder structures and obtaining complete file paths.
Introduction Google Apps Script empowers developers to interact with Google Drive data programmatically, unlocking a wide range of functionalities. A core component of this interaction is the Drive service (DriveApp) and Drive API.
MoveFolder was updated to v1.0.1. v1.0.1 (June 18, 2024)
In the recent update on the Google side, it was found that in the current stage, when the other libraries are loaded from a library, an error like We're sorry, a server error occurred while reading from storage. Error code NOT_FOUND occurs. So, from v1.0.1, the library of BatchRequest is included in this library. You can see the detail information here https://github.
UnlockSmartInvoiceManagementWithGeminiAPI was updated to v1.0.1. v1.0.1 (June 17, 2024)
In order to easily customize the value of “jsonSchema” for generating content with Gemini API, I added it as a new sheet of “jsonSchema” sheet in the Spreadsheet. When you customize it, you can edit the cell “A1” of the “jsonSchema” sheet. By this, the script generates content with Gemini API using your customized JSON schema. The cell “A2” is the number of characters of “A1”.
GeminiWithFiles was updated to v1.0.6. v1.0.6 (June 15, 2024)
Included the script of PDFApp in this library. You can see the detail information here https://github.com/tanaikech/GeminiWithFiles
Gists You can see the presentation of this application at https://www.youtube.com/watch?v=Dc2WPQkovZE. Abstract This report describes an invoice processing application built with Google Apps Script. It leverages Gemini, a large language model, to automatically parse invoices received as email attachments and automates the process using time-driven triggers. Introduction The emergence of large language models (LLMs) like ChatGPT and Gemini has significantly impacted various aspects of our daily lives. One such example
GeminiWithFiles was updated to v1.0.5. v1.0.5 (June 7, 2024)
Spelling mistakes in the warning message were modified. The wait time for changing the value of state for the movie file is changed from 5 seconds to 10 seconds per cycle. You can see the detail information here https://github.com/tanaikech/GeminiWithFiles
Overview This is a Google Apps Script library for moving a folder including files and folders on Google Drive.
Description This library addresses a common challenge: efficiently moving folders, including their subfolders and files, between Google Drives. This encompasses both personal and shared drives using a script. While Google Drive offers straightforward methods for moving individual files between any drives, directly moving entire folders containing subfolders presents limitations, particularly when shared drives are involved.
Gists
Abstract This script automates moving a folder including files and folders between Google Drives (personal or shared) by recreating the folder structure and transferring files, overcoming limitations for shared drives.
Introduction This report addresses a common challenge: efficiently moving folders, including their subfolders and files, between Google Drives, encompassing both personal and shared drives using a script. While Google Drive offers straightforward methods for moving individual files between any drives, directly moving entire folders containing subfolders presents limitations, particularly when shared drives are involved.
FilesApp was updated to v1.1.4.
v1.1.4 (June 5, 2024)
A bug for retrieving metadata from the shared drive was removed. GitHub of FilesApp
My post was featured in the section “Community cuts” of “The overwhelmed person’s guide to Google Cloud: week of May 23”.
Gitst
Abstract This report builds on prior work using Gemini 1.0 Pro to expand Google Apps Script error messages. It highlights how the script’s execution time limit created a bottleneck, but the introduction of Gemini 1.5 Flash eliminates this issue.
Introduction After the release of the Gemini API, I previously reported on “Expanding Error Messages of Google Apps Script using Gemini Pro API with Google Apps Script”. Ref In that report, I utilized the Gemini 1.
GeminiWithFiles was updated to v1.0.4. v1.0.4 (May 29, 2024)
Recently, when model.countToken is used with the uploaded files, I confirmed that an error like You do not have permission to access the File ### or it may not exist. occurred. In order to handle this issue, I modified the library. In order to use the movie files for generateContent, I modified the library. Ref You can see the detail information here https://github.
Gists
Abstract Current Google Docs API documentation offers methods for working with existing lists but lacks instructions for creating nested lists in new documents. This report fills this gap, empowering developers and streamlining nested list creation using Google Apps Script (concepts applicable to other languages).
Introduction The official Google Docs API documentation provides instructions for working with existing lists, including converting paragraphs to lists and removing bullets. However, it lacks guidance on creating nested lists within an empty document body.
Gists
Abstract Despite the improved Google Apps Script IDE launched in April 2022, challenges arise for HTML and Javascript development. The Script Editor’s built-in formatting for these languages proves insufficient, particularly for large Javascript codebases, potentially causing errors. This report proposes a solution to address this formatting issue and streamline development within the Script Editor.
Introduction The Google Apps Script Integrated Development Environment (IDE) Script Editor received a significant update on April 13, 2022, transforming it into a more modern and streamlined development experience.
Gists
Abstract The Gemini API traditionally required specific prompts for desired output formats. This report explores two new GenerationConfig properties: “response_mime_type” and “response_schema”. These allow developers to directly specify formats like JSON, enhancing control and predictability. We analyze and compare the effectiveness of both properties for controlling Gemini API output formats.
Introduction One of the key challenges when working with the Gemini API is ensuring the output data is delivered in the format your application requires.
GeminiWithFiles was updated to v1.0.3. v1.0.3 (May 17, 2024)
Bugs were removed. You can see the detail information here https://github.com/tanaikech/GeminiWithFiles
ImgApp was updated to v1.3.3.
v1.3.3 (May 16, 2024) In SlidesAppp.gs, added a script for checking whether Drive API and Slides API are enabled. Ref You can see the detail information here https://github.com/tanaikech/ImgApp
Gists
Overview This script checks if the desired API is enabled or disabled in the Advanced Google Services section of Google Apps Script.
Introduction As of December 11, 2023, Drive API v3 became available for use in Advanced Google Services. Ref This means you can now choose between v2 and v3 in your scripts. However, when Drive API is enabled, version 3 is automatically selected. This caused compatibility issues with previously published libraries that relied on v2.
PDFApp was updated to v1.0.7. v1.0.7 (May 15, 2024)
The method of “addPageNumbers” was updated. Ref When a number is used to the property x instead of “left”, “center”, and “right”, the inputted number is directly used. You can see the detail information here https://github.com/tanaikech/PDFApp
PDFApp was updated to v1.0.6. v1.0.6 (May 15, 2024)
A new method of “addPageNumbers” was added. Ref This method adds the page numbers to each page of the PDF. You can see the detail information here https://github.com/tanaikech/PDFApp
Gists
Description This is a simple sample script for adding the page numbers to PDF data using Google Apps Script.
When you use this script, please copy and paste the following script to the script editor of Google Apps Script. And, please set the file ID of the PDF file.
Sample script In this script, pdf-lib is used.
/** * ### Description * Add page numbers to PDF. * * @param {Object} blob PDF blob.
Gists
Abstract This report examines leveraging Gemini 1.5 API with Google Apps Script to automate sample input creation during script reverse engineering. Traditionally, this process is manual and time-consuming, especially for functions with numerous test cases. Gemini 1.5 API’s potential to streamline development by automating input generation is explored through applying reverse engineering techniques to Google Apps Script samples.
Introduction With the release of Gemini 1.5 API, users gained the ability to process more complex data, opening doors for various application developments.
GeminiWithFiles was updated to v1.0.2. v1.0.2 (May 7, 2024)
For generating content, parts was added. From this version, you can select one of q, jsonSchema, and parts. From this version, systemInstruction can be used. In order to call the function call, toolConfig was added to the request body. You can see the detail information here https://github.com/tanaikech/GeminiWithFiles
UtlApp was updated to v1.0.6. v1.0.6 (May 4, 2024)
Methods of blobToDataUrl was added. When this method is used, the Blob data can be converted to the data URL. You can see the detail information here https://github.com/tanaikech/UtlApp
Gists
Overview This script demonstrates how to insert an animated GIF over cells in a Google Sheet using Google Apps Script.
Description I recently received a request to create a Google Apps Script for inserting animated GIFs into cells on a Google Sheet. I previously published a sample script on my blog on June 6, 2017. Ref In that script, the animation GIF was inserted using a public link. This new script leverages data URLs, which simplifies the process for using GIFs stored in Google Drive.
GeminiWithFiles was updated to v1.0.1. v1.0.1 (May 2, 2024)
response_mime_type got to be able to be used for controlling the output format. Ref You can see the detail information here https://github.com/tanaikech/GeminiWithFiles
Gists
Abstract This report explores controlling output formats for the Gemini API. Traditionally, prompts dictated the format. A new property, “response_mime_type”, allows specifying the format (e.g., JSON) directly. Testing confirms this property improves control over output format, especially for complex JSON schemas. The recommended approach is to combine a detailed JSON schema with “response_mime_type” for clear and consistent outputs.
Introduction One of the key challenges when working with the Gemini API is ensuring the output data is in the format your application requires.
Overview This is a Google Apps Script library for Gemini API with files.
A new Google Apps Script library called GeminiWithFiles simplifies using Gemini, a large language model, to process unstructured data like images and PDFs. GeminiWithFiles can upload files, generate content, and create descriptions from multiple images at once. This significantly reduces workload and expands possibilities for using Gemini.
Description Recently, Gemini, a large language model from Google AI, has brought new possibilities to various tasks by enabling the use of unstructured data as structured data.
Gists
Abstract A new Google Apps Script library, “GeminiWithFiles”, simplifies using the powerful Gemini 1.5 AI model. It lets users directly upload files for content generation or create descriptions for many images at once, making it much faster than prior methods. This is helpful for tasks involving large amounts of text or images.
Introduction Recently, Gemini, a family of Google’s most capable AI models, has revolutionized various tasks by allowing unstructured data to be used as structured data.
RichTextAssistant was updated to v1.0.1. v1.0.1 (April 22, 2024)
From oshliaer’s report, a bug was removed. In the current stage, when RichTextValueBuilder is used, it seems that when setTextStyle is used after setLinkUrl, the style of the hyperlink is removed while the link is kept. So, it is required to set setLinkUrl after setTextStyle. You can see the detail information here https://github.com/tanaikech/RichTextAssistant
UtlApp was updated to v1.0.5. v1.0.5 (April 16, 2024)
Methods of consolidateA1Notations was added. When this method is used, the scattered A1Notations can be consolidated. You can see the detail information here https://github.com/tanaikech/UtlApp
Gists
Abstract Consolidate scattered cell references (A1Notation) in Google Sheets for efficiency. This script helps select cells by background color or update values/formats, overcoming limitations of large range lists.
Introduction When working with Google Spreadsheets, there might be a scenario where you need to process scattered A1Notations (cell addresses in the format “A1”). This could involve selecting cells with specific background colors, updating cell values, or modifying cell formats.
One approach to handle scattered A1Notations is to create a range list containing the individual cell coordinates and activate it.
UtlApp was updated to v1.0.4. v1.0.4 (April 13, 2024)
Updated the methods of convGridRangeToA1Notation. When the sheet name is not given, only the A1Notation is returned. You can see the detail information here https://github.com/tanaikech/UtlApp
Gists
Abstract The Gemini API generates different outputs depending on the prompts. This report explains how to use function calling in the new Gemini 1.5 API to control the output format (string, number, etc.) within a script during a chat session. This allows for more flexibility in using the Gemini API’s results.
Introduction The appearance of Gemini has already brought a wave of innovation to various fields. When the Gemini API returns a response, the format of the response is highly dependent on the input text provided as a prompt.
Gists
Overview This Google Apps Script helps identify and analyze regions of colored cells in a Google Sheet.
Description Recently, I encountered a situation where I needed to identify colored cell regions in Google Sheets. For instance, consider the following spreadsheet:
The region enclosed by the red cells (B2:D4) is a rectangle. In this case, the closed region can be easily identified using a simple script in Google Sheets. However, the region enclosed by the blue cells (H3, I2, J2,,,) is more complex.
Gists
Abstract This report explores using Gemini, a new AI model, to parse invoices in Gmail attachments. Traditional text searching proved unreliable due to invoice format variations. Gemini’s capabilities can potentially overcome this inconsistency and improve invoice data extraction.
Introduction After Gemini, a large language model from Google AI, has been released, it has the potential to be used for modifying various situations, including information extraction from documents. In my specific case, I work with invoices in PDF format.
Gists
Description This script converts soft breaks to hard breaks in a Google Document using Google Apps Script.
Usage Follow these steps:
1. Create a New Google Document Create a new Google Document and open it. Go to “View” -> “Show non-printing characters” in the top menu to see line breaks in the document body (as shown in the image below).
2. Sample Script Copy and paste the following script into the script editor of your Google Document.
Gists
Abstract This report addresses the challenge of appending values to specific columns in Google Sheets when columns have uneven last rows. It offers a Google Apps Script solution with a sample script and demonstration image, enabling efficient and flexible data manipulation.
Introduction Google Apps Script is a versatile tool that allows for seamless management of various Google Workspace applications, including Docs, Sheets, Slides, Forms, and APIs. Its ability to automate tasks within Google Sheets is particularly powerful.
Gists
Abstract A new large language model (LLM) called Gemini with an API is now available, allowing developers to analyze vast amounts of data. This report explores trends in Google Apps Script by using the Gemini 1.5 API to analyze questions on Stack Overflow.
Introduction The release of the LLM model Gemini as an API on Vertex AI and Google AI Studio has opened a world of possibilities. Ref The Gemini API significantly expands the potential of various scripting languages, paving the way for diverse applications.
Gists
Abstract The Gemini API allows the generating of text from uploaded files using Google Apps Script. It expands the potential of various scripting languages for diverse applications.
Introduction With the release of the LLM model Gemini as an API on Vertex AI and Google AI Studio, a world of possibilities has opened up. Ref The Gemini API significantly expands the potential of various scripting languages and paves the way for diverse applications.
Gists
Abstract Gemini API on Vertex AI/Studio unlocks new applications with data retrieval and content generation through function calls. This report explores using the API for reverse engineering with a sample interpreter in Google Apps Script.
Introduction The recent release of the LLM model Gemini as an API on Vertex AI and Google AI Studio unlocks a vast potential for new applications and methodologies. It significantly expands capabilities across diverse situations, paving the way for groundbreaking applications.
Gists
Abstract This report presents a workaround for copying a sheet, including its charts, from Google Spreadsheet “A” to Google Spreadsheet “B” using Google Apps Script.
Description In this report, I would like to introduce a workaround for the following goal:
Copying a sheet including charts from Google Spreadsheet “A” to Google Spreadsheet “B” using a script. In general, when Sheets API is used, all objects of the Spreadsheet can be copied easily.
Overview This is a Google Apps Script library for supporting to use Google APIs with Google Apps Script.
Description There are numerous powerful Google APIs available today. Google Apps Script streamlines interacting with these APIs through a simplified authorization process. Additionally, advanced Google services integrate seamlessly with Apps Script, making Google APIs highly advantageous for users.
However, utilizing APIs beyond advanced Google services can be challenging for some users. Developing a simpler method for using various Google APIs would significantly increase their accessibility and empower a broader range of users to create diverse applications.
Gists
Abstract Google offers powerful APIs but using them (except advanced services) can be complex. A new, simpler method would benefit developers creating diverse applications. To address this, I built a Google Apps Script library simplifying API access.
Description There are numerous powerful Google APIs available today. Google Apps Script streamlines interacting with these APIs through a simplified authorization process. Additionally, advanced Google services integrate seamlessly with Apps Script, making Google APIs highly advantageous for users.
v1.2.3 (March 11, 2024)
Modified the URL of the application installed in Microsoft Azure.
GitHub of OnedriveApp
Gists
Abstract The Gemini API can now do semantic searches, going beyond content generation. This means it can understand the meaning of your search and provide better results, even if your words don’t exactly match the data. This report introduces the enhanced search capabilities of the Gemini API.
Introduction The Gemini API expands its potential beyond content generation to encompass powerful semantic search capabilities. Searching existing data is crucial in various situations.
CorporaApp was updated to v1.0.3. v1.0.3 (March 6, 2024)
New method of getChunk was added. When this method is used, you can retrieve a single chunk using the resource name of chunk. You can see the detail information here https://github.com/tanaikech/CorporaApp
Gists
Abstract Google Apps Script automates tasks like managing protections in Google Spreadsheets. These protections control user access to specific cells. While scripts exist for this purpose, users still encounter challenges, prompting this report. The report aims to introduce techniques for managing protections using sample scripts, helping users understand and implement this functionality.
Introduction Google Apps Script is a powerful tool that enables seamless management of Google Documents, Spreadsheets, Slides, Forms, APIs, and more.
Gists
Abstract The Gemini API enables both content generation and semantic search, managing data effectively. This report introduces a Gemini-powered similarity viewer for easy visualization of complex text similarity scores, using Google Spreadsheet and Apps Script.
Introduction The Gemini API unlocks new possibilities, extending its capabilities beyond content generation to encompass semantic search. Within this context, the API excels at efficiently managing data within corpora. While semantic search provides valuable similarity scores (chunkRelevanceScore) for text pairs, interpreting these numerical values can be cumbersome.
CorporaApp was updated to v1.0.2. v1.0.2 (February 26, 2024)
New method of setAccessToken was added. When this method is used, you can use the access token retrieved from the service account. Default access token is retrieved by ScriptApp.getOAuthToken(). You can see the detail information here https://github.com/tanaikech/CorporaApp
Gists
Updated on April 26 From this X and this blog, it seems that in the current stage, this situation can be resolved using Sheets API. The sample script of Google Apps Script is as follows. Before you test this, please enable Sheets API at Advanced Google services.
function myFunction() { // Please set your Spreadsheet ID. const spreadsheetId = "###"; const requests = [{ updateSpreadsheetProperties: { properties: { importFunctionsExternalUrlAccessAllowed: true }, fields: "importFunctionsExternalUrlAccessAllowed" } }]; Sheets.
Gists
Abstract New Gemini API opens doors for developers to integrate its AI power into apps, potentially impacting education, healthcare, and business. The latest Gemini 1.5 brings even more features. This report showcases an image bot using Gemini as one example of its diverse applications. Showcasing its diverse application potential across various fields.
Introduction The recent release of Gemini as an accessible API on Vertex AI and Google AI Studio empowers developers to integrate its vast capabilities into their applications, potentially revolutionizing fields like education, healthcare, and business.
Gists
Abstract Powerful AI tool Gemini’s API release (Vertex AI & Google AI Studio) opens doors for diverse applications. Its recent upgrade to version 1.5 boosts capabilities. This report demonstrates using simple Google Apps Script function calls to leverage Gemini’s power for both data retrieval and content generation.
Introduction The recent release of the LLM model Gemini as an API on Vertex AI and Google AI Studio unlocks a world of possibilities.
CorporaApp was updated to v1.0.1. v1.0.1 (February 16, 2024)
New method of searchQueryWithGenerateAnswer was added. You can see the detail information here https://github.com/tanaikech/CorporaApp
Gists
Abstract New “semantic search” features in Gemini API help find desired information within its corpora. While using these features with Google Apps Script was complex, a new library simplifies the process. This report proposes using this library with Gemini-generated content to automate template processes in Google Docs and Slides, creating a more flexible workflow.
Introduction The semantic search opens up a new wind for finding the expected values. Recently, the APIs for managing corpora have been added to Gemini API.
Gists
This is a sample script for converting a contact URL of “person/c###” to a resource name “people/c###” for People API using Google Apps Script.
When you open each contact at Contacts with your browser, you can see the URL like https://contacts.google.com/person/c###################.
Here, you might have a situation where you are required to retrieve the resource name for People API from this URL. But, in the current stage, person/c################### cannot be directly used as the resource name people/c###################.
PDFApp was updated to v1.0.5. v1.0.5 (February 5, 2024)
A new method of “splitPDF” was added. Ref This method splits each page of a PDF to an individual PDF file. You can see the detail information here https://github.com/tanaikech/PDFApp
PDFApp was updated to v1.0.4. v1.0.4 (February 5, 2024)
From this discussion, I changed the logic of copyPages. You can see the detail information here https://github.com/tanaikech/PDFApp
UtlApp was updated to v1.0.3. v1.0.3 (February 5, 2024)
Methods of addQueryParameters was updated. You can see the detail information here https://github.com/tanaikech/UtlApp
Gists
Description In the current stage, v1beta of Gemini API can use the corpora. Ref When the corpora are used, the values can be searched with the semantic search. In the current stage, 5 corpora can be created in a single project. And, each corpus can have 10,000 documents and 1,000,000 chunks. In this report, I would like to introduce a method for achieving the semantic search using the corpora with Google Apps Script.
DocsServiceApp was updated to v1.2.2 v1.2.2 (January 30, 2024)
Remove a bug in ExcelApp. When the inserted image had no data, an error occurred. This issue was removed. You can see the detail information here https://github.com/tanaikech/DocsServiceApp
Gists
Description I have published “Flexible Labeling for Gmail using Gemini Pro API with Google Apps Script” on December 19, 2023. Today, I published “Categorization using Gemini Pro API with Google Apps Script”.
In this report, as part 2, I would like to introduce 2 sample scripts for flexible labeling for Gmail using the semantic search and the function calling of Gemini Pro API with Google Apps Script.
Usage In order to test this script, please do the following flow.
Gists
Abstract This report explores using the Gemini Pro API with Google Apps Script to achieve flexible data categorization.
Introduction The recent release of the LLM model Gemini as an API on Vertex AI and Google AI Studio opens a world of possibilities. Ref and Ref I believe Gemini API significantly expands the potential of Google Apps Script and paves the way for diverse applications. In this report, I present the flexible categorization of data using Gemini Pro API with Google Apps Script.
UtlApp was updated to v1.0.2. v1.0.2 (January 25, 2024)
2 methods of dotProduct and cosineSimilarity were added. You can see the detail information here https://github.com/tanaikech/UtlApp
Gists
Abstract Gemini API unlocks semantic search for Google Apps Script, boosting its power beyond automation. This report explores the result of attempting the semantic search using Gemini Pro API with Google Apps Script.
Introduction The recent release of the LLM model Gemini as an API on Vertex AI and Google AI Studio opens a world of possibilities. Ref and Ref I believe Gemini API significantly expands the potential of Google Apps Script and paves the way for diverse applications.
Gists
Abstract Google Apps Script automates tasks (even offline) and builds web apps using spreadsheets as databases. This report presents a basic dining reservation system to illustrate key aspects of web app development with Apps Script, HTML, and Javascript.
Introduction Google Apps Script is one of the powerful automation tools for achieving the automation process. When Google Apps Script can be used for the situation, even when users are away from their computers, automation can continue thanks to cloud computing.
Gists
Abstract Google Apps Script is one of the strong tools with cloud computing, and it is very useful for various situations. Google Apps Script can be run by various methods. This report introduces how to execute Google Apps Script.
Recently, generative AI has given a lot of new users a chance to use Google Apps Script. If this report helps the users develop applications using Google Apps Script, I’m glad.
Gists
Published: January 6, 2024
Kanshi Tanaike
Introduction At Stackoverflow, a lot of people post questions and answers to the questions every day. There are various tags in Stackoverflow. A lot of discussions are performed at each tag. Their discussions bring important information and are very useful for a lot of people. As one of the tags, there is “google-apps-script”. I sometimes discuss the questions with that tag. When we see the discussions, we notice that the discussions have changed and progressed over time, because “Google Apps Script” which is the origin of the tag is updated.
Gists
Description Recently, Microsoft Docs files (Word, Excel, and PowerPoint files) could be manually edited by opening it on Google Drive using the browser. This is good news for a lot of users. With this situation, the URLs of Microsoft Docs files were changed. For example, when a Microsoft Word file (DOCX) is opened on Google Drive with the browser, the URL is https://docs.google.com/document/d/###/edit. ### of this URL is the file ID of the DOCX file on Google Drive.
Overview This is a Google Apps Script library for using Microsoft Docs files (Word, Excel, and PowerPoint files) using Document service, Spreadsheet service, and Slides Service of Google Apps Script.
Description Recently, Microsoft Docs files (Word, Excel, and PowerPoint files) could be manually edited by opening it on Google Drive using the browser. This is good news for a lot of users. With this situation, the URLs of Microsoft Docs files were changed.
Gists
Description On December 7, 2020, new IDE has been released. Ref This has been helping users develop various applications using Google Apps Script with the new IDE very much. When it develops scripts and applications using Google Apps Script with the new IDE, it is considered that the existence of JSDoc in the script will be helpful for both the developers and the client users, because it is reflected in the auto-completion.
Gists
Description When the generated text can be automatically inserted into the cursor position of Google Document, Google Spreadsheet, and Google Slide, it will be useful for users. This report introduces sample scripts for achieving this.
Sample scripts Here, I would like to introduce 3 sample scripts for a Google Document, a Google Spreadsheet, and a Google Slide.
Create an API key These sample scripts request Gemini Pro API using an API key.
Gists
Abstract It is considered that when the current error message of Google Apps Script is expanded, it will be useful for a lot of users. This report introduces a sample script for expanding the error message of Google Apps Script using Gemini Pro API with Google Apps Script.
Introduction The recent release of the LLM model Gemini as an API on Vertex AI and Google AI Studio opens a world of possibilities.
Gists
Abstract The release of Gemini API is expected to expand the future of Google Apps Script. This report introduces a sample script for flexible email labeling in Gmail using Gemini API with Google Apps Script.
Introduction The recent release of the LLM model Gemini as an API on Vertex AI and Google AI Studio opens a world of possibilities. Ref and Ref I believe Gemini API significantly expands the potential of Google Apps Script and paves the way for diverse applications.
Gists
Abstract Gemini LLM, now a Vertex AI/Studio API, unlocks easy document summarization and image analysis via Google Apps Script. This report details an example script for automatically creating the description of the files on Google Drive and highlights seamless integration options with API keys.
Introduction Recently, the LLM model Gemini has been released and is now available as an API on Vertex AI and Google AI Studio. Ref and Ref This report presents a simple Google Apps Script example for automatically creating descriptions of files on Google Drive using the Gemini Pro API.
Recently, when Drive API is enabled at Advanced Google services with the script editor, Drive API v3 is automatically used. In the official document, Drive API v3 has already been used. Ref In the current stage, the users can select V3 and V2.
When Drive API v3 is used with Advanced Google services, the following advantages can be considered.
Drive API v3 can be used with only the scopes for Drive API without adding a scope https://www.
Gists
Description Google Documents can be managed by the Document service of Google Apps Script. Ref One day, you might have a situation in which you are required to manage the footnotes on Google Documents using Google Apps Script. There are several official documents related to the footnotes for Google Apps Script. Ref and Ref However, unfortunately, I’m worried that it might be difficult a little to understand the management of the footnotes from these documents.
Gists
Abstract One day, you might have a situation where you are required to manage rich texts in Google Spreadsheet using Google Apps Script. In this report, I would like to introduce the basic technique for managing rich texts with Google Apps Script.
Introduction At Google Spreadsheet, rich texts can be used as a cell value. The rich texts can be also managed by Google Apps Script. Ref When I saw the official document related to the rich texts with Google Apps Script, I’m worried that it might be difficult a little for users to manage the rich texts using Google Apps Script.
PDFApp was updated to v1.0.3. v1.0.3 (November 26, 2023)
From this discussion, I changed the logic of the method mergePDFs. The method for using mergePDFs and the output are not changed. With this modification, the large PDF data can be merged. You can see the detail information here https://github.com/tanaikech/PDFApp
Gists
Abstract One day, you might have a situation where you are required to make users edit the protected cells using Google Apps Script. This report introduces a workaround for achieving this situation. The key factors for achieving this are as follows. 1. Run the script as the owner of Spreadsheet even when the script is run by a user. 2. In order to run the script as the owner, the installable triggers and the Web Apps are used.
Gists
Description At Google Spreadsheet, custom functions created by Google Apps Script can be used. Ref When the custom function is used, the users can create a function for expanding the built-in functions for Spreadsheet. One day, you might have a situation for executing the custom function using an array. For example, under the situation that your custom function uses a single value instead of an array as the argument, when you want to use an array for each argument of the custom function, it is required to modify the original script of the custom function or the function calling the custom function.
Gists
Description At Google Spreadsheet, the cells can be merged as one cell. But, when the Spreadsheet including the merged cells is used with Google Apps Script, the script becomes a bit complicated. Also, I sometimes find some questions like this situation on Stackoverflow. In this report, I would like to introduce a technique for easily using the Spreadsheet including the merged cells with Google Apps Script.
Principle Before it introduces the sample scripts, I would like to introduce the principle for using the Spreadsheet for the merged cells.
Gists
Abstract One day, you might have a situation where you are required to make the shared users upload a file and text using a dialog or sidebar on Google Spreadsheet to your Google Drive and Spreadsheet without authorization by the users. This report introduces a solution for achieving this situation.
Introduction Google Spreadsheet can run Javascript on a dialog and a sidebar. Ref These can be used as a strong tool for working on Spreadsheet.
Gists
Abstract One day, you might have a situation where you are required to update a sheet using Google Apps Script when the cell values retrieved by IMPORTRANGE are changed. This report introduces a workaround for achieving this situation.
Introduction Google Apps Script can be executed by several triggers. Ref When a cell in a Google Spreadsheet is manually edited, a function of Google Apps Script can be executed by detecting this edit.
Gists
Abstract This report introduces the method for retrieving the Emoji reactions from the comments in Google Docs files (Google Documents, Google Slides, and Google Spreadsheets) using Google Apps Script.
Introduction Recently, the Emoji reactions have been implemented in the comments on Google Docs files (Google Documents, Google Slides, and Google Spreadsheets). Ref With this implementation, the collaborative work has been higher. Here, it is considered that when the Emoji reactions can be retrieved from the Google Docs files, the statistics of the reactions will be also more useful for increasing collaboration.
Gists
Abstract One day, you might have a situation where you are required to create a Web Apps with Google Apps Script and are required to load another HTML created by Javascript on the Web Apps. This report will help achieve such a situation.
Introduction Google Apps Script can create Web Apps. Ref When you access the Web Apps using your browser, you can see the HTML. When your browser can run Javascript, you can see the HTML reflecting the Javascript.
Gists
Abstract One day, you might have a situation where it is required to run Google Apps Script using the service account. Unfortunately, in the current stage, Google Apps Script cannot be directly run with the service account because of the current specification. So, this report introduces a workaround for executing Google Apps Script using the service account.
Introduction When you want to execute Google Apps Script from outside of Google, as the basic approach, it can be achieved by Google Apps Script API.
HtmlFormApp was updated to v1.0.2. v1.0.2 (October 17, 2023)
The 2nd argument row of appendFormData(object, row) was added. This is from this suggestion. When row is used, the value is put into the specific row of the Spreadsheet. In this case, please set the value of row more than 1.
In this case, the submitted row can be forcefully put into the specific row of Google Spreadsheet.
Gists
Abstract This report introduces the method for managing the histories of the Google Apps Script project.
Description On August 23, 2023, the project history has been implemented in the new IDE of Google Apps Script. Ref and Ref In the current stage, the users can see the history of the previously deployed script version. This is a very important implementation for a lot of developers.
Here, I remember the classic IDE.
Overview This is a Google Apps Script library for managing the histories of the Google Apps Script project.
Description On August 23, 2023, the project history has been implemented in the new IDE of Google Apps Script. Ref and Ref In the current stage, the users can see the history of the previously deployed script version. This is a very important implementation for a lot of developers.
Here, I remember the classic IDE.
Gists
Introduction This is a report for taking advantage of the auto-completion of the script editor for Google Apps Script.
In the current stage, the auto-completion is implemented in the script editor of Google Apps Script. This auto-completion can be used for not only the built-in classes and methods but also the methods for Javascript. This helps develop scripts and applications very much. In the case of the built-in classes and methods and the methods for Javascript, you can see the detailed specifications of the documents like the official documents and developer.
Gists
This is a sample script for copying the date object between Google Spreadsheets with the different time zones using Google Apps Script.
One day, you might have a situation in which it is required to copy the date object between Google Spreadsheets with the different time zones using Google Apps Script. In this post, I would like to introduce the sample scripts for achieving this.
Sample situation Here, the sample situation is declared.
Gists
This is a sample script for moving the selected cell to the top left on Google Spreadsheet to focus it using Google Apps Script.
One day, there might be a case where you are required to focus the specific cell on Google Spreadsheet to help edit cells. In this post, I would like to introduce a sample script for achieving this.
Sample script Please copy and paste the following script to the script editor of Google Spreadsheet, and save the script.
Gists At the Google Apps Script project, the values can be transferred from HTML to Google Apps Script using google.script.run with Javascript. In this case, unfortunately, the values of all types cannot be transferred. In the official document, it says as follows. Ref Most types are legal, but not Date, Function, or DOM element besides form; see description Legal parameters are JavaScript primitives like a Number, Boolean, String, or null,
PDFApp was updated to v1.0.2. v1.0.2 (August 21, 2023)
A new method of insertHeaderFooter was added. Ref When this method is used, the custom header and footer can be added when a Google Spreadsheet is exported as PDF. You can see the detail information here https://github.com/tanaikech/PDFApp
Gists
This is a sample script for adding header and footer to PDF using Google Apps Script.
In the current stage, when Google Spreadsheet is manually exported as a PDF file at “Print settings” on the UI of Spreadsheet, the custom header and footer can be added as shown in the following image.
But, unfortunately, in the current stage, this cannot be directly achieved by Google Apps Script. So, I created this sample script.
PDFApp was updated to v1.0.1. v1.0.1 (August 18, 2023)
About the method of “getMetadata”, pageInfo is added to the retrieved metadata. By this, each page size can be obtained. You can see the detail information here https://github.com/tanaikech/PDFApp
TriggerApp was updated to v1.0.1. v1.0.1 (August 18, 2023)
When toDay is not used, there was a case that the next trigger is not installed. This bug was removed. You can see the detail information here https://github.com/tanaikech/TriggerApp
Overview This is a Google Apps Script library for managing PDFs.
Description Google Apps Script is one of the most powerful tools for cloud computing. When Google Apps Script is used, the result can be obtained even when the user doesn’t stay in front of the PC and mobile phone by the triggers. One day, there might be a case where it is required to manage PDF data using Google Apps Script.
Gists
Abstract When PDF file can be managed with Google Apps Script, that will lead to the automation process on cloud. In this report, the method for cooking PDF over Google Apps Script.
Introduction Google Apps Script is one of the strong tools for achieving the automation process. When Google Apps Script can be used for the situation, it can be processed with cloud computing. By this, the users are not required to stay on the desks with the PC.
Gists
Description In this report, I would like to introduce a sample script for efficiently deleting rows by conditions on Google Spreadsheet using Google Apps Script. Recently, I had a situation for being required to achieve this situation. In my report, it has already known that when Sheets API is used, the rows can be efficiently deleted by a condition. Ref However, in that case, Sheets API couldn’t be used. Under this situation, I came up with a method.
My report “Easily Managing Time-Driven Triggers Using Google Apps Script” has been featured in “Community Spotlight” of Google Workspace Developer Newsletter on July 2023
https://developers.google.com/workspace/newsletters#expandable-2
Gists
This is a sample script for embedding the objects in PDF using Google Apps Script.
Recently, I had a situation where it is required to manage PDFs using Google Apps Script. At that time, I had a situation where it is required to embed objects of texts and images in PDF using Google Apps Script. So, I created the following Class with Google Apps Script. When this Class is used, the objects of texts and images can embed in PDF.
Gists This is a sample script for creating PDF forms from a Google Slide template using Google Apps Script. Recently, I had a situation where it is required to create a custom PDF form. In that case, I thought that when a PDF form can be created from a template, it might be useful. So, I created the following Class with Google Apps Script. When this Class is used, a
Gists This is a sample script for retrieving and putting values for PDF Forms using Google Apps Script. PDF can have the PDF Form for inputting the values in the PDF by the user. Ref Recently, I had a situation that required me to retrieve and put the values to the PDF Form using Google Apps Script. In order to achieve this, I created a Class object with Google Apps
v1.2.2 (July 27, 2023)
Checked the array of to, cc, and bcc for the sendEmails method.
GitHub of OnedriveApp
Gists
This is a sample script for changing the order of pages in a PDF file using Google Apps Script.
Sample script Before you run this script, please set the variables in the function main.
/** * ### Description * Changing order of pages in a PDF file. * * @param {Object} fileId is file ID of PDF file. newOrderOfpages is new order of pages. About "ignoreSkippedPages", if this is false, when the PDF has 5 pages and "newOrderOfpages" is "[3, 2]", the exported PDF file has 5 pages of 3, 2, 1, 4, 5.
v1.2.1 (July 26, 2023)
A bug of “Send Email messages” was removed.
GitHub of OnedriveApp
Gists
Here, I would like to introduce a report for understanding the flow of the request to Web Apps created by Google Apps Script. There might be a case that various applications using the Web Apps are created and the Web Apps are used as the webhook. In that case, it is considered that when you have understood the flow of requests to the Web Apps, your goal might be able to be smoothly achieved.
Gists
This is a sample script for managing the metadata of PDF data using Google Apps Script.
There might be a case in that you want to retrieve and update the metadata of PDF data using Google Apps Script. In this post, I would like to introduce achieving this.
Class ManagePdfMetadata This is a Class ManagePdfMetadata. This Class is used for managing the metadata of PDF files using Google Apps Script.
Gists
This is a sample script for checking the overwrapped cells of multiple ranges on Google Spreadsheet using Google Apps Script.
When applications are developed, there might be a case that it is required to confirm whether 2 ranges on Google Spreadsheet are overwrapped. In this post, I would like to introduce a sample script for achieving this.
Method: getOverwrappedCells The following script is a method of getOverwrappedCells. This is the main script of this post.
Gists
This is a sample script for automatically refreshing the basic filter on Google Spreadsheet using Google Apps Script.
Description A sample situation is as follows.
In this sample, the basic filter is set to columns “B” and “D”.
Column “B”: When the checkbox is checked, the row is hidden. Column “D”: When the cell value is multiples of 3, the row is hidden. In this case, the custom function =MOD(E2,3)<>0 is used.
Overview This is a Google Apps Script library for efficiently managing the time-driven triggers for executing Google Apps Script using Google Apps Script.
Description Google Apps Script can execute with not only the manual operation but also several triggers. The time-driven trigger is one of them, and this is one of a lot of important functions. When the time-driven trigger is used, Google Apps Script can be automatically executed at the time you set without launching the user’s PC.
Gists
This is a sample script for exporting the specific pages from a PDF as a new PDF using Google Apps Script.
In this sample script, pdf-lib is used. In the current stage, it seems that this Javascript can be directly used with Google Apps Script.
Sample script async function myFunction() { // Retrieve PDF data. const fileId = "###"; // Please set a file ID of your a PDF file or a file ID of Google Docs files (Document, Spreadsheet, Slide).
Gists
This is a sample script for converting various images to PNG Format and JPEG format using Google Apps Script.
The flow of this sample script is as follows.
Convert the file to PNG format by the thumbnail link. Convert PNG format to JPEG format if outputFormat is “JPEG”. Create the image data in the JPEG format in the root folder as a file. Sample script Please set your file ID and output format.
Gists
This is a sample script for copying a Google Spreadsheet including a container-bound script by removing the container-bound script using Google Apps Script.
When you want to copy a Google Google Spreadsheet including a container-bound script by removing the container-bound script using Google Apps Script, this could be achieved by using “get” and “create” methods of Sheets API before. The sample script is as follows.
const obj = Sheets.Spreadsheets.get( SpreadsheetApp.
Gists
This is a sample script for notifying the new release of Google APIs and Google Apps Script with an email using Google Apps Script.
Recently, I published a sample script of “Retrieving Release Notes of Google Apps Script and Google APIs from RSS using Google Apps Script”. After this was published, I got an email that it wants to automatically notice the new release of Google APIs and Google Apps Script with an email.
Gists
This is a sample script for managing the row height and the column width of a table on Google Slides using Google Apps Script.
In the current stage, Google Slides service (SlidesApp) cannot manage the row height and the column width of the table on Google Slides, while the table width and height can be managed. But, fortunately, when Google Slides API is used, this can be achieved.
In this post, I would like to introduce a sample script for managing the row height and the column width of a table on Google Slides using Google Apps Script.
Gists
This is a sample script for converting the relative reference to the absolute reference and vice versa of A1Notation on Google Spreadsheet using Google Apps Script.
Description A1Notation is used in the cells on Google Spreadsheet.
As the 1st sample, it supposes that a formula of =A1 is put into a cell “B1”. Under this condition, when the cell “B1” is copied to “B2” and “C1”, the cells “B2” and “C1” have the formulas of =A2 and =B1, respectively.
Gists
This is a sample script for comparing the file contents of files on Google Drive using Google Apps Script.
Sample script Before you use this script, please enable Drive API at Advanced Google services. And also, please set the file IDs you want to check whether the file contents of the files are the same.
function checkFiles_(f, checks = ["md5Checksum", "sha1Checksum", "sha256Checksum"]) { files = f.map(id => DriveApp.getFileById(id)); const fields = [.
Gists
This is a sample script for exporting Google Documents as HTML with the image hyperlinks using Google Apps Script.
Recently, it seems that the specification for exporting Google Documents as HTML data has been changed. When a Google Document are exported as HTML data before, the images in the Google Document were the image hyperlinks, which are publicly shared. But, in the current stage, when a Google Document is exported as HTML data, the images in the Google Document are the data URL (base64 data) of the images.
Gists
This is a sample script for retrieving the release notes of Google Apps Script and Google APIs from RSS using Google Apps Script.
Recently, the release notes of Google Apps Script and Google APIs have been published as RSS. By this, the data got to be able to be easily retrieved using XmlService of Google Apps Script. Knowing the latest release notes will be useful for developing the applications. So, I would like to introduce the sample script for retrieving this information.
Gists
This is a sample script for inserting the paragraphs with the checkboxes in Google Documents using Google Apps Script.
In the current stage, Google Documents can create paragraphs with checkboxes as the paragraph bullet. But, unfortunately, this cannot be created by the Google Document service (DocumentApp). Fortunately, it seems that this got to be able to be achieved by Google Docs API. In this post, I would like to introduce a sample script for this.
Overview This is a Google Apps Script library for easily managing the template of Google Documents and Google Slides using Google Spreadsheet as a database using Google Apps Script.
Description You might have situations where are required to create multiple Google Documents and Google Slides from the templates using Google Spreadsheet as a database with Google Apps Script. When the simple texts are replaced with the placeholders on the templates, this can be achieved by a simple script.
My report has been published at Champion Innovators Content Library and Google Cloud Medium publication.
Easily Implementing HTML Form with Google Spreadsheet as Database using Google Apps Script If these reports will be useful, I’m glad.
Gists
Abstract Recently, I felt a change like never before in the questions on Stackoverflow. In order to confirm this, in this report, the trend of “google-apps-script” tag on Stackoverflow in the first half (January 1st to June 1st) of 2023 has been investigated. From this report, in 2023 when the affection of COVID-19 has been reduced socially, the appreciable trend was confirmed to the questions including a tag of “google-apps-script”.
Gists
Abstract This report introduces the method for easily implementing HTML forms with a Google Spreadsheet as a database using Google Apps Script. There are 2 patterns for the HTML form using Google Apps Script. One is that an HTML form is put into the same Google Apps Script project. Another is that an HTML form is put to a different server from a Google Apps Script project. In this report, the methods for easily implementing both patterns are introduced using the sample scripts.
HtmlFormApp was updated to v1.0.1. v1.0.1 (May 29, 2023)
When multiple files are uploaded, each URL was set as the hyperlink. You can see the detail information here https://github.com/tanaikech/HtmlFormApp
Gists
Abstract In this report, the detailed specification of PropertiesService has been investigated. It is considered that knowing this specification will be useful for developing applications with Google Apps Script. As a result, it was found that the maximum key and value sizes are 524,287 bytes with a 1-byte key and 8,066 bytes, respectively. And also, it was found that the maximum size of PropertiesService is required to be considered with both the key and value sizes.
My report has been published at Champion Innovators Content Library and Google Cloud Medium publication.
Exporting Binary Data with Batch Requests using Google Apps Script If these reports will be useful, I’m glad.
Gists
This is a sample script for converting Google Spreadsheet to an HTML table using Google Apps Script.
There is the case that it is required to convert a sheet in a Google Spreadsheet to an HTML table. For example, there might be a situation that a sheet in a Google Spreadsheet is sent as an email including an HTML table. And, there might be a situation in which a sheet in a Google Spreadsheet is published to an HTML page including the converted HTML table.
My report has been published at Champion Innovators Content Library and Google Cloud Medium publication.
Best Practices for Discontinuous Cells on Google Spreadsheet by Google Apps Script If these reports will be useful, I’m glad.
Overview This is a Google Apps Script library including useful scripts for supporting to development of applications by Google Apps Script. In the current stage, the 3 categories “For array processing”, “For binary processing”, and “For string processing” are included in this library.
Description When I create applications using Google Apps Script, there are useful scripts for often use. At that time, I thought that when those scripts can be simply used, they will be useful not only to me but also to other users.
Gists
This is a sample script for converting the values on Google Spreadsheet as unpivot (reverse pivot) using Google Apps Script.
Sample script 1: const SAMPLE1 = ([[, ...header], ...values]) => header.flatMap((h, i) => values.map((t) => [h, t[0], t[i + 1]])); In the sample, the source data is “A1:D8”. And, a custom function of =SAMPLE1(A1:D8) is put into “F1”. When this script is used, the result showing the above image (most left table to middle table) is obtained.
Gists
Abstract It has already been known that Google Apps Script is a strong tool for managing Google Spreadsheets. When the values are retrieved and/or put for Google Spreadsheet, there is a case that the discontinuous cells are required to be used. This report suggests the Best Practices for processing the discontinuous cells on Google Spreadsheet. From the results of process costs, it could understand the usefulness of using the discontinuous cells with low cost using Sheets API and Class RangeList of Spreadsheet service with Google Apps Script.
My report has been published at Champion Innovators Content Library.
Enriched Management of Rich Text on Google Spreadsheet using Google Apps Script If these reports will be useful, I’m glad.
Gists
These are sample scripts for putting an image into a cell of a Spreadsheet using Google Apps Script.
Sample 1 In this sample, the image is put into a cell using thumbnailLink retrieved by Drive API. So, when you test this, please enable Drive API at Advanced Google services. The image is put into cell “A1”.
function sample1() { const fileId = "###"; // Please set the file ID of the PNG image file on Google Drive.
Gists
This is a workaround for automatically installing the OnEdit trigger to the copied Google Spreadsheet using Google Apps Script.
The sample situation for this workaround is as follows.
You have a Google Spreadsheet. Your Spreadsheet is shared with a user as the writer. Your Spreadsheet has a button for executing a script for copying the active Spreadsheet. Your Spreadsheet has a function installedOnEdit for executing by the installable OnEdit trigger.
Gists
In this post, I would like to introduce a sample script for putting Time-based One-time Password (TOTP) value into Google Spreadsheet using Google Apps Script.
In this sample script, I used a Javascript library of https://github.com/hectorm/otpauth . In the current stage, Google Apps Script can run with V8 runtime. By this, it seems that this library can be used with Google Apps Script.
Sample script function myFunction() { const secret = "ABCDEFGHIJKLMN23"; // Please set your secret here.
Gists
In the current stage, Google Spreadsheet can use rich texts in cells. The rich texts can be also managed by Google Apps Script. But, I thought that creating a script for editing the existing rich text in the cell might be a bit complicated. Because, for example, in the current stage, when the text of the rich text of a cell is changed using a script, all text styles are cleared.
Overview This is a GAS library for supporting editing RichText in Google Spreadsheet using Google Apps Script.
Description There is RichTextApp in my published libraries. RichTextApp can be used mainly for converting RichText to Google Documents and vice versa. This library RichTextAssistant will support editing the rich text in Google Spreadsheets using Google Apps Script. Google Spreadsheet can use rich text as the cell value using Google Apps Script. But, I thought that when I created a script for editing the existing rich text in the cell, it might be a bit complicated.
Gists
This is a sample script for the folder picker using jsTree with Google Apps Script and Javascript.
I have already published “File Picker using Google Apps Script and Javascript without 3rd party”. In this post, jsTree is used.
Usage 1. Install Google Apps Script library. In this script, “FilesApp” of my Google Apps Script library is used. So, please install it. You can see how to install it at here.
Gists
When a value is searched from the 1-dimensional array and a 2-dimensional array, after V8 runtime could be used, I use JSON object, Set object, and Map Object. But, I had never measured the process cost of this situation. In this post, I would like to introduce the process cost for searching a value using a JSON object, Set object, and Map object converted from the 1-dimensional array and 2-dimensional array.
Gists
This is a sample script for importing Microsoft Excel (XLSX) data to Google Spreadsheet using a custom function with Google Apps Script.
Usage 1. Install SheetJS library. Please copy the script of the SheetJS library from https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js, and paste the script to the script editor of Google Spreadsheet, and save the script.
In this case, I would like to recommend the following flow.
Add a new script to the script editor.
Gists
Updated on July 8, 2023
In the current stage, unfortunately, the built-in methods of Google Apps Script cannot directly retrieve the values from the XLSX data. From this situation, I have created DocsServiceApp. When this Google Apps Script library is used, the values are directly retrieved from XLSX data by parsing XML data of XLSX data.
Here, as another approach, I would like to introduce a sample script for directly retrieving the values from XLSX data using SheetJS with Google Apps Script.
Gists
In Google Spreadsheet, when a single quote is added to the top letter of the cell value, the cell is used as the text value. About detecting this, I have already reported in this post in my blog. In this post, I would like to introduce a sample script for removing the single quote at the top character of the cell value.
Sample script: function sample() { const sheetName = "Sheet1"; // Please set your sheet name.
Gists
This is a sample script for splitting the binary data with search data using Google Apps Script.
Sample script /** * Split byteArray by a search data. * @param {Array} baseData Input byteArray of base data. * @param {Array} searchData Input byteArray of search data using split. * @return {Array} An array including byteArray. */ function splitByteArrayBySearchData_(baseData, searchData) { if (!Array.isArray(baseData) || !Array.isArray(searchData)) { throw new Error("Please give byte array.
Gists
This is a sample script for exporting Google Docs files (Spreadsheets, Documents, and so on) in PDF format with batch requests using Google Apps Script.
I have published a report “Efficient File Management using Batch Requests with Google Apps Script”. In this report, I mentioned how to use the batch requests using Google Apps Script.
In this post, I would like to introduce the method for retrieving binary data using this method.
BatchRequest was updated to v1.2.1. v1.2.1 (March 8, 2023)
An option of exportDataAsBlob was added to the request object to the method of EDo(). Ref When this option is used, the response values from the batch requests are returned as Blob. By this, for example, when you export Google Spreadsheet as PDF data using the batch requests, the PDF data can be retrieved as Blob. Sample script using exportDataAsBlob In this sample, the Spreadsheet and Document files are exported as PDF format using the batch requests.
Gists
This is a sample script for retrieving the total file sizes in the specific folder of Google Drive using Google Apps Script.
There is a case where you want to retrieve the total file sizes in the specific folder of Google Drive using Google Apps Script. In this post, I would like to introduce a sample script for achieving this.
Sample script Before you use this script, please enable Drive API at Advanced Google services.
Gists
This is a sample script for retrieving the start and end row numbers of the same values in a column on Google Spreadsheet using Google Apps Script.
There is a case in that I want to retrieve the rows of the same values in a column on Google Spreadsheet using Google Apps Script. In this post, I would like to introduce a simple sample script for achieving this.
Sample script function myFunction() { const sheet = SpreadsheetApp.
Gists
Today, I discussed with Riël Notermans an issue with the HTML form with the input tab of type="file" with google.script.run. Through this discussion, the reason for this issue could be found. When you use the input tab of type="file" in the HTML form, and you want to send the file content with google.script.run, I thought that this post might be useful for other users. So, I posted it here.
Gists
This sample script decrypts the salted base64 data of finance.yahoo.com using Google Apps Script.
Recently, it seems that the specification of the key for decrypting the data has been changed on the server side, again. In this update, I looked for the logic for retrieving the key value. But, I cannot still find it. So, in this post, I would like to use a workaround discussed in this thread. In this thread, the valid keys are listed in a text file.
Gists
This is a sample script for putting the values of all Spreadsheets in a folder to the master Spreadsheet with a low process cost using Google Apps Script.
There is a case in that I want to collect the values from multiple Spreadsheets and put the values into the master Spreadsheet. When this situation is achieved by Google Apps Script, as the general method, the values are required to be retrieved from each Spreadsheet in a loop.
Gists
This is a workaround for retrieving the hyperlink from the cell of a number value using Google Apps Script.
As a sample situation, it supposes that a cell “A1” has a number value like 123, and a hyperlink of https://tanaikech.github.io is set to the cell. In order to retrieve the hyperlink from the cell, it is required to use the methods of getRichTextValue() and getRichTextValues(). But, in the current stage, when the cell value is a number value, when the RichText is retrieved by getRichTextValue(), null is returned.
My report has been published at Champion Innovators Content Library.
Uploading Video File on Google Drive to YouTube with Resumable Upload using Google Apps Script If these reports will be useful, I’m glad.
Gists
This sample script decrypts the salted base64 data of finance.yahoo.com using Google Apps Script.
Recently, it seems that the specification of the key for decrypting the data has been changed at the server side. So. from this script, I updated the script as follows.
Sample script function myFunction() { // Load crypto-js.min.js. const cdnjs = "https://cdnjs.cloudflare.com/ajax/libs/crypto-js/4.1.1/crypto-js.min.js"; eval(UrlFetchApp.fetch(cdnjs).getContentText()); // Retrieve HTML and retrieve salted base64.
Gists
In the current stage, by the current specification, Google Apps Script cannot be directly run on Google Spreadsheet created by Service Account. But, there is a case in that we want to use the OnEdit trigger on the Spreadsheet that the service account is the owner. In this post, I would like to introduce the method for achieving this.
Recently, I published “Using OnEdit Trigger to Google Spreadsheet by Hiding Google Apps Script from Other Users”.
Gists
This is a method for using OnEdit Trigger to Google Spreadsheet by hiding Google Apps Script from other users.
A sample flow for achieving this is as follows.
Flow 1. Create a new Google Spreadsheet. Please create a new Google Spreadsheet. In this flow, this Google Spreadsheet is used for testing the script. And, please copy the Spreadsheet ID. This spreadsheet ID is used.
In this case, even when Spreadsheet has no container-bound script, this goal can be achieved.
Gists
This is a sample script for checking and replacing a character of U+00A0 (no-break space) with U+0020 (space) as Unicode using Google Apps Script.
When I’m seeing the questions on Stackoverflow, I sometimes saw the situation that the script doesn’t work while the script is correct. In this case, there is the case that the reason is due to U+00A0 being used as the spaces. When U+00A0 is used as the spaces, Google Apps Script and formulas cannot be correctly run.
Gists
This is a sample script for setting the line space of paragraphs on Google Documents using Google Apps Script.
When the line space of a paragraph on Google Documents is manually set, you can do it as follows.
When it is set with Google Apps Script, the following script can be used.
function sample1() { const doc = DocumentApp.getActiveDocument(); const body = doc.getBody(); const paragraph = body.
Gists
This is a sample script for opening and closing Google Forms on time using Google Apps Script.
In order to test this sample script, please do the following flow.
Usage 1. Create a new Google Form. Please create a new Google Form and set your sample questions. And, please open the script editor of Google Form.
2. Prepare sample script. Please copy and paste the following script to the script editor of Google Form.
Gists
This sample script decrypts the salted base64 data of finance.yahoo.com using Google Apps Script.
Recently, it seems that the specification of the key for decrypting the data has been changed at the server side. So. in this post, this post is updated. About this specification, I checked this thread.
Sample script function myFunction() { // Load crypto-js.min.js. const cdnjs = "https://cdnjs.cloudflare.com/ajax/libs/crypto-js/4.1.1/crypto-js.min.js"; eval(UrlFetchApp.fetch(cdnjs).getContentText()); // Retrieve HTML and retrieve salted base64.
Gists
This is a sample script for converting all pages in a PDF file to PNG images using Google Apps Script.
I have already published “Merging Multiple PDF Files as a Single PDF File using Google Apps Script”. In this post, it was found that pdf-lib can be used with Google Apps Script. From this, in this post, I would like to propose a sample script for converting all pages in a PDF file to PNG images using Google Apps Script.
Gists
This is a sample script for merging multiple PDF files as a single PDF file using Google Apps Script.
In this sample script, pdf-lib is used. In the current stage, it seems that this Javascript can be directly used with Google Apps Script.
Sample script 1 As a sample situation, please put multiple PDF files in your Google Drive. This sample merges those PDF files as a single PDF file.
Gists
This is a sample script for transferring the ownership of a file to another user using Google Apps Script.
In the current stage, about the consumer account (gmail.com) the specification for transferring the ownership of a file has been changed as follows. Ref
The current owner initiates an ownership transfer by creating or updating the prospective new owner’s file permission. The permission must include these settings: role=writer, type=user, and pendingOwner=true.
Gists
This is a sample script for retrieving the start and end of the month in a year using Google Apps Script and Javascript.
Sample script function myFunction() { const year = 2023; // Please set year you expect. const res = [...Array(12)].map((_, i) => [0, 1].map((e) => new Date(year, i + e, 1 - e)) ); console.log(res); console.log(res.map(([a, b]) => [a.toDateString(), b.toDateString()])); } Testing https://jsfiddle.
Gists
Published: January 3, 2023
Kanshi Tanaike
Introduction At Stackoverflow, a lot of people post questions and answers to the questions every day. There are various tags in Stackoverflow. A lot of discussions are performed at each tag. Their discussions bring important information and are much useful for a lot of people. As one of the tags, there is “google-apps-script”. I sometimes discuss the questions with that tag. When we see the discussions, we can notice that the discussions are changed and progressed over time, because “Google Apps Script” which is the origin of the tag is updated.
Gists
This is a simple sample script for filtering JSON objects using Google Apps Script.
In the current stage, V8 runtime can be used with Google Apps Script. By this, when you want to filter a JSON object, you can use the following sample script.
Sample script In this sample script, obj is filtered by the value of the even number.
const obj = { key1: 1, key2: 2, key3: 3, key4: 4, key5: 5 }; const res = Object.
Gists
This sample script decrypts the salted base64 data of finance.yahoo.com using Google Apps Script.
Recently, when I saw the HTML of finance.yahoo.com, I noticed that the data is converted by the salted base64. In order to decrypt the data, it is required to use the key data. But, unfortunately, I couldn’t find the key data from the HTML. When I searched for it, I found this thread. From the thread, I could retrieve the key data.
Gists
This is a sample script for encrypting and decrypting with AES using crypto-js with Google Apps Script.
Unfortunately, in the current stage, Google Apps Script cannot encrypt and decrypt AES using the built-in functions. In this post, in order to achieve this, “crypto-js” is used from cdnjs.com ( https://cdnjs.com/libraries/crypto-js ). In the current stage, it seems that the main functions of crypto-js.min.js can be directly used with Google Apps Script.
Gists
This is a sample script for rearranging columns on Google Spreadsheet using Google Apps Script.
Sample script In this sample script, the initial columns of “header1”, “header2”, “header3”, “header4” and “header5” are rearranged to “header2”, “header5”, “header1”, “header4”, “header3”. This result can be seen at the above image.
As an important point, in this script, the header titles in the 1st row are used. Please be careful about this.
Gists
In this post, I would like to introduce 2 sample scripts for copying and deleting the dimension groups in Google Spreadsheet using Google Apps Script.
Unfortunately, in the current stage, all dimension groups cannot be copied by one action, and also, all dimension groups cannot be deleted by one action. In this post, these can be achieved using Google Apps Script.
These sample scripts use Sheets API. So, please enable Sheets API at Advanced Google services.
Gists
This report is a workaround for reflecting the latest Google Apps Script to the deployed Web Apps without redeploying.
Pattern 1 Of course, when the developer mode of https://script.google.com/macros/s/###/dev is used, the latest script can be used without redeploying.
But, in this case, only the permitted users can use it using the access token. when you want to achieve this using the endpoint of https://script.google.com/macros/s/###/exec without the access token, in order to reflect the latest script to Web Apps, it is required to redeploy.
Gists
This is a sample script for searching Gmail messages by Gmail Filters using Google Apps Script.
At Gmail, users can set the filter for the Emails. With this filter, users can filter the Emails of Gmail. But, when the users want to search by the installed filter using Google Apps Script, unfortunately, it seems that this cannot be directly achieved. For example, messages cannot be searched using a filter ID.
Gists
These are sample scripts for retrieving specific folders from Google Drive using Google Drive service (DriveApp) with Google Apps Script.
Retrieving folders in own Google Drive const folders = DriveApp.searchFolders( `'${Session.getActiveUser().getEmail()}' in owners and trashed=false` ); const res = []; while (folders.hasNext()) { const folder = folders.next(); res.push(folder.getName()); } console.log(res); Retrieving folders in shared Drives const folders = DriveApp.searchFolders( `not '${Session.getActiveUser().getEmail()}' in owners and trashed=false` ); const res = []; while (folders.
Gists
This is a workaround for converting a Gmail message to a PNG image using Google Apps Script.
Sample script Please set the message ID of Gmail.
function myFunction() { var id = "###"; // Please set your message ID of Gmail. var message = GmailApp.getMessageById(id); var date = Utilities.formatDate( message.getDate(), Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss" ); var from = message.getFrom(); var to = message.
My report has been featured by Google Workspace Developer Newsletter. Ref
The featured report is “Report: Implementing Pseudo 2FA for Web Apps using Google Apps Script”.
Gists
This is a simple sample script for uploading a video file on Google Drive to YouTube with the resumable upload using Google Apps Script.
When you want to upload a video file to YouTube using Google Apps Script, when YouTube API of Advanced Google services is used, the maximum file size is 5 MB, because, in this case, the video file is uploaded with multipart/form-data. When you want to use a video file with more file size using Google Apps Script, a resumable upload is required to be used.
Gists
This is a workaround for detecting to edit Google Spreadsheet using Sheets API with the service account.
It has already been found that when Google Spreadsheet is edited using Sheets API, this can be detected by the installed OnChange trigger. For example, when a Spreadsheet is edited using Sheets API with the access token retrieved from your Google account, the event object of the installable OnChange trigger includes your email address and nickname.
Gists
These are the sample scripts for using getBatchGet, batchUpdateContacts, batchDeleteContacts, batchCreateContacts of People API with Google Apps Script.
When the data is retrieved and put with People API, the process cost can be reduced by using batch requests. Fortunately, in the current stage, People API has native methods for using batch requests. Those are getBatchGet, batchCreateContacts, batchDeleteContacts, and batchUpdateContacts. In this post, I would like to introduce the sample scripts for using the batch requests of People API.
My report has been published at Champion Innovators Content Library.
Report: Implementing Pseudo 2FA for Web Apps using Google Apps Script If these reports will be useful, I’m glad.
Gists
This is a workaround for checking the existence of file ID in Google Drive without both the access token and API key.
When you want to check whether the file of the file ID is existing in Google Drive, generally, you might use Drive API and Drive service (DriveApp) of Google Apps Script. In this case, the scope of Drive API is required to be used. By this, the access token and the API key (in the case of publicly shared files) are required to be used.
Gists
This is a workaround for retrieving the values and URLs from the smart chips inserted in Google Document using Google Apps Script.
Recently, the smart chips for Google Document are updated. Ref1 and Ref2 It is considered that this update will advance the collaboration for editing Document. So, there might a case that the information of the smart chips is required to be retrieved. Unfortunately, in the current stage, there are no methods for directly retrieving the information of the smart chips while the smart chips of DATE and PERSON can be retrieved.
Gists
I have submitted a report of “Workaround: Putting Multiple Hyperlinks to a Cell using Sheets API” before. At that time, there are no direct methods for setting multiple hyperlinks to the part of text in a cell. But, recently, textFormatRuns was added to Sheets API. By this, multiple hyperlinks got to be able to be set to the part of text in a cell. In this report, I would like to introduce a sample script for this.
Gists
Abstract In Google Apps Script, there is the Web Apps. When Web Apps is used, the users can execute Google Apps Script using HTML and Javascript. This can be applied to various applications. When the Web Apps is deployed with “Anyone”, anyone can access the Web Apps. And, there is the case that Web Apps deployed with “Anyone” is required to be used. Under this condition, when 2 Factor Authentication (2FA) can be implemented, it is considered that the security can be higher and it leads to giving various directions for the applications using Web Apps.
Gists
Unfortunately, in the current stage, in order to retrieve the file list using the created date, the search query of createdDate cannot be used with searchFiles method of DriveApp in Google Apps Script. This has already been reported at this issue tracker In this post, I would like to introduce a workaround for searching the files using the created date.
Issue and workaround The parameter of “searchFiles” method of DriveApp uses the search query for Drive API v2.
Gists
This is a sample script for updating Array1 with Array2 using Google Apps Script.
As a sample situation, there are 2 arrays (Array1 and Array2) of the 2-dimensional array. The sample situation can be seen in the above sample Spreadsheet.
Conditions When the values of column “A” of Array2 are existing in column “A” of Array1, the rows of Array1 are updated by that of Array2. When the values of column “A” of Array2 are not existing in column “A” of Array1, the rows of Array2 are appended to Array1.
My report has been published on the Blog of Google Cloud.
Blog Efficient File Management using Batch Requests with Google Apps Script
Twitter
Gist
This is a sample script for retrieving the batch path for using the batch requests using Google Apps Script.
After August 12, 2020, in order to use batch requests, the batch path is required to be used to the endpoint of the batch requests. And, the batch path is sometimes updated. So, when a constant batch path has been continued to be used, this might lead to the reason for an error.
BatchRequest was updated to v1.2.0. v1.2.0 (September 30, 2022)
A new method of getBatchPath(name, version) was added. After August 12, 2020, in order to use batch requests, the batch path is required to be used to the endpoint of the batch requests. And, the batch path is sometimes updated. So, when a constant batch path has been continued to be used, this might lead to the reason for an error.
Overview This is a Google Apps Script library for supporting Document service, Docs API, Spreadsheet service, Sheets API, Slides service and Slides API. The aim of this library is to compensate the processes that they services cannot achieve.
DocsServiceApp was updated to v1.2.0 v1.2.0 (September 29, 2022)
Added a new method of getNamedFunctions(). This method can retrieve the named functions from Google Spreadsheet. You can see the detail information here https://github.
Gists
This is a sample script for retrieving the named functions from Google Spreadsheet using Google Apps Script.
Recently, the named functions got to be able to be used in Google Spreadsheet. Ref When several named functions are added, I thought that I wanted to retrieve these functions using a script. But, unfortunately, in the current stage, it seems that there are no built-in methods (SpreadsheetApp and Sheets API) for directly retrieving the named functions.
Overview This is a Google Apps Script library for supporting Document service, Docs API, Spreadsheet service, Sheets API, Slides service and Slides API. The aim of this library is to compensate the processes that they services cannot achieve.
DocsServiceApp was updated to v1.1.0 v1.1.0 (September 28, 2022)
Added a new method of getQuotePrefixCells(). This method can detect the cells with the quote prefix cells. You can see the detail information here https://github.
Gists
This is a sample script for showing the specific rows and columns in Google Spreadsheet using Google Apps Script.
When you export a Google Spreadsheet as a PDF file, you might have a case where you want to export the specific rows and columns in a sheet. In this post, I would like to introduce the sample script for achieving this.
Script Please copy and paste the following scripts to the script editor of Google Spreadsheet.
Gists
These are sample scripts for removing the vertical borders of a table in Google Document using Google Apps Script.
Unfortunately, in the current stage, only the vertical borders cannot be removed from the table in Google Document using the Google Document service (DocumentApp). I believe that this might be resolved in the future update. But, in the current stage, when Google Docs API is used, this can be achieved. So, Google Docs API can be used as the current workaround.
Gists
This is a sample script for retrying UrlFetchApp of Google Apps Script when an error occurs.
When the HTTP request is run using UrlFetchApp, there is a case that an error occurs in various situations. And, there is a case that when the request is run again, no error occurs. This sample script can automatically retry the requests using Google Apps Script.
Sample script This is Class RetryFetch.
/** * UrlFetchApp is run by retrying when an error occurs.
Gists
This is a sample script for requesting with keeping cookies using Google Apps Script.
This might be similar to requests.Session" of Python. Ref
Sample script This is Class SessionFetch.
/** * UrlFetchApp is run by keeping Cookie. */ class SessionFetch { constructor() { this.cookie = ""; this.his = []; } /** * Request URL. * @param {string} url URL * @param {object} params Object * @return {UrlFetchApp.
Gists
This is a sample script for retrieving the files of ‘Shared with Me’ in the specific folder using Google Apps Script.
In the current stage, when you transfer the ownership of your file on your Google Drive to another user and/or copy the file “Shared with me” to the specific folder on your Google Drive, the file becomes the shortcut file. Under this situation, when you want to retrieve the files of “Shared with me” in the specific folder, unfortunately, the following search query cannot be used.
Gists
These are sample scripts for achieving the full-text search of Google Apps Script projects using Google Apps Script. I have the case that I want to search a value from Google Apps Script projects using a Google Apps Script. In this post, I would like to introduce the sample scripts for achieving the full-text search of Google Apps Script projects.
1. Full-text search from all Google Apps Script Projects of standalone type in Google Drive Before you use this script, please enable Drive API at Advanced Google services.
Gists
Kanshi Tanaike
Introduction There is a maximum executing time for Google Apps Script (GAS). That is 6 minutes. And, in the case of the custom function and the simple trigger, it is 30 seconds. Ref So users always have to pay attention to reducing the process cost of the scripts. Especially, it is very important to know the process costs for the array processing, because array processing is often used for spreadsheets and Google APIs.
Following 3 reports have been published at Champion Innovators Content Library.
Taking advantage of Web Apps with Google Apps Script Report: Management of Images on Google Spreadsheet using Google Apps Script Report: Handling 10,000,000 cells in Google Spreadsheet using Google Apps Script If these reports will be useful, I’m glad.
Gists
This is a sample script for converting the large images to Google Document by OCR using Google Apps Script.
When the image size, the image file size, the resolution of the image, and so on are large, an error like Request Too Large occurs. In this sample script, such the image can be converted to Google Document by reducing them.
Sample script Please enable Drive API at Advanced Google services.
Gists
This is a sample script for detecting cells with the quote prefix in Google Spreadsheet using Google Apps Script.
For example, when a value is put to a cell by adding a single quote ' as the top character, the cell value is used as the string value. This is the current specification. Under this condition, when the cells with the value of the quote prefix are tried to be detected, unfortunately, in the current stage, it seems that there is no method for directly achieving this in the methods of Spreadsheet service (SpreadsheetApp).
Gists
This is a report related to the rule of item IDs for questions of Google Forms.
When the questions are created using the method of batchUpdate with Google Forms API, the created questions have the item IDs when the item IDs are not given in the request body. ( https://developers.google.com/forms/api/reference/rest/v1/forms#item ) For example, when you want to create a question and update the created question in one API call, it is required to include the custom item ID in the request body.
Gists
Today, I found a question ( https://stackoverflow.com/q/73540735 ) in Stackoverflow by Max Makhrov. When I saw this question, I thought that this is a good raising question. In this question, it has reported that when the Spreadsheet includes the formulas of the large calculation, when a Google Apps Script is run during the calculation of the formulas is running, the processing time of the script becomes long.
I have also experienced this before.
Gists
This is a sample script for updating the same position on Google Document using Google Apps Script.
In this case, the named range is used. About the named range on Google Document, in the current stage, unfortunately, this cannot be used with the UI on Google Document. And, when I saw the official document of named range, I thought that this might be a bit difficult. Ref Ref So, I have created a Google Apps Script for managing the named range on Google Document.
My report has been published at Champion Innovators Content Library. The published report is Taking Advantage of Google Apps Script. If this report will be useful, I’m glad.
Gists
This is a sample script for high-efficiency finding and replacing many values in Google Spreadsheet with the low process cost using Google Apps Script.
When the various values are replaced in Google Spreadsheet using Google Apps Script, I’m worried about the process cost. So, in this report, I would like to introduce a sample script for high-efficiency achieving this.
As the result, using a sample situation, when the process cost of the sample script using Sheets API is compared with that of the sample script using Spreadsheet services (SpreadsheetApp), it was found that the above script using Sheets API could reduce the process cost by about 70 % from the script using Spreadsheet service.
Gists
This is a sample script for updating the destination sheet by the source sheet in Google Spreadsheet using Google Apps Script.
The sample situation is as follows.
Sample script function myFunction() { const sheetNames = ["Sheet1", "Sheet2"]; // Retrieve values from source and destination sheets. const ss = SpreadsheetApp.getActiveSpreadsheet(); const [srcSheet, dstSheet] = sheetNames.map((s) => ss.getSheetByName(s)); const [srcValues, dstValues] = [srcSheet, dstSheet].map((s) => s.
Gists
This is a sample script for detecting whether the specific cells on Google Spreadsheet are manually moved using Google Apps Script.
In this case, the named range, OnChange trigger, and PropertiesService are used.
Usage: 1. Create a named range. As a sample, please create a named range to the cells “A2:B2” as “sampleNamedRange1”. Ref
2. Prepare sample script. Please copy and paste the following script to the script editor of Spreadsheet.
Gists
In this post, I would like to introduce the method for using RichTextValue with a custom function of Google Apps Script.
This sample is for this thread in Stackoverflow.
In this thread, the OP’s goal is as follows.
Put a text to a cell. In this case, use a hyperlink in a part of the text. This is required to be achieved using a custom function. In the current stage, in order to reflect the hyperlink in a part of the text, it is required to use setRichTextValue of Google Apps Script.
Gists
This is a sample script for retrieving icons of each mimeType on Google Drive using Google Apps Script.
Sample script Sample list of mimeType is from this official document.
function getIcons() { const iconSize = 256; // Pixels const mimeTypes = [ "application/vnd.google-apps.audio", "application/vnd.google-apps.document", "application/vnd.google-apps.drive-sdk", "application/vnd.google-apps.drawing", "application/vnd.google-apps.file", "application/vnd.google-apps.folder", "application/vnd.google-apps.form", "application/vnd.google-apps.fusiontable", "application/vnd.google-apps.jam", "application/vnd.google-apps.map", "application/vnd.google-apps.photo", "application/vnd.google-apps.presentation", "application/vnd.
Gists
Updated on January 25, 2024 Overview: This is a report for challenging exporting the selected cells on Spreadsheet as an image using Google Apps Script and Javascript.
Description: This report is based on this question by Max Makhrov. When I saw this question, I remembered that there are many questions for asking this in Stackoverflow. And, I thought that when this is achieved, it will be useful for the owner of this question and a lot of users.
Gists
This is a sample script for clearing the discrete cell values on multiple sheets using Google Apps Script.
There might be a case where you want to clear the values of the discrete cells in the multiple sheets using Google Apps Script. In this post, I would like to introduce the efficient script for achieving this.
Sample script 1 Please copy and paste the following script to the script editor of the Google Spreadsheet you want to use.
Gists
This is a sample script for creating the user’s dashboard by inputting the user name and password using Web Apps with Google Apps Script. In this case, Google Spreadsheet is used as a database of the dashboard.
Usage 1. Create a Google Apps Script project. In order to use Web Apps, please create a new Google Apps Script project. In this case, please create a new Spreadsheet and open the script editor of Spreadsheet.
Gists
This is a sample script for retrieving the names of month and day of week using Google Apps Script.
I think that you might have a case that you want to retrieve the names of month and day of week using Google Apps Script. This sample script retrieves them using a simple script.
Using Utilities.formatDate of Google Apps Script, the names of month and day of week can be retrieved using a simple script.
Gists
This is a sample script for checking the exchange rate using GOOGLEFINANCE with Google Apps Script.
Recently, I have published a report of “Report: Obtaining Values from GOOGLEFINANCE using Google Apps Script”. Ref In this post, I would like to introduce a sample script for checking the exchange rate using Google Apps Script.
Sample script Please copy and paste the following script to the script editor of Spreadsheet.
// When this script is run, a trigger for executing "checkCurrency" function is installed.
Gists
This is a sample script for replacing images on Google Document in order using Google Apps Script.
Sample script This sample script uses Drive API and Docs API. So, please enable Drive API and Docs API at Advanced Google services. Ref
In this sample script, the images on Google Document are replaced with the image files on your Google Drive in order. Each image in Document is replaced in order of file Ids in fileIds.
Gists
This is a report for management of images on Google Spreadsheet using Google Apps Script.
At October 30, 2018, Cass OverGridImage and the method of inserImage have been added to Spreadsheet Service. Ref At January 19, 2022, Class CellImageBuilder and Class CellImage have been added to Spreadsheet Service. Ref By these Classes and methods, the images got to be able to be managed on Google Spreadsheet. But, when the image is used to the various situations, there are the cases that it is required to ingenuity to manage the images.
Gists
This is a report for the documentation comments for the functions of Google Apps Script.
When the documentation comments for functions of Google Apps Script are considered, you will think JsDoc. At Google Apps Script, a part of JsDoc can be used. But, in this report, I would like to introduce the documentation comments including JsDoc.
Sample situations Sample 1 For example, when the following sample function is written,
Gists
Introduction In order to retrieve the values from XML data, when XML data is parsed using Google Apps Script, there are several methods for parsing the data. Class XmlService, which is a built-in Class for managing XML data, might be the first way to come up with it. At Stackoverflow, it is posted questions that XML data is often parsed using Class XmlService. It is considered that Class XmlService is suitable for managing XML data.
Gists
This is a sample script for retrieving the values of dropdown list of the smart chips on Google Document using Google Apps Script.
At August 23, 2021, 3 Classes for retrieving the smart chips have been added to Google Apps Script. But, in the current stage, unfortunately, all values of the smart chips cannot be retrieved by the Classes. For example, the dropdown list of the smart chips cannot be retrieved using the 3 Classes.
Gists
This is a sample script for removing the invalid named range of #REF from Google Spreadsheet using Google Apps Script.
Issue and workaround For example, there are 2 sheets of “Sheet1” and “Sheet2” in a Google Spreadsheet. A new named range of sample is created for the range of Sheet1!A1, and remove the sheet of “Sheet1”. By this flow, when the named range list is checked by UI on Spreadsheet, sample has #REF.
Gists
This is a sample script for converting A1Notation to GridRange and vice versa using Google Apps Script without any scopes.
A1Notation and GridRange are often used with Sheets API. I have posted a sample script for converting A1Notation to GridRange before. Ref But, in that case, I used the method of Spreadsheet service (SpreadsheetApp). By this, in order to use the script, it is required to authorize the scopes. In this sample script, A1Notation can be converted to GridRange and vice versa with no scopes.
Gists
This is a sample script for shortening a long URL using Firebase Dynamic Links API with Google Apps Script.
IMPORTANT Before you use this script, please create a new Firebase project and link it to your Google Cloud Platform Project. Ref And, please enable Firebase Dynamic Links API at the API console. And then, please create your API key from your Google Cloud Platform Project.
Sample script const apiKey = "###"; // Please set your API key.
Gists
This is a sample script for protecting the cells of a Spreadsheet by clicking a checkbox using Google Apps Script.
You might have a situation where you want to protect the cells when a user checks a checkbox. This sample script is for achieving this situation. The demonstration of this sample script is as follows.
This demonstration is for a user. You can see that when the checkbox is checked, the checkbox and the right side of the checkbox are protected.
Gists
This is a sample flow for parsing XML data in Google Apps Script using IMPORTXML. Recently, it seems that ContentService.MimeType.XML has been removed by the Google side. By this, in the current stage, the XML data cannot be directly loaded by the Web Apps URL with IMPORTXML. From this current situation, I would like to introduce a workaround. In this workaround, the XML data in Google Apps Script is parsed by IMPORTXML of the built-in function of Google Spreadsheet.
There is a method of ScriptApp.getService().getUrl() for obtaining the Web Apps URL in Google Apps Script.
Before the V8 runtime is released, this method had returned the endpoint like https://script.google.com/macros/s/{deploymentId}/exec.
After the V8 runtime was released, the endpoint of https://script.google.com/macros/s/{deploymentId}/dev was returned.
Now, it seems that this returns https://script.google.com/macros/s/###/exec.
But, in the current stage, when I access this endpoint, the message of Sorry, unable to open the file at this time.
Gists
Introduction When we use HTML in the Google Apps Script project, in order to show the values from the Google Apps Script side, the HTML template is used. When I used the HTML template with a large value, I understood that the process cost can be reduced by devising a script. In this report, I would like to introduce the process cost of the HTML template using the benchmark.
Gists
This is a sample method for publishing various Google Docs files with the same URL using Google Apps Script.
By updating on May 25, 2022, the content got to be able to be embedded as a full page in the new Google site. Ref In this method, this is used.
Usage 1. Create a Google Docs. First, as a simple sample, please create a new Google Spreadsheet. And please copy the URL like https://docs.
Gists
This is a sample script for replacing values in the cells on Google Spreadsheet using Google Apps Script.
Sample situation The sample situation is as follows.
Sample script This sample script used Sheets API. So, please enable Sheets API at Advanced Google services.
function myFunction() { const obj = { sample1: "ab", sample2: "cd", sample3: "ef", sample4: "gh", sample5: "ij", sample6: "kl", sample7: "mn", sample8: "op", sample9: "qr", }; const ss = SpreadsheetApp.
Gists
This is a sample script for parsing JSON data array by expanding the header row using Google Apps Script.
Sample script function myFunction() { const obj = [ { key1: "value1", key2: "value2", key3: "value3" }, { key4: "value1", key5: "value2", key6: "value3" }, { key7: "value1", key8: "value2", key9: "value3" }, ]; const headers = Object.keys( obj.reduce((o, e) => (Object.keys(e).forEach((k) => (o[k] = true)), o), []) ); const values = [headers, .
RichTextApp was updated to v1.4.0 v1.4.0 (May 25, 2022)
Added a new method of ReplaceTextToRichText. In this method, the text in a cell is converted to the richtext. You can see the detail information here https://github.com/tanaikech/RichTextApp
Gists
This is a sample script for exporting the Tabulator data to Google Drive using Google Apps Script.
As the sample, a dialog on Google Spreadsheet is used. So, please copy and paste the following scripts to the container-bound script of Google Spreadsheet.
Google Apps Script side: Code.gs const saveFile = (e) => DriveApp.createFile(Utilities.newBlob(...e)).getId(); // Please run this script. const openDialog = (_) => SpreadsheetApp.getUi().showModalDialog( HtmlService.createHtmlOutputFromFile("index"), "sample" ); HTML & Javascript side: index.
Gists
This is a sample script for requesting to Gate API v4 using Google Apps Script.
The official document of Gate API v4 is here. Recently, I answered this thread. In that case, in order to convert the sample python script to Google Apps Script, the script for retrieving the signature might be a bit complicated. So, here, I would like to introduce this.
Sample python script from official document This is a sample python script from official document.
Gists
This is a sample script for retrieving the text positions in the text data using Google Apps Script.
For example, in order to set the rich text style the part of text from the text data, this sample script will be useful.
Sample situation 1 The sample situation is as follows.
sample1, sample2, sample3, sample4, sample5
sample1, sample2, sample3, sample4, sample5
sample1, sample2, sample3, sample4, sample5 In this sample, the text positions of sample2 and sample5 are retrieved from this sample text data.
Gists
This is a sample script for retrieving and parsing the XML data from Google Workspace Update Blog and putting it to Google Spreadsheet using Google Apps Script.
At Google Workspace Update Blog, the XML data is provided. By this, the retrieved XML data is parsed with XmlService, and the data is put to Google Spreadsheet. Recently, I got a request for this. So I created this sample script. When this was useful for your situation, I’m glad.
Gists
This is a sample script for retrieving the values of subscriberCount of the channel from the video URLs of YouTube using Google Apps Script.
In this sample, the video URLs are retrieved from Spreadsheet. And, the retrieved values of subscriberCount are put to the Spreadsheet. The sample Spreadsheet is as follows.
Sample script Please copy and paste the following script to the script editor of Spreadsheet. Before you use this script, please enable YouTube Data API v3 at Advanced Google services.
Gists
This is a sample script for splitting and processing an array every n length using Google Apps Script. When I prepare a sample script with Google Apps Script, I sometimes have the situation that it is required to split and process an array every n length. This sample script is for achieving this situation.
Please set limit. This sample script splits the sample array every 3 length.
When you use this script with Google Apps Script, please enable V8 runtime.
Gists
This is a report for obtaining the values from GOOGLEFINANCE using Google Apps Script. When I tested to retrieve the values from GOOGLEFINANCE function on Google Spreadsheet using Google Apps Script, I noticed that the values can be retrieved.
When I had tested this before, I had got the value of #N/A. About retrieving the values from GOOGLEFINANCE function on Google Spreadsheet, I had known “Historical GOOGLEFINANCE data no longer accessible outside of Google Sheets”.
Gists
This is a sample script for reducing the image data size using Google Apps Script. You might have a situation where you might want to reduce the data size of image data using Google Apps Script. Here, using Google Apps Script, I would like to introduce a sample script for reducing the data size of the image data by reducing the image quality.
Limitations In the current stage, by the specification of Google side, there are the following limitations.
Gists
This is a sample script for expanding the rows in Google Spreadsheet using Google Apps Script. The sample situation is as follows.
Sample situation Input Output Sample script function myFunction() { const expandedColumns = [2, 3, 4, 5]; // Please set the expanded columns you expect. const delimiter = "\n"; // Please set the delimiter. const srcSheetName = "Sheet1"; // Please set the source sheet name. const dstSheetName = "Sheet2"; // Please set the destination sheet name.
Gists
This is a report for checking the number of requests for Sheets API. I had contact about the quota for Sheets API. So, in order to explain this, I used the following simple sample scripts.
Sample 1 This sample puts a value of “sample” to a cell “A1” using the batchUpdate method. This request body includes one request. When this script is run, one API quota is used.
function sample1() { const spreadsheetId = "###"; const sheetId = 0; const requests = [{ "updateCells": { "rows": [ { "values": [ { "userEnteredValue": { "stringValue": "sample" } } ] } ], "range": { "sheetId", "startRowIndex": 0, "endRowIndex": 1, "startColumnIndex": 0, "endColumnIndex": 1 }, "fields": "userEnteredValue.
Gists
This is a sample script for increasing the column letter by one using Google Apps Script.
Sample script const increase = (ar) => ar.map((e) => { const idx = [...e].reduce( (c, e, i, a) => (c += (e.charCodeAt(0) - 64) * Math.pow(26, a.length - i - 1)), -1 ); // Ref: https://stackoverflow.com/a/53678158 columnIndexToLetter = (n) => (a = Math.floor(n / 26)) >= 0 ?
Overview This is a Google Apps Script library for managing the named range on Google Documents.
Description Google Document can use the named range. When the named range is used, the users can directly access the contents using the named range. For example, the developer can guide the users to the specific content in Google Documents using the named range. But, unfortunately, in the current stage, it seems that the named range cannot be directly used by the UI on Google Documents.
Gists
This is a sample script for analyzing the responses from Grid Items of Google Form using Google Apps Script.
In this sample situation, all responses are retrieved from Grid Items of Google Form, and the average values of each row of each question are calculated. And, the result is put on the Spreadsheet.
Sample situation Input: Sample Google Form The sample Google Form is as follows.
Output: Sample Spreadsheet The sample output is as follows.
Gists
Introduction On March 14, 2022, it reported about “Google Sheets doubles cell limit”. Ref By this update, now, the users can use 10,000,000 cells in a Google Spreadsheet. This is great news for us. When I tried to handle 10,000,000 cells in a Google Spreadsheet using Google Apps Script, it was found that there were various important points. In this report, I would like to introduce the important points for handling 10,000,000 cells in Google Spreadsheet using Google Apps Script.
Gists
This is a sample script for replacing the multiple paragraphs on Google Document with a regex using Google Apps Script. There is the method of replaceText(searchPattern, replacement) for replacing the text on Google Document. Ref For example, when a text of sample1 is replaced with sample2, this can be achieved by a script like DocumentApp.getActiveDocument().getBody().replaceText("sample1", "sample2"). But, when the texts for replacing are the multiple paragraphs, this script cannot be used.
Gists
This is a sample script for simply converting HTML to plain text using Google Apps Script.
Sample values HTML (input value) <div id="sample1">sample text1</div> <div id="sample2">sample text2</div> <ul id="sample3"> <li>sample list 1</li> <li>sample list 2</li> </ul> <table id="sample4"> <tbody> <tr> <td>a1</td> <td>b1</td> <td>c1</td> </tr> <tr> <td>a2</td> <td>b2</td> <td>c2</td> </tr> </tbody> </table> Text (output value) sample text1
sample text2
- sample list 1
- sample list 2
a1 b1 c1
a2 b2 c2 Sample script function myFunction() { const sampleHTML = `<div id="sample1">sample text1</div> <div id="sample2">sample text2</div> <ul id="sample3"> <li>sample list 1</li> <li>sample list 2</li> </ul> <table id="sample4"> <tbody> <tr> <td>a1</td> <td>b1</td> <td>c1</td> </tr> <tr> <td>a2</td> <td>b2</td> <td>c2</td> </tr> </tbody> </table>`; const temp = GmailApp.
Gists
This is a sample Google Apps Script for processing the values in Google Spreadsheet. In this sample situation, each row is merged using the same header title.
In this sample script, the sample input and output situations are as follows.
Sample situation Input: Output: Sample script In this sample script, this sample can be used as the custom function.
function SAMPLE(values) { const headers = [ ...new Set( values .
On April 13, 2022, “Additional functionality for the Apps Script Integrated Development Environment (IDE) Script Editor” has been reported.
They say as follows.
We’re now adding several new features to the IDE to help achieve functional parity with the legacy IDE experience. These features are:
Script Properties Add-on Testing Time Zone Setting Rhino Debugging By this update, I believe that the script editor will be more useful.
Gists
This is a sample script for creating quizzes in Google Form using Google Forms Service with Google Apps Script.
Usage 1. Prepare questions and answers. In this sample, the questions and answers are prepared using Spreadsheet as follows.
2. Sample script. This script is container-bound script of the above Spreadsheet.
function myFunction() { const formTitle = "sample"; // This is a form title. const sheetName = "Sheet1"; // This is a sheet name.
Gists
This is a sample script for creating quizzes in Google Form using Google Forms API with Google Apps Script. Recently, Google Forms API has been officially published, and it got to be able to be used by users. By this, quizzes in Google Form can be created using Google Forms API.
Here, there is one thing that can be achieved by Google Forms API. When Google Forms API is used, each choice in each question can be shuffled.
Gists
This is a sample script for retrieving the summary of Google Document using Google Apps Script. Recently, a blog of Auto-generated Summaries in Google Docs has been posted. I thought that this is very interesting function. I thought that when this function is released, checking each summary of a lot of Google Document will be much useful for simply confirming the document content. And also, I thought that when all summaries can be retrieved using a script, it will be also useful.
Gists
This is a sample Google Apps Script for processing the values in Google Spreadsheet. In this sample situation, each column are merged using the same header title.
In this sample script, the sample input and output situations are as follows.
Sample situation Input: “Sheet1” Output: “Sheet2” Sample script function myFunction() { const srcSheetName = "Sheet1"; // This sheet is "Input" situation. const dstSheetName = "Sheet2"; // This sheet is "Output" situation.
Gists
Introduction In this report, I would like to introduce to apply the spread syntax and the destructuring assignment to Google Spreadsheet with Google Apps Script. The destructuring assignment can be used without V8 runtime. But, the spread syntax is required to be used with V8 runtime. Recently, I often saw the script using them at Stackoverflow. And also, I have sometimes gotten the questions related to the spread syntax and the destructuring assignment.
Gists
When I tested Google Forms API, I noticed that when a new Google Form is created by the method of forms.create, there is not title of the created Google Form. So, I have reported this to Google issue tracker. Ref Today, I confirmed that this bug has been removed.
And, when I saw the official document, I noticed that the following document has been added. Ref
In the current stage, when the following curl command is used, a new Google Form with the file title of sampleFormTitle and the form title of sampletitle can be created.
v2.0.0 (February 25, 2022)
Modified using the latest libraries. The specification of ggsrun is not changed. You can check ggsrun at https://github.com/tanaikech/ggsrun.
Gists
This is a sample script of a counter in a cell of Google Spreadsheet using the infinite loop with Google Apps Script. Recently, I have reported about the infinite loop on Google Spreadsheet. Ref This sample script achieves a counter in a cell using the infinite loop.
Sample script This sample script is a test script for counting the number using the infinite loop. Please be careful this. Please copy and paste the following script to the script editor of Google Spreadsheet and save it.
Gists
Here, I would like to introduce a report for occurring and resolving the infinite loop on Google Spreadsheet using Google Apps Script. I have reported this to Google issue tracker. Ref
Sample script: Occurring infinite loop This sample script is a test script for confirming the infinite loop. Please be careful this. Please copy and paste the following script to the script editor of Google Spreadsheet and save it. And, please install OnChange trigger to the function onChange().
Gists
This is a report for inserting the multiple paragraphs to Google Document in order using Google Docs API.
When the multiple paragraphs are inserted to Google Document using Google Docs API, it is required to pay attention to the index for inserting the texts. In this report, I would like to introduce the points for achieving this with a simple method.
Although this report uses Google Apps Script, the logic of this method can be used for other language.
RichTextApp was updated to v1.3.1 v1.3.1 (February 8, 2022)
Modified the method of DocumentToSpreadsheet. Before this update, the paragraphs with only line breaks have been ignored. From this version, such paragraphs are included in the result value of the cell. You can see the detail information here https://github.com/tanaikech/RichTextApp
Today, I noticed that the sheet ID of Spreadsheet, which is one of database, had been changed. So I used the sheet name of “Libraries” instead of the sheet ID. By this, the Google Apps Script library can be retrieved from 2 databases.
Google Apps Script Library Database
Overview This is a Google Apps Script library for parsing the form object from HTML form and appending the submitted values to the Spreadsheet.
Description There is Google Form in the Google service. Google Form can parse the submitted data and put it in the Spreadsheet. But when we want to use the custom form, it is required to use the HTML form on Web Apps, dialog, and sidebar. In this case, it is required to prepare Javascript and Google Apps Script for parsing the form object from the HTML form and appending the parsed values to Spreadsheet.
On January 19, 2022, 2 classes of CellImageBuilder and CellImage have been added to the Spreadsheet Service.
CellImageBuilder: https://developers.google.com/apps-script/reference/spreadsheet/cell-image-builder CellImage: https://developers.google.com/apps-script/reference/spreadsheet/cell-image Unfortunately, in the current stage, it seems that the image manually put to a cell without using the URL cannot still be retrieved.
When an image is manually put from URL and Google Drive to a cell, this image cannot be retrieved. When an image is put using setSourceUrl method with a script, this image and URL cannot be retrieved.
Gists
This is a workaround for inserting the non-public image of Google Drive using IMAGE function in a cell on Google Spreadsheet using Google Apps Script.
When an image on Google Drive is inserted to a cell of Spreadsheet using =IMAGE(URL) function, the image of URL is required to be publicly shared. But, there is the case that the image cannot be publicly shared. This workaround might be able to be used for this situation.
This is a Javascript library for sending the HTML form object to Google Apps Script using google.script.run.
HtmlFormObjectParserForGoogleAppsScript_js was updated to v1.0.1.
v1.0.1 (January 11, 2022)
A new argument of includeOrder was added as the 4th argument. This library returns the parsed form object as a JSON object. By this, the order of HTML form object is not saved. From this version, this order can be included. This argument includes the order of each input tag in form.
Gists
This is a sample script for importing a CSV data by keeping the number formats of cells on Google Spreadsheet using Google Apps Script.
When a CSV data is manually put using the default UI using the browser, it seems that the number formats of all cells cannot be kept. In order to import the CSV data to the cells with keeping the number formats, it is required to use a script as a workaround.
Gists
Kanshi Tanaike
Introduction At Stackoverflow, a lot of people post the questions and answers to the questions every day. There are various tags in Stackoverflow. A lot of discussions are performed at each tag. Their discussions bring important information and are much useful for a lot of people. As one of the tags, there is “google-apps-script”. I sometimes discuss the questions with that tag. When we see the discussions, we can notice that the discussions are changed and progressed by the time, because “Google Apps Script” which is the origin of the tag is updated.
Gists
This is a report for detecting the operations to Google Spreadsheet by the owner, the specific users, and the anonymous users using Google Apps Script. When the Spreadsheet is shared with the specific users and/or the anonymous users, when these users can be identified, it will be useful. In this report, I would like to introduce the method for identifying the users who are the owner, the specific users, and the anonymous users.
Gists
This is a simple method for using ggsrun. ggsrun is a CLI tool to execute Google Apps Script (GAS) on a terminal. Also this CLI tool can be used for managing files in Google Drive for OAuth2 and Service Account.
When you use ggsrun, it is required to retrieve the client ID and client secret and/or the service account at Cloud Platform Project. But, there is the case that you want to simply test or you want to just upload and download the files for Google Drive.
Gists
This is a sample script for inverting the selected objects on Google Slides using Google Apps Script.
I have the case that I want to invert the selected objects on Google Slides. This sample script can be achieved this goal using Google Apps Script.
Sample script Please copy and paste the following script to the script editor of Google Slides, and save the script. And, please select the objects on the Slide and run the function main().
Gists
This is a sample script for inverting the selected ranges on Google Spreadsheet using Google Apps Script.
I have the case that I want to invert the selected ranges on Google Spreadsheet. This sample script can be achieved this goal using Google Apps Script.
Sample script Please copy and paste the following script to the script editor of Google Spreadsheet, and save the script. And, please select the cells and run the function main().
Gists
This is a sample script for checking whether the cells on Google Spreadsheet have checkboxes using Google Apps Script.
When the checkboxes are used in Google Spreadsheet, there is the case that it is required to know whether the cells have the checkboxes. This sample script can be used for such the situation.
Sample script 1 This sample script can check whether all cells in “A1:B10” have the checkboxes. When all cells in “A1:B10” have the checkboxes, res is true.
Gists
This is a sample script for converting from the string value to the hex value, from the hex value to the byte array, from the byte array to the string value using Google Apps script.
When the creation of a signature for requesting and the encryption of data are used, there is the case that these conversions are required to be used. So I would like to introduce these scripts as sample scripts.
The sample scripts for using Google Forms API have been published by cschalk-goog. The scripts include the method for basically requesting to Forms API. This information will be very useful for a lot of users.
https://github.com/googleworkspace/apps-script-samples/tree/master/forms-api/demos/AppsScriptFormsAPIWebApp
Gists
This is a sample script for achieving the pseudo OnEdit trigger for Google Spreadsheet without the simple and the installable triggers using Google Apps Script.
Today, I saw a question at Stackoverflow. The goal of this question is as follows.
There is a Google Spreadsheet created by a service account. Goal is to use OnEdit trigger on this Spreadsheet. I thought a workaround for achieving this goal.
Gists
On October 12, 2021, Google Forms API had been announced. Ref On December 7, 2021, Google Forms API had been released as open beta. Ref In the current stage, when the users join the Early Adopter Program, they can use Google Forms API of the beta version. Ref
By using Google Forms API, what Google Forms service cannot achieve got to be able to be achieved by Google Forms API.
After V8 runtime was released, there was a bug that when the file is sent from HTML form to Google Apps Script side using google.script.run, the file blob was the invalid data.
From Apps Script Pulse by Martin Hawksey, it was found that the invalid blob of sending the file of HTML form to Google Apps Script side using google.script.run has finally been resolved. In this case, this script can be used with V8 runtime.
Gists
This is a sample script for achieving the pseudo OnEdit trigger for Google Document using Google Apps Script.
In the current stage, there is not OnEdit trigger for Google Document. Ref But I sometimes have the case that I want to use OnEdit trigger. So, as the current workaround, I created this sample script. I think that this workaround can be also used for Google Slides, Google Form and also Google Spreadsheet.
Gists
This is a workaround for converting the range ID to the range object on Google Spreadsheet using Google Apps Script.
When the named range is put to a cell as the hyperlink as follows,
the hyperlink is like #rangeid=123456789. When this link is clicked, it moves to the cells of the named range. So it is considered that this value of #rangeid=123456789 includes the information about the range of the named range.
Gists
This is a sample script for exporting all thumbnail images retrieved from Google Slides as a zip file using Google Apps Script.
Sample script Before you use this script, please enable Slides API at Advanced Google services. Ref
function myFunction() { const presentationId = "###"; // Please set Google Slides ID. const folderId = "###"; // Please set the folder ID. const outputFilename = "###"; // Please set the output filename.
Gists
This is a sample workaround for letting users running Google Apps Script on Google Spreadsheet without both authorizing the scopes and showing the script.
The flow of this workaround is as follows.
Create Web Apps created by Google Apps Script and deploy it as Web Apps. As the returned value, the XML data is returned. Your script can be included in this script. User put a formula of =IMPORTML("WebApps URL", "xpath") to a cell.
My post of “Sending Multiple Emails using Batch Request with Gmail API using Google Apps Script” was featured at Google Workspace Developer Newsletter on October 2021.
Gists
This is a sample script for uploading a movie file on Google Drive to YouTube using Google Apps Script.
Before you use these scripts, please enable YouTube API at Advanced Google services. Ref
Sample script 1 This sample script uses YouTube API at Advanced Google services.
function myFunction() { const fileId = "###"; // Please set the file ID of movie file on the Google Drive. const res = YouTube.
Gists
This is a sample script for updating the values of “Sheet A” with the values of “Sheet B” using Google Apps Script. I often see this situation at Stackoverflow and other sites. So, in this post, I would like to introduce the sample script using Google Apps Script.
Sample script function myFunction() { const ss = SpreadsheetApp.getActiveSpreadsheet(); const [src, dst] = ["Sheet1", "Sheet2"].map((e) => ss.getSheetByName(e)); const obj = src .
Gists
This is a sample script for reducing the table height of the table inserted from Google Spreadsheet to Google Slides using Google Apps Script.
Sample script Please copy and paste the following script to the script editor of Google Slides. This sample script uses Slides API. So, please enable Slides API at Advanced Google services. Ref
As the sample situation, this script supposes that a table is manually copied from Google Spreadsheet to the 1st slide of Google Slides.
Gists
This is a sample script for retrieving the glyph value from the list items of Google Document using Google Apps Script.
In the current stage, when the list is put to the Google Document, the count of glyph is automatically calculated. When the glyph values of the list items are tried to be retrieved using the manual operation and the script, unfortunately, the glyph values cannot be retrieved. Only the values of the list are retrieved.
Gists
This is a report about images put with “=IMAGE(IMAGE_URL)” function on Google Spreadsheet.
Experiment When “=IMAGE(IMAGE_URL)” is put to a cell “A1” on Spreadsheet, the image is shown in the cell as shown in the following image.
For this situation, when the following script is run,
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); const range = sheet.getRange("A1"); range.copyTo(range, { contentsOnly: true }); The following result is obtained. In this case, the formula is removed and an image can be seen as shown in the following image.
GitHub
This is a sample script for the file picker using Google Apps Script and Javascript without 3rd party. I had created the same sample script before. Ref But, in the case of that script, jQuery is used. And, only Google Drive of own account could be used. In this sample script, 3rd party of jQuery is not used, and also, not only Google Drive of your own account, but also Google Drive of the service account can be used.
Gists
This is a sample script for converting the values of Google Spreadsheet to an object using Google Apps Script.
Sample script function myFunction() { const sheetName = "Sheet1"; const [headers, ...rows] = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getDataRange().getValues(); const res = rows.map((r) => headers.reduce((o, h, j) => Object.assign(o, { [h]: r[j] }), {})); console.log(res); } When this script is run, the above sample image can be retrieved.
In this sample script, the 1st row of the sheet is used as the header row.
RichTextApp was updated to v1.3.0 v1.3.0 (October 20, 2021)
Added a new method of RangeToHTMLTableForSpreadsheet. In this method, the range on Google Spreadsheet is converted to a HTML table. Using this method, for example, you can send the specific range in the Spreadsheet as an email by including a HTML table. You can see the sample HTML table at https://jsfiddle.net/oq9x458e/. I used this method for this thread of Stackoverflow.
Gists
There is Class TextFinder in Spreadsheet service for Google Apps Script. Ref The TextFinder can search and replace the texts in the Spreadsheet using Google Apps Script. There is the method for createTextFinder in Class Spreadsheet, Class Sheet and Class Range. When these methods are used, you can search and replace the texts for all sheets in a Spreadsheet, the specific sheet, and the specific range in the specific sheet.
Gists
This is a sample script for 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 ?
Gists
This is a sample script for compiling the continuous numbers using Google Apps Script. For example, the values of [4, 5, 9, 3, 10, 5, 11, 7, 7, 13, 1] are converted to ["1","3-5","7","9-11","13"].
Sample script const compilingNumbers = (ar) => { const { values } = [...new Set(ar.sort((a, b) => a - b))].reduce( (o, e, i, a) => { if ( o.temp.length == 0 || (o.
Gists
This is a sample script for 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
Published: January 16, 2021
Kanshi Tanaike
Introduction At Stackoverflow, a lot of people post the questions and answers to the questions every day. There are various tags in Stackoverflow. A lot of discussions are performed at each tag. Their discussions bring the important information and are much useful for a lot of people. As one of tags, there is “google-apps-script”. I sometimes discuss at the questions with that tag. When we see the discussions, we can notice that the discussions are changed and progressed by the time, because “Google Apps Script” which is the origin of the tag is updated.
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
Introduction At Stackoverflow, a lot of people post the questions and answer to the questions every day. By this, there are a lot of important information in Stackoverflow. I have already reported “Trend of google-apps-script Tag on Stackoverflow” using the data retrieved from Stackoverflow. Ref. 1 It is found that the important statistical result can be obtained by analyzing the data on Stackoverflow. In this report, I would like to introduce the statistical analysis of duplicated questions for the google-apps-script tag in Stackoverflow.
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.
Another approach 10 Aug 2022: Report: Challenging Exporting Selected Cells on Spreadsheet as Image using Google Apps Script and Javascript
Old approach Gists
This is a sample script for converting a range in Google Spreadsheet as an image data using Google Apps Script. Unfortunately, there are no methods for directly converting the range in Google Spreadsheet as an image data in the built-in functions. So in this case, as a workaround, Charts Service is used.
Gists
These are 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.
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
This table shows all tags using together with the tag of “google-apps-script” at Stackoverflow. The tags are summarized every year. At 2020, the data is retrieved from January 1, 2020 to July 16, 2020. The number enclosed (###) means the number of use of the tag. When this table was a material for discussing about the current tags for “google-apps-script”, I’m glad.
Year Used Tag list 2008 2009 google-form(1),google-sheets(1),google-apps-script(1) 2010 google-apps-script(50),google-sheets(31),javascript(23),google-apps(4),custom-function(2),google-calendar-api(2),spreadsheet(2),google-docs-api(2),vba(2),google-docs(2),amazon(1),amazon-web-services(1),api(1),timezone(1),ms-office(1),xml-namespaces(1),xml(1),closures(1),google-sites(1),google-maps-api-3(1),array-formulas(1),google-sheets-formula(1),google-data-api(1),date-manipulation(1),datetime(1) 2011 google-apps-script(72),google-sheets(31),google-apps(16),javascript(15),google-docs(8),google-spreadsheet-api(4),google-form(3),google-api(3),google-docs-api(2),spreadsheet(2),google-fusion-tables(2),google-calendar-api(2),java(2),google-app-engine(2),number-formatting(1),file-upload(1),google-apps-marketplace(1),json(1),email(1),html(1),tabpanel(1),gmail(1),performance(1),user-interface(1),zend-framework(1) 2012 google-apps-script(1877),google-sheets(345),javascript(161),google-docs(63),google-sites(61),google-apps(54),google-spreadsheet-api(44),google-drive-api(44),gmail(40),google-calendar-api(31),google-docs-api(29),html(19),google-visualization(18),triggers(18),email(15),google-form(15),spreadsheet(14),urlfetch(13),gwt(13),oauth(13),custom-function(13),google-app-engine(12),user-interface(11),jquery(11),jdbc(10) 2013 google-apps-script(2606),google-sheets(765),javascript(413),google-drive-api(124),google-docs(120),google-form(97),google-spreadsheet-api(78),google-apps(77),gmail(58),google-sites(57),html(56),google-calendar-api(41),google-api(34),email(31),google-docs-api(26),jquery(24),arrays(23),triggers(22),forms(22),google-app-engine(21),google-visualization(21),user-interface(20),spreadsheet(20),json(19),google-caja(19) 2014 google-apps-script(2758),google-sheets(875),javascript(606),google-form(177),google-drive-api(168),google-docs(143),google-spreadsheet-api(90),google-apps(84),html(81),google-calendar-api(61),google-sites(56),gmail(54),email(53),jquery(49),arrays(45),forms(39),spreadsheet(35),triggers(35),excel(33),json(33),google-docs-api(28),google-api(28),google-app-engine(24),google-visualization(22),google-bigquery(22) 2015 google-apps-script(2781),google-sheets(1134),javascript(714),google-form(159),html(158),google-docs(127),google-apps(115),google-spreadsheet-api(98),google-drive-api(97),gmail(66),arrays(57),google-calendar-api(57),jquery(51),google-api(46),forms(40),spreadsheet(39),email(38),google-docs-api(36),json(35),triggers(33),google-sites(32),regex(32),google-visualization(30),php(23),css(20) 2016 google-apps-script(2888),google-sheets(1198),javascript(783),google-form(156),html(118),google-drive-api(117),google-apps(117),google-docs(77),arrays(74),google-spreadsheet-api(73),json(60),gmail(59),email(46),google-calendar-api(43),google-sheets-api(42),google-api(42),triggers(38),google-visualization(31),jquery(31),spreadsheet(31),google-docs-api(29),date(24),regex(23),forms(22),csv(22) 2017 google-apps-script(3873),google-sheets(1537),javascript(979),google-form(194),html(158),google-sheets-api(137),google-docs(127),google-apps(122),google-drive-api(122),google-spreadsheet-api(114),arrays(110),gmail(85),email(64),json(56),gmail-api(52),google-api(52),triggers(51),jquery(51),spreadsheet(48),google-calendar-api(42),regex(34),google-sites(31),forms(27),custom-function(27),excel(27) 2018 google-apps-script(4716),google-sheets(2062),javascript(1046),google-form(189),google-drive-api(189),google-docs(167),google-sheets-api(165),gmail-addons(155),html(134),triggers(129),gmail(120),arrays(111),google-calendar-api(79),json(68),google-app-maker(63),email(59),google-apps-script-addon(55),gmail-api(54),google-api(50),regex(50),custom-function(48),gsuite(47),google-bigquery(45),google-slides-api(43),web-applications(43) 2019 google-apps-script(6731),google-sheets(3300),javascript(1352),google-form(263),html(250),google-sheets-api(223),google-drive-api(216),google-sheets-formula(168),google-docs(158),gmail(154),web-applications(143),arrays(141),google-sheets-macros(134),triggers(128),google-apps-script-web-application(112),google-calendar-api(88),gmail-addons(85),google-apps-script-addon(85),json(82),google-apps(77),gsuite(76),google-api(69),google-app-maker(64),scripting(61),google-apps-script-editor(61) 2020 google-apps-script(4853),google-sheets(2503),javascript(1221),html(199),google-form(176),google-drive-api(161),google-apps-script-web-application(160),arrays(154),google-sheets-api(149),google-docs(124),google-sheets-formula(120),google-sheets-macros(90),gmail(86),json(85),google-apps-script-simple-triggers(69),google-calendar-api(68),urlfetch(66),google-apps-script-addon(65),spreadsheet(57),triggers(51),gsuite-addons(48),gs-installable-triggers(47),email(47),gsuite(47),google-apps-script-editor(45)
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.
Gists
This is the statistics for the tag gas on Stackoverflow. This statistics are retrieve from Stackoverflow using Stackexchange API. About 2020, the data is retrieved from 2020-01-01 to 2020-07-01.
This statistics data was obtained at 2020-07-15.
Measurement result Fig. 1: Year vs. Total questions, Answered, Solved and Closed questions
Table 1: Tags which were used together with gas tag for each year.
Year Used Tag list 2008 2009 gas(12),assembly(11),x86(4),gcc(3),nasm(2),linux(2),relative-addressing(1),disassembly(1),avr(1),macos(1),segments(1),inline-assembly(1),tdm-mingw(1),c(1),directive(1),mips(1),compiler-construction(1),gnu(1),stack(1),multithreading(1) 2010 gas(31),assembly(28),x86(9),ld(6),gcc(4),linux(4),arm(4),gnu(3),c(3),inline-assembly(2),linker(2),elf(1),gdb(1),macos(1),ubuntu(1),mips(1),binutils(1),iphone(1),x86-64(1),64-bit(1),macros(1),intel-syntax(1),compiler-construction(1),python(1),embedded(1) 2011 gas(46),assembly(37),x86(16),gcc(11),c(9),linux(6),gnu(4),64-bit(3),arm(3),nasm(3),att(3),inline-assembly(3),binutils(2),x86-64(2),register-allocation(2),system-calls(1),32-bit(1),cortex-a8(1),fibonacci(1),recursion(1),xcode(1),string(1),mips(1),dwarf(1),callstack(1) 2012 gas(59),assembly(46),gcc(15),x86(10),x86-64(6),macos(6),gnu(6),linux(6),att(5),c(5),arm(4),ld(4),system-calls(3),binutils(3),nasm(3),struct(2),elf(2),syntax(2),interrupt(2),fpu(2),inline-assembly(1),clang(1),webrtc(1),compilation(1),iphone(1) 2013 gas(70),assembly(55),x86(18),x86-64(16),gcc(14),linux(14),att(7),nasm(6),c++(5),inline-assembly(4),c(4),syntax(3),intel-syntax(2),printf(2),arm(2),intel(2),segmentation-fault(2),arrays(2),linker(2),i386(2),ffmpeg(1),ios(1),yasm(1),tasm(1),elf(1) 2014 gas(61),assembly(53),x86(17),c(9),gcc(8),x86-64(7),nasm(5),linux(5),gdb(4),arm(4),stack(3),masm(3),disassembly(3),att(3),inline-assembly(3),arrays(2),32-bit(2),cpu-registers(2),shellcode(1),sockets(1),intel-syntax(1),opcode(1),gnu(1),c++(1),scanf(1) 2015 gas(89),assembly(76),x86(25),att(15),c(13),gcc(12),x86-64(12),gnu(6),nasm(4),linux(4),gdb(3),inline-assembly(3),mips(3),macos(2),clang(2),elf(2),disassembly(2),macros(2),yasm(2),xcode(2),calling-convention(2),makefile(2),ld(2),arm64(2),intel-syntax(1) 2016 gas(97),assembly(86),x86(32),x86-64(17),gcc(16),linux(16),att(14),c(5),nasm(5),osdev(4),ld(4),x86-16(4),linker(4),c++(3),gnu(3),gdb(2),forth(2),riscv(2),dwarf(2),intel(2),memory-alignment(2),intel-syntax(2),macos(2),system-calls(2),real-mode(2) 2017 gas(73),assembly(64),x86(26),gcc(22),att(15),c(11),x86-64(11),intel-syntax(4),gnu(4),linux(4),arm(4),bootloader(4),64-bit(4),nasm(3),x86-16(3),ubuntu(2),osdev(2),inline-assembly(2),gdb(2),macos(2),ld(2),string(2),go(1),if-statement(1),objdump(1) 2018 gas(69),assembly(55),x86(24),x86-64(14),gcc(13),att(6),linux(6),arm(4),macos(3),intel-syntax(3),position-independent-code(3),forth(2),ubuntu(2),x86-16(2),gdb(2),scanf(2),inline-assembly(2),gnu(2),blockchain(2),nasm(2),ld(2),osdev(2),bootloader(2),cygwin(1),debugging(1) 2019 gas(74),assembly(58),x86(21),x86-64(15),gcc(10),att(10),c(8),linux(8),gnu(5),gdb(5),ld(4),macros(3),intel-syntax(3),inline-assembly(2),shared-libraries(2),macos(2),dwarf(2),elf(2),arm(2),addressing-mode(2),machine-code(2),bootloader(2),c-preprocessor(1),pdp-11(1),thumb(1) 2020 gas(68),assembly(55),x86(25),gcc(17),x86-64(16),att(13),intel-syntax(7),c(4),bootloader(4),x86-16(4),osdev(3),arm(3),linux(3),c++(3),gnu(3),linker(3),arm64(2),string-literals(2),powerpc(2),riscv(2),calling-convention(2),ld(2),macos(2),clang(1),abi(1) Summary From Fig.
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.
Gists
This is the statistics for the tag appscript on Stackoverflow. This statistics are retrieve from Stackoverflow using Stackexchange API. About 2020, the data is retrieved from 2020-01-01 to 2020-07-01.
Recently, this tag is modified by editing the tags. So I think that it has to be said that this statistics data is obtained at 2020-07-06.
Measurement result Fig. 1: Year vs. Total questions, Answered, Solved and Closed questions
Table 1: Tags which were used together with appscript tag for each year.
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.
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.
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.
v1.7.4 (March 11, 2020)
Recently, I noticed that new Google Apps Script project of the standalone script type cannot be created by the create method of Drive API. From now, in order to create the standalone Google Apps Script project, only Google Apps Script API is required to be used. Ref By this, I updated ggsrun. So the command for creating new GAS project is not changed.
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 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.
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
Published: January 10, 2020
Kanshi Tanaike
Introduction At Stackoverflow, a lot of people post the questions and answers to the questions every day. There are various tags in Stackoverflow. A lot of discussions are performed at each tag. Their discussions bring the important information and are much useful for a lot of people. As one of tags, there is “google-apps-script”. I sometimes discuss at the questions with that tag. When we see the discussions, we can notice that the discussions are changed and progressed by the time, because “Google Apps Script” which is the origin of the tag is updated.
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.
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].
Gists
Kanshi Tanaike
Introduction At Stackoverflow, a lot of people post the questions and answers to the questions every day. There are various tags in Stackoverflow. A lot of discussions are performed at each tag. Their discussions bring the important information and are much useful for a lot of people. As one of tags, there is “google-apps-script”. I sometimes discuss at the questions with that tag. When we see the discussions, we can notice that the discussions are changed and progressed by the time, because “Google Apps Script” which is the origin of the tag is updated.
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.
ggsrun was updated to v.1.7.1 v1.7.1 (December 30, 2018) A bug was removed. When a project is downloaded and zipped, there was a case that “createdTime” and “modifiedTime” of the project cannot be retrieved by Apps Script API. This was modified. You can check ggsrun at https://github.com/tanaikech/ggsrun.
ggsrun was updated to v.1.7.0 v1.7.0 (December 27, 2018) Manage permissions of files. Get Drive Information. By this, you can know the storage quotas. ggsrun got to be able to be used by not only OAuth2, but also Service Account. By this, using ggsrun, Google Drive for Service Account got to be able to be managed. Some modifications. You can check ggsrun at https://github.com/tanaikech/ggsrun.
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
Updated on June 22, 2024
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.
Usage Create the Service Account and retrieve JSON file. Put Scopes, private_key and client_email as an object. If you want to use impersonate email, please set the value of impersonate_email. Run the script. /** * ### Description * Get access token from service account.
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
ggsrun was updated to v.1.6.0 v1.6.0 (November 30, 2018) Although at ggsrun, files can be searched by filename and file ID, searching files using search query and regex couldn’t be done. From version 1.6.0, files got to be able to be searched using the search query and regex. $ ggsrun sf -q "### search query ###" -f "### fields ###" -r "### regex ###" Some modifications.
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
ggsrun was updated to v.1.5.2 v1.5.2 (November 4, 2018) About downloading folders, when files are downloaded from a folder, you can download Google Docs files with the mimeType you want. For example, when you download files from the folder, if -e txt is used, Google Docs are downloaded as the text file. When -e pdf is used, they are downloaded as the PDF file. Of course, there are mimeType which cannot be converted.
ggsrun was updated to v.1.5.1 v1.5.1 (November 2, 2018) Removed a bug. When a file information was retrieved, createdTime and modifiedTime couldn’t be seen and the information was incomplete. You can check ggsrun at https://github.com/tanaikech/ggsrun.
ggsrun was updated to v.1.5.0 v1.5.0 (October 27, 2018) From this version, ggsrun got to be able to download all files and folders in the specific folder in Google Drive. When all files are downloaded from a folder, the same folder structure of Google Drive is created to the local PC. $ ggsrun d -f folderName or folderId When the project file is downloaded, it is downloaded as a zip file.
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.
Recently, I was invited to Skillshare.com. They said that “will you teach about Google Apps Script?”. I thought that if I can be helpful for other users, I’m glad. So I joined to there and created a presentation. I would like to create more classed in the future. If those were helpful for you, I’m glad.
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
Overview This is sample scripts for copying files to a specific folder in Google Drive using Google Apps Script (GAS).
Description When the files in Google Drive are copied to a specific folder using GAS, most users will use makeCopy(destination). When the files are copied using makeCopy(), you might have already noticed that only the standalone projects cannot be copied to the specific folder. They are copied to the root folder (My Drive).
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
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
Updated on February 5, 2024 This is for adding the query parameters to the URL. These scripts can be also used for Javascript. When I created an endpoint with some query parameters, I had used the scripts of various patterns every time. Today, I prepared this sample script to unify them. If this is also useful for you, I’m glad.
Sample script (With V8 runtime): String.prototype.addQuery = function (obj) { return (this == "" ?
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.
At February 15th, 2018, ShapeApp was featured as one of “4 useful add-ons launched last month”.
ggsrun was updated to v.1.4.1 v1.4.1 (February 9, 2018) For uploading, the resumable-upload method was added. The resumable-upload method is automatically used by the size of file. “multipart/form-data” can upload files with the size less than 5 MB. “resumable-upload” can upload files with the size more than 5 MB. The chunk for resumable-upload is 100 MB as the default. Users can also give this chunk size using an option.
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.
ggsrun was updated to v.1.4.0 v1.4.0 (January 25, 2018)
Google Apps Script API was finally released. From this version, ggsrun uses this API. So ggsrun got to be able to use not only projects of standalone script type, but also projects of container-bound script type. I hope this updated ggsrun will be useful for you.
To users which are using ggsrun with v1.3.4 and/or less. For retrieving, downloading, creating and updating projects, Apps Script API is used.
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.
ggsrun was updated to v.1.3.4 v1.3.4 (January 2, 2018)
Added new option for downloading ‘bound-scripts’ of Google Sheets, Docs, or Forms file. When the bound-scripts are downloaded, the project name cannot be retrieved because Drive API cannot be used for the bound-scripts. So when the bound-scripts are downloaded, the project ID had been used previously. Such filename is not easily to be seen. By this additional option, users can give the filename when it downloads the bound-scripts.
I have reported because I found a bug for scopes of Slides. The detail is the following URL.
https://issuetracker.google.com/issues/71448583
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.