Overview

TableApp is a Google Apps Script library for managing Tables on Google Sheets.

Description

Recently, a new feature “Tables” was introduced to Google Sheets. Tables allow users to group data into structured tables with headers, filtering, and specific data types. While these can be managed via the Google Sheets API (v4), constructing the raw JSON requests for operations like creating, updating, and managing tables can be complex.

This library, TableApp, creates an object-oriented wrapper around the Google Sheets API, making it easy to manage Tables directly within Google Apps Script.

Gists

Abstract

This article introduces “TableApp,” a Google Apps Script library designed to simplify managing Google Sheets Tables. It addresses the complexity of the native Sheets API, providing an intuitive interface for creating, updating, and manipulating tables. Sample scripts and installation guides are included to ensure easy implementation.

Introduction

The introduction of Tables in Google Sheets has significantly enhanced data management capabilities. While these tables can be managed via the Sheets API, the process is often complex and verbose. I previously discussed this in my article, Managing Tables on Google Sheets using Google Apps Script.

Gists

Overview

Google Sheets now supports managing smart chips via its API, enabling retrieval and placement with Apps Script and other languages.

Description

Smart chips in Google Sheets and Docs are a powerful feature designed to enhance collaboration and information sharing. While there historically haven’t been built-in Google Apps Script methods to manage these chips, a workaround was previously developed to address this limitation (Ref).

However, Google has recently introduced direct methods for managing smart chips within the Google Sheets API (Ref). This report provides sample Google Apps Script code that demonstrates how to get and put smart chips on Google Sheets using these new API methods. Although these methods are currently exclusive to Google Sheets, their availability via the Sheets API means they can also be utilized with other programming languages, not just Google Apps Script. It will be anticipated that similar functionality will be extended to Google Docs in future updates.

Gists

Abstract

Google Sheets API now supports programmatic table management (create, delete, modify) as of April 29, 2025. This eliminates previous workarounds and enables direct control, including with Apps Script.

Introduction

Google Sheets tables can now be managed programmatically via the Sheets API, a significant update officially released on April 29, 2025. Ref I learned about this important development from Martin Hawksey’s Apps Script Pulse newsletter. Ref I am very grateful to Martin for bringing this to light. This update introduces the ability to programmatically manage tables directly through the Sheets API, enabling operations such as creating, deleting, and modifying tables and their properties. Previously, programmatic interaction with Sheets tables was limited and often required using workarounds for even simple management tasks, as explored in my earlier reports Ref and Ref. With this official API support, more robust and direct control is now possible. In this report, I will introduce how to manage tables on Google Sheets using the Sheets API, with examples implemented using Google Apps Script. It is worth noting, of course, that the Sheets API can also be used with other programming languages besides Apps Script.

Gists

Abstract

Gemini and Google Apps Script automate project roadmap creation in Google Sheets, including Gantt charts, improving efficiency and agile planning.

Introduction

When initiating a new project, a comprehensive roadmap is crucial for successful execution. Previously, I meticulously crafted these roadmaps manually, a time-consuming process. However, leveraging the advanced capabilities of Google’s Gemini, I’ve significantly streamlined this workflow. Gemini now assists in generating detailed project roadmaps, enhancing efficiency and accuracy. To further automate this process, I developed a Google Apps Script that dynamically constructs these roadmaps directly within Google Sheets, complete with integrated Gantt charts. This script facilitates the rapid generation of diverse project roadmaps, enabling agile planning and adaptation for future endeavors. This report details the functionality and implementation of this script, demonstrating its potential to optimize project planning and visualization.

Gists

Abstract

This report demonstrates the onSelectionChange simple trigger in Google Sheets by creating a slide puzzle. Selecting cells triggers script execution, enabling interactive gameplay.

Introduction

At Google Sheets, simple triggers can be used. When simple triggers are used, users can automatically run Google Apps Script based on actions in the spreadsheet. Among the simple triggers is onSelectionChange. This can automatically run a script when a cell is selected. In this report, as a simple sample script demonstrating the use of onSelectionChange, I created a slide puzzle in Google Sheets. The goal of the slide puzzle is to align the numbers by sliding them. Here, onSelectionChange is used to detect the selected number and slide it. The aim of this report is to learn about the simple trigger onSelectionChange.

Gists

Description

Now, Google Docs and Google Sheets can insert smart chips. Smart chips are very useful for easily inserting information like users, maps, files, and so on. However, unfortunately, at the current stage, smart chips cannot be directly managed using Google Apps Script. Specifically, the information within smart chips cannot be directly retrieved by Google Apps Script. Although I believe this will be resolved in a future update, there might be cases where you want to retrieve information from smart chips using Google Apps Script. This report introduces a workaround for achieving this.

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. MimeTypeApp bridges this gap by providing functions to convert unsupported MIME types to formats compatible with Gemini. With MimeTypeApp, you can streamline your workflows that involve parsing Gmail messages and their attachments for tasks like text extraction, summarization, or sentiment analysis using the Gemini API. This report introduces a sample script that demonstrates how to leverage MimeTypeApp to achieve this functionality. By leveraging Google Apps Script’s integration capabilities, MimeTypeApp allows you to create powerful applications that seamlessly connect Gmail, Spreadsheets (for storing results or extracted data), and the Gemini API.

Gists

Description

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

Gists

Abstract

Google Sheets’ new Tables feature enhances data organization but lacks direct management via Apps Script. This report proposes a workaround solution using Apps Script until native support arrives.

Introduction

Google Sheets recently introduced a new feature called Tables. Ref Tables offer a powerful way to organize and manage your data by transforming unformatted ranges into structured datasets with automatic headers, filtering options, and data validation capabilities. This not only improves the readability and maintainability of your spreadsheets but also allows for seamless integration with existing Google Sheets functions.

Gists

Abstract

This report showcases a practical application of Google Apps Script, demonstrating how new JavaScript methods can be used to create a script that automatically transfers selected rows between sheets in a Google Sheet.

Introduction

JavaScript, a fundamental pillar of contemporary web development, has experienced a significant rise in popularity due to its versatility and widespread adoption. As JavaScript’s influence has expanded, so too has Google Apps Script, a cloud-based scripting language constructed on the V8 JavaScript engine. This evolution has led to the introduction of novel methods and features, thereby expanding the capabilities of developers working within the Google Workspace ecosystem.

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. However, for smaller datasets, directly including data in the prompt or an uploaded file can be more efficient. Ref

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:

1. Create a Template Spreadsheet: This spreadsheet serves as a blueprint, containing essential elements like custom functions implemented using Google Apps Script.
2. Develop a Dashboard Spreadsheet: This centralized hub provides an overview of all user spreadsheets.
3. Clone and Share Spreadsheets: For each user, a copy of the template spreadsheet is created and shared with them. The URLs of these individual spreadsheets are then recorded in the dashboard.
4. Import Data Using IMPORTRANGE: The dashboard utilizes the IMPORTRANGE function to fetch data from individual user spreadsheets. This enables real-time updates on the dashboard as users edit their respective sheets.

While this manual process can be effective, it can become cumbersome and time-consuming, especially as the number of users and spreadsheets grows. To streamline this workflow and enhance efficiency, I’ve developed a Google Apps Script solution that automates many of these steps.

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. Since this approach might be helpful to others, I’m sharing it here.

RichTextAssistant was updated to v1.0.1.

• v1.0.1 (April 22, 2024)

  1. From oshliaer’s report, a bug was removed. In the current stage, when RichTextValueBuilder is used, it seems that when setTextStyle is used after setLinkUrl, the style of the hyperlink is removed while the link is kept. So, it is required to set setLinkUrl after setTextStyle.

You can see the detail information here https://github.com/tanaikech/RichTextAssistant

Gists

Abstract

Consolidate scattered cell references (A1Notation) in Google Sheets for efficiency. This script helps select cells by background color or update values/formats, overcoming limitations of large range lists.

Introduction

When working with Google Spreadsheets, there might be a scenario where you need to process scattered A1Notations (cell addresses in the format “A1”). This could involve selecting cells with specific background colors, updating cell values, or modifying cell formats.

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. It consists of multiple disconnected cells that form a single shape. Identifying such irregular shapes using a script can be challenging.

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

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. It is particularly effective for automating tasks within Google Spreadsheets. Google Spreadsheets offer cell protection capabilities, allowing you to define editable areas for different collaborators. Google Apps Script can effectively manage these protections. While online resources like Stack Overflow often feature questions related to spreadsheet protection, this report aims to introduce practical techniques through sample scripts. This will help users understand how to manage Google Spreadsheet protections using Google Apps Script.

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. This report addresses this challenge by introducing a novel similarity viewer, built upon the powerful trio of Gemini API, Google Spreadsheet, and Google Apps Script. This user-friendly tool allows us to visually represent the similarity of texts, transforming numerical data into an intuitive and easily digestible format.

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.Spreadsheets.batchUpdate({ requests }, spreadsheetId);
}

As additional information, in this case, when importFunctionsExternalUrlAccessAllowed: true is modified to importFunctionsExternalUrlAccessAllowed: false, an error like Cannot set import functions external URL access allowed from true to false. occurs. It seems that when access is allowed once, this cannot be canceled.

DocsServiceApp was updated to v1.2.2

• v1.2.2 (January 30, 2024)

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

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. So, please create your API key.

Gists

Abstract

One day, you might have a situation where you are required to manage rich texts in Google Spreadsheet using Google Apps Script. In this report, I would like to introduce the basic technique for managing rich texts with Google Apps Script.

Introduction

At Google Spreadsheet, rich texts can be used as a cell value. The rich texts can be also managed by Google Apps Script. Ref When I saw the official document related to the rich texts with Google Apps Script, I’m worried that it might be difficult a little for users to manage the rich texts using Google Apps Script.

Gists

Abstract

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

Gists

Description

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

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. You can understand the principle of this method from the following sample input and output images.

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. There might be a situation where you want to make the shared users input a value and upload a file without authorization of the scopes. In this report, I would like to introduce 2 patterns to achieve this goal.

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. In most cases, the OnEdit trigger trigger of the simple trigger or the installable trigger is used. When the OnEdit trigger is used, a function can be executed by manually editing a cell. When the function is executed, the function can be run by giving the event object including the information about the edited cell.

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. This report introduces a sample script for retrieving Comments including the Emoji reactions from Google Docs files.

HtmlFormApp was updated to v1.0.2.

• v1.0.2 (October 17, 2023)

  1. 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. So, when you run appendFormData(object, row) by the constant value of row, the submitted row is put into the same row. Please be careful about this.

You can see the detail information here https://github.com/tanaikech/HtmlFormApp

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. In order to test the below scripts, please create 2 Google Spreadsheets.

Gists

This is a sample script for moving the selected cell to the top left on Google Spreadsheet to focus it using Google Apps Script.

One day, there might be a case where you are required to focus the specific cell on Google Spreadsheet to help edit cells. In this post, I would like to introduce a sample script for achieving this.

Sample script

Please copy and paste the following script to the script editor of Google Spreadsheet, and save the script.

Gists

Description

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

Gists

This is a sample script for checking the overwrapped cells of multiple ranges on Google Spreadsheet using Google Apps Script.

When applications are developed, there might be a case that it is required to confirm whether 2 ranges on Google Spreadsheet are overwrapped. In this post, I would like to introduce a sample script for achieving this.

Method: getOverwrappedCells

The following script is a method of getOverwrappedCells. This is the main script of this post. This method returns the information about the overwrapped cells by inputting an array including the Class Range object. For example, as the default response, when each cell in “range1” and “range2” is overwrapped, true is returned. When { responseType: "list" } is used, the cell coordinates of the overwrapped cells are returned as an array.

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.

For example, under the condition that the basic filter is set to columns “B” and “D”, even when a checkbox of “B3” is checked, unfortunately, the basic filter is not automatically refreshed. In this case, it is required to manually refresh it.

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.getActiveSpreadsheet().getId(),
  { fields: "namedRanges,properties,sheets" }
);
Sheets.Spreadsheets.create(obj);

But, in the current stage, I noticed that this cannot be used. Because, in the current stage, the smart chips are implemented. In this case, Sheets API cannot be retrieved them as an object by the current specification. So, I would like to introduce a sample script as the current workaround.

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. This is the relative reference.

Gists

This is a sample script for retrieving the release notes of Google Apps Script and Google APIs from RSS using Google Apps Script.

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

Gists

Abstract

This report introduces the method for easily implementing HTML forms with a Google Spreadsheet as a database using Google Apps Script. There are 2 patterns for the HTML form using Google Apps Script. One is that an HTML form is put into the same Google Apps Script project. Another is that an HTML form is put to a different server from a Google Apps Script project. In this report, the methods for easily implementing both patterns are introduced using the sample scripts.

HtmlFormApp was updated to v1.0.1.

• v1.0.1 (May 29, 2023)

  1. When multiple files are uploaded, each URL was set as the hyperlink.

You can see the detail information here https://github.com/tanaikech/HtmlFormApp

Gists

This is a sample script for converting Google Spreadsheet to an HTML table using Google Apps Script.

There is the case that it is required to convert a sheet in a Google Spreadsheet to an HTML table. For example, there might be a situation that a sheet in a Google Spreadsheet is sent as an email including an HTML table. And, there might be a situation in which a sheet in a Google Spreadsheet is published to an HTML page including the converted HTML table. I have published the method for achieving this before. Ref But, in that case, the column width, the row height, merged cells, and the images in the cells couldn’t be used. When those are included in the script, the script becomes complicated. So, I couldn’t include it. But, recently, I have come up with a simple method for achieving this. In this post. I would like to introduce a sample script for converting a sheet in a Google Spreadsheet to HTML.

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.

Sample script 2:

const SAMPLE2 = v => {
  const [a, b, c] = v[0].map((_, c) => v.map(r => r[c]));
  const ch = [...new Set(a)];
  const rh = [...new Set(b)];
  const size = rh.length;
  const values = [...Array(Math.ceil(c.length / size))].map(_ => c.splice(0, size));
  const temp = [[null, ...rh], ...values.map((vv, i) => [ch[i], ...vv])];
  return temp[0].map((_, c) => temp.map(r => r[c]));
}

• In the sample, the source data is “F1:H21”. And, a custom function of =SAMPLE2(F1:H21) is put into “J1”.
• When this script is used, the result showing the above image (middle table to most right table) is obtained.

Gists

Abstract

It has already been known that Google Apps Script is a strong tool for managing Google Spreadsheets. When the values are retrieved and/or put for Google Spreadsheet, there is a case that the discontinuous cells are required to be used. This report suggests the Best Practices for processing the discontinuous cells on Google Spreadsheet. From the results of process costs, it could understand the usefulness of using the discontinuous cells with low cost using Sheets API and Class RangeList of Spreadsheet service with Google Apps Script.

Gists

These are sample scripts for putting an image into a cell of a Spreadsheet using Google Apps Script.

Sample 1

In this sample, the image is put into a cell using thumbnailLink retrieved by Drive API. So, when you test this, please enable Drive API at Advanced Google services. The image is put into cell “A1”.

function sample1() {
  const fileId = "###"; // Please set the file ID of the PNG image file on Google Drive.

  const url = Drive.Files.get(fileId).thumbnailLink.replace("=s220", "=s1000");
  const image = SpreadsheetApp.newCellImage().setSourceUrl(url).build();
  const range = SpreadsheetApp.getActiveSheet().getRange("A1");
  range.setValue(image);

  const value = range.getValue();
  console.log(value.getUrl()); // ---> null
  console.log(value.getContentUrl()); // --> Exception: Unexpected error while getting the method or property getContentUrl on object SpreadsheetApp.CellImage.
}

Sample 2

In this sample, the image is put into a cell using the data URL. The image is put into cell “A1”. In this case, I believe that when the data URL is used, this method will be able to be used for various situations.

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.
• You want to make the user copy the active Spreadsheet by clicking the button, and also, you want to automatically install the OnEdit trigger to installedOnEdit for the copied Spreadsheet, simultaneously.

This method is from “Using OnEdit Trigger to Google Spreadsheet by Hiding Google Apps Script from Other Users (Author: me)” and “Using OnEdit trigger on Google Spreadsheet Created by Service Account (Author: me)”.

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.
  const n = 3; // Code is created 5 times every 30 seconds.

  // Loading otpauth.umd.min.js (Ref: https://github.com/hectorm/otpauth)
  const cdnjs =
    "https://cdnjs.cloudflare.com/ajax/libs/otpauth/9.1.1/otpauth.umd.min.js";
  eval(UrlFetchApp.fetch(cdnjs).getContentText());

  const sheet = SpreadsheetApp.getActiveSheet();
  let c = n;
  while (c > 0) {
    const now = new Date();
    const code = new OTPAuth.TOTP({
      secret,
      algorithm: "SHA1",
      digits: 6,
      period: 30,
    }).generate();
    const [start, end] = [now, new Date(now.getTime() + 30000)].map((e) =>
      Utilities.formatDate(e, Session.getScriptTimeZone(), "HH:mm:ss")
    );
    sheet.appendRow([code, `Limit: ${start} - ${end}`]);
    SpreadsheetApp.flush();
    c--;
    if (c > 0) Utilities.sleep(30000);
  }
  sheet.appendRow(["Done."]);
}

Testing

When this script is run, the situation of the above demonstration is obtained.

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. In order to add and delete a text for the rich text in a cell, it is required to create a script while the current text style is kept. This is actually complicated. In this post, I would like to introduce the enriched management of rich text on Google Spreadsheet using Google Apps Script. In order to enrich the management of Rich Text using Google Apps Script, I created a library RichTextAssistant.

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. Because, for example, in the current stage, when the text of rich text of a cell is changed using a script, all text styles are cleared. In order to add and delete a text for the rich text in a cell, it is required to create a script while the current text style is kept. This is actually complicated. From this situation, when a script for supporting editing the rich text in a cell is published, it will be useful for a lot of users. So, I created it and published it as “RichTextAssistant” of a Google Apps Script library.

Gists

This is a sample script for the folder picker using jsTree with Google Apps Script and Javascript.

I have already published “File Picker using Google Apps Script and Javascript without 3rd party”. In this post, jsTree is used.

Usage

1. Install Google Apps Script library.

In this script, “FilesApp” of my Google Apps Script library is used. So, please install it. You can see how to install it at here.

Gists

This is a sample script for importing Microsoft Excel (XLSX) data to Google Spreadsheet using a custom function with Google Apps Script.

Usage

1. Install SheetJS library.

Please copy the script of the SheetJS library from https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js, and paste the script to the script editor of Google Spreadsheet, and save the script.

In this case, I would like to recommend the following flow.

1. Add a new script to the script editor. For example, the filename is SheetJS.
2. Copy and paste the script of https://cdn.sheetjs.com/xlsx-latest/package/dist/xlsx.full.min.js to the added script file, and save the script.
3. Copy and paste the following sample script of the custom function to the other script file (It’s the default script file (Code.gs)).

2. Prepare custom function.

Please copy and paste the following script to the script editor of Google Spreadsheet (this is the same Spreadsheet installed SheetJS library.) and save the script. And, please reopen Google Spreadsheet.

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.

  const range = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName(sheetName)
    .getDataRange();
  range
    .createTextFinder("^'{1,}")
    .useRegularExpression(true)
    .replaceAllWith("");
  range.setValues(range.getValues());
}

• In this script, for example, '001, '''001, 'abc, and '''abc are converted to 1, 1, abc, and abc, respectively.

Reference:

• I answered this sample script to this thread on Stackoverflow.

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.

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. In the current stage, even when Sheets API is used, the values cannot be retrieved from multiple Spreadsheets by one API call. In this report, I would like to introduce the method for achieving this with the low process cost using Google Apps Script.

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. By this, unfortunately, the hyperlink of the cell cannot be retrieved. This has already been reported in the Google issue tracker. Ref

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”. Here, this method is used.

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. Only the below standalone script can be used.

Gists

This is a sample script for rearranging columns on Google Spreadsheet using Google Apps Script.

Sample script

In this sample script, the initial columns of “header1”, “header2”, “header3”, “header4” and “header5” are rearranged to “header2”, “header5”, “header1”, “header4”, “header3”. This result can be seen at the above image.

As an important point, in this script, the header titles in the 1st row are used. Please be careful about this.

Gsits

This is a sample script for retrieving the values from a publicly shared Google Spreadsheet using an API key with Javascript.

Sample script

In this sample script, googleapis for Javascript is used.

<script async defer src="https://apis.google.com/js/api.js" onload="handleClientLoad()"></script>
<script>
function handleClientLoad() {
  const apiKey = "###"; // Please set your API key.
  const spreadsheetId = "###"; // Please set your Spreadsheet ID.

  gapi.load('client', async () => {
    await gapi.client.init({ apiKey, discoveryDocs: ["https://sheets.googleapis.com/$discovery/rest?version=v4"] });
    const { result } = await gapi.client.sheets.spreadsheets.values.get({ spreadsheetId, range: "Sheet1" });
    console.log(result);
  });
}

• In this sample, I used Method: spreadsheets.values.get. Of course, in this case, you can use the methods of Method: spreadsheets.get, Method: spreadsheets.values.batchGet.

Gists

In this post, I would like to introduce 2 sample scripts for copying and deleting the dimension groups in Google Spreadsheet using Google Apps Script.

Unfortunately, in the current stage, all dimension groups cannot be copied by one action, and also, all dimension groups cannot be deleted by one action. In this post, these can be achieved using Google Apps Script.

These sample scripts use Sheets API. So, please enable Sheets API at Advanced Google services.

Gists

This is a workaround for detecting to edit Google Spreadsheet using Sheets API with the service account.

It has already been found that when Google Spreadsheet is edited using Sheets API, this can be detected by the installed OnChange trigger. For example, when a Spreadsheet is edited using Sheets API with the access token retrieved from your Google account, the event object of the installable OnChange trigger includes your email address and nickname. With this information, you can know the user who edited the Spreadsheet. However, when the Spreadsheet is edited using Sheets API with the access token retrieved from the service account, unfortunately, the email of the service account is not included in the event object of the OnChange event. It is considered that this is the current specification.

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.

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)

  1. 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.com/tanaikech/DocsServiceApp

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. So, I created this sample script.

Gists

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

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)

  1. 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.com/tanaikech/DocsServiceApp

Gists

This is a sample script for detecting cells with the quote prefix in Google Spreadsheet using Google Apps Script.

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

Gists

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

I have also experienced this before. And, in that time, I noticed that there are differences in process costs for the methods of Google Apps Script under this condition.

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.getDataRange().getValues()
  );

  // Create an array for updating the destination sheet.
  const srcObj = srcValues.reduce((o, r) => ((o[r[0]] = r), o), {});
  const values = [
    ...dstValues.map(([a, ...v]) => {
      if (srcObj[a]) {
        const temp = srcObj[a];
        delete srcObj[a];
        return temp;
      }
      return [a, ...v];
    }),
    ...Object.values(srcObj),
  ];

  // Update the destination sheet.
  dstSheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

• In this sample script, “Sheet1” and “Sheet2” are the source and destination sheets, respectively.

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. And, please install OnChange trigger to the function installedOnChange.

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. In this case, this method cannot be used with the custom function. This is the current specification.

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. So, I have discussed this in his question.

Gists

This is a sample script for clearing the discrete cell values on multiple sheets using Google Apps Script.

There might be a case where you want to clear the values of the discrete cells in the multiple sheets using Google Apps Script. In this post, I would like to introduce the efficient script for achieving this.

Sample script 1

Please copy and paste the following script to the script editor of the Google Spreadsheet you want to use.

Gists

This is a sample script for creating the user’s dashboard by inputting the user name and password using Web Apps with Google Apps Script. In this case, Google Spreadsheet is used as a database of the dashboard.

Usage

1. Create a Google Apps Script project.

In order to use Web Apps, please create a new Google Apps Script project. In this case, please create a new Spreadsheet and open the script editor of Spreadsheet.

Gists

This is a sample script for checking the exchange rate using GOOGLEFINANCE with Google Apps Script.

Recently, I have published a report of “Report: Obtaining Values from GOOGLEFINANCE using Google Apps Script”. Ref In this post, I would like to introduce a sample script for checking the exchange rate using Google Apps Script.

Sample script

Please copy and paste the following script to the script editor of Spreadsheet.

// When this script is run, a trigger for executing "checkCurrency" function is installed.
function setTrigger() {
  const functionName = "checkCurrency";
  const trigger = ScriptApp.getScriptTriggers().find(t => t.getHandlerFunction() == functionName);
  if (trigger) {
    ScriptApp.deleteTrigger(trigger);
  }
  ScriptApp.newTrigger(functionName).timeBased().everyMinutes(10).create();
}

function checkCurrency() {
  const threshold = ###; // Please set the the threshold value you want to check.
  const ticker = "CURRENCY:USDJPY"; // Please set ticker you want to check.
  const email = "###"; // Please set the email address you want to notice.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheets()[0];
  const range = sheet.getRange(sheet.getLastRow() + 1, 1, 1, 2);
  range.setFormulas([["=NOW()", `=GOOGLEFINANCE("${ticker}")`]]);
  range.offset(0, 0, 1, 1).setNumberFormat("yyyy/MM/dd H:mm:ss");
  SpreadsheetApp.flush();
  const values = range.getValues();
  range.setValues(values);
  if (values[0][1] > threshold) { // Or if you want to check when the current value is less than the threshold, please modify this to values[0][1] < threshold
    MailApp.sendEmail({
      to: email,
      subject: `Report: Value is over the threshold (${threshold})`,
      htmlBody: `<p>Current value is ${values[0][1].toFixed(2)}.</p><p><a href="${ss.getUrl()}">Open Spreadsheet</a>`,
    });
  }
}

• In this script, when the function of checkCurrency is run, the current value of exchange rate is retrieved as a fixed value without using the formula. When the value is over threshold, an email is sent.

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. So, in this report, I would like to introduce the management of images on Google Spreadsheet using the sample scripts of Google Apps Script.

Gists

This is a sample script for removing the invalid named range of #REF from Google Spreadsheet using Google Apps Script.

Issue and workaround

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

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. Also, this sample script can be used for Javascript and Node.js.

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 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.google.com/spreadsheets/d/{spreadsheetId}/edit.

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.getActiveSpreadsheet();
  const sheetId = ss.getSheetByName("Sheet1").getSheetId(); // Please set the sheet name you want to use this script.

  const requests = Object.entries(obj).map(([k, v]) => ({
    findReplace: {
      find: `(${k})`,
      matchCase: true,
      sheetId,
      replacement: `[${v}]`,
    },
  }));
  requests.push({
    findReplace: { find: ",", matchCase: true, sheetId, replacement: "" },
  });
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}

• In this script, one sheet is used. Also, you can use this script to all sheets in a Google Spreadsheet.

Reference

• FindReplaceRequest

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, ...obj.map((o) => headers.map((k) => o[k] || null))];

  SpreadsheetApp.getActiveSheet()
    .clearContents()
    .getRange(1, 1, values.length, values[0].length)
    .setValues(values);
}

Testing

When this script is run, the following result is obtained. It is found that the header row is expanded by including all keys.

Gists

This is a sample script for retrieving the text positions in the text data using Google Apps Script.

For example, in order to set the rich text style the part of text from the text data, this sample script will be useful.

Sample situation 1

The sample situation is as follows.

sample1, sample2, sample3, sample4, sample5
sample1, sample2, sample3, sample4, sample5
sample1, sample2, sample3, sample4, sample5

In this sample, the text positions of sample2 and sample5 are retrieved from this sample text data.

Gists

This is a sample script for retrieving and parsing the XML data from Google Workspace Update Blog and putting it to Google Spreadsheet using Google Apps Script.

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

Gists

This is a report for obtaining the values from GOOGLEFINANCE using Google Apps Script. When I tested to retrieve the values from GOOGLEFINANCE function on Google Spreadsheet using Google Apps Script, I noticed that the values can be retrieved.

When I had tested this before, I had got the value of #N/A. About retrieving the values from GOOGLEFINANCE function on Google Spreadsheet, I had known “Historical GOOGLEFINANCE data no longer accessible outside of Google Sheets”. By this situation, #N/A had been returned when the value had been retrieved using a script.

Gists

This is a sample script for expanding the rows in Google Spreadsheet using Google Apps Script. The sample situation is as follows.

Sample situation

Input

Output

Sample script

function myFunction() {
  const expandedColumns = [2, 3, 4, 5]; // Please set the expanded columns you expect.
  const delimiter = "\n"; // Please set the delimiter.
  const srcSheetName = "Sheet1"; // Please set the source sheet name.
  const dstSheetName = "Sheet2"; // Please set the destination sheet name.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [srcSheet, dstSheet] = [srcSheetName, dstSheetName].map((s) =>
    ss.getSheetByName(s)
  );
  const [head, ...values] = srcSheet.getDataRange().getValues();
  const res = [
    head,
    ...values.flatMap((r) => {
      const { v, max } = expandedColumns.reduce(
        (o, c, i) => {
          const s = r[c - 1].split(delimiter);
          o.v[c - 1] = s;
          const len = s.length;
          if (i == 0) {
            o.max = len;
          } else {
            o.max = o.max > len ? o.max : len;
          }
          return o;
        },
        { v: {}, max: 0 }
      );
      return [...Array(max)].map((_, j) =>
        r.map((c, k) => (!v[k] ? c : v[k][j] || null))
      );
    }),
  ];
  dstSheet.getRange(1, 1, res.length, res[0].length).setValues(res);
}

• When this script is run, the above sample situation can be obtained.
• For example, when you change const expandedColumns = [2, 3, 4, 5]; to const expandedColumns = [5];, only the column “E” is expanded.

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.stringValue"
    }
  }];
  Sheets.Spreadsheets.batchUpdate({ requests }, spreadsheetId);
}

Sample 2

This sample puts 1000 values like “sample#” to the cells of “B1:B1000” using the batchUpdate method. This request body includes 1000 requests. When this script is run, one API quota is used.

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
        ? columnIndexToLetter(a - 1) + String.fromCharCode(65 + (n % 26))
        : "";

    return columnIndexToLetter(idx + 1);
  });

const samples = ["A", "Z", "AA", "AZ", "ZZ"];
const res = increase(samples);
console.log(res); // <--- [ 'B', 'AA', 'AB', 'BA', 'AAA' ]

• When this script is used, the column letters of ["A", "Z", "AA", "AZ", "ZZ"] is increased by one. As the result, [ 'B', 'AA', 'AB', 'BA', 'AAA' ] is obtained.

Testing

• jsfiddle.net

Gists

This is a sample script for analyzing the responses from Grid Items of Google Form using Google Apps Script.

In this sample situation, all responses are retrieved from Grid Items of Google Form, and the average values of each row of each question are calculated. And, the result is put on the Spreadsheet.

Sample situation

Input: Sample Google Form

The sample Google Form is as follows.

Output: Sample Spreadsheet

The sample output is as follows.

Gists

Introduction

On March 14, 2022, it reported about “Google Sheets doubles cell limit”. Ref By this update, now, the users can use 10,000,000 cells in a Google Spreadsheet. This is great news for us. When I tried to handle 10,000,000 cells in a Google Spreadsheet using Google Apps Script, it was found that there were various important points. In this report, I would like to introduce the important points for handling 10,000,000 cells in Google Spreadsheet using Google Apps Script.

Gists

This is a sample Google Apps Script for processing the values in Google Spreadsheet. In this sample situation, each row is merged using the same header title.

In this sample script, the sample input and output situations are as follows.

Sample situation

Input:

Output:

Sample script

In this sample script, this sample can be used as the custom function.

function SAMPLE(values) {
  const headers = [
    ...new Set(
      values
        .map((r) => [...r])
        .flatMap((r) =>
          [...Array(Math.ceil(r.length / 2))].map((_) => r.splice(0, 2)[0])
        )
    ),
  ].filter(String);
  const obj = values.reduce((o, r) => {
    [...Array(Math.ceil(r.length / 2))].forEach((_) => {
      const [k, v] = r.splice(0, 2);
      if (k && headers.includes(k)) o[k] = o[k] ? [...o[k], v] : [v];
    });
    return o;
  }, {});
  const v = headers.map((e) => [e, ...obj[e]]);
  return v[0].map((_, c) => v.map((r) => r[c]));
}

• In this sample script, the following flow is used.
  1. Retrieve values from cells.
  2. Retrieve header values.
  3. Create an object for populating values for each header.
  4. Convert the object to an array.
  5. Tanspose the created array.

Reference

• I proposed this sample script to this thread of Stackoverflow.

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.

  const transpose = (ar) => ar[0].map((_, c) => ar.map((r) => r[c]));
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [src, dst] = [srcSheetName, dstSheetName].map((s) =>
    ss.getSheetByName(s)
  );
  const values = src.getDataRange().getValues();
  const temp = [
    ...transpose(values)
      .reduce(
        (m, [a, ...b]) => m.set(a, m.has(a) ? [...m.get(a), ...b] : [a, ...b]),
        new Map()
      )
      .values(),
  ];
  const res = transpose(temp);
  dst.getRange(1, 1, res.length, res[0].length).setValues(res);
}

• In this sample script, the following flow is used.
  1. Retrieve values from “Sheet1”.
  2. Tanspose the retrieved values.
  3. Create an array using Map object.
  4. Tanspose the created array.
  5. Put the array to “Sheet2”.

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. So, I thought that I would like to introduce in my blog.

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. And, please install OnChange trigger to the function onChange().

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

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. Recently, a bug of the built-in parser from the Javascript side to the Google Apps Script side for parsing the form object from the HTML form had been removed. Ref But, in the current stage, this bug is removed for only Web Apps. Unfortunately, for the dialog and sidebar, this bug has never been removed. And also, unfortunately, the built-in parser from the Javascript side to the Google Apps Script side cannot be used for the multiple files of the input tag. And, this cannot be used except for google.script.run. For example, when the HTML form including the files is submitted using “action” of the form tag, the file content is not included. And then, when the form object is retrieved, it is required to parse the object and put it in the Spreadsheet. From these situations, I thought that when this process can be run using the libraries, that might be useful for users. So I created this.

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.

I would like to expect to resolve these in the future update.

Gists

This is a workaround for inserting the non-public image of Google Drive using IMAGE function in a cell on Google Spreadsheet using Google Apps Script.

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

Gists

This is a sample script for importing a CSV data by keeping the number formats of cells on Google Spreadsheet using Google Apps Script.

When a CSV data is manually put using the default UI using the browser, it seems that the number formats of all cells cannot be kept. In order to import the CSV data to the cells with keeping the number formats, it is required to use a script as a workaround. In this workaround, Google Apps Script is used.

Gists

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

Gists

This is a sample script for inverting the selected ranges on Google Spreadsheet using Google Apps Script.

I have the case that I want to invert the selected ranges on Google Spreadsheet. This sample script can be achieved this goal using Google Apps Script.

Sample script

Please copy and paste the following script to the script editor of Google Spreadsheet, and save the script. And, please select the cells and run the function main(). By this, the selected ranges are inverted.

Gists

This is a sample script for checking whether the cells on Google Spreadsheet have checkboxes using Google Apps Script.

When the checkboxes are used in Google Spreadsheet, there is the case that it is required to know whether the cells have the checkboxes. This sample script can be used for such the situation.

Sample script 1

This sample script can check whether all cells in “A1:B10” have the checkboxes. When all cells in “A1:B10” have the checkboxes, res is true.

Gists

This is a sample script for achieving the pseudo OnEdit trigger for Google Spreadsheet without the simple and the installable triggers using Google Apps Script.

Today, I saw a question at Stackoverflow. The goal of this question is as follows.

• There is a Google Spreadsheet created by a service account.
• Goal is to use OnEdit trigger on this Spreadsheet.

I thought a workaround for achieving this goal.

Issue and workaround:

In the current stage, unfortunately, when the owner of Google Spreadsheet is the service account. The Google Apps Script cannot be run. By this, even when onEdit function is put to the script editor, this script cannot be run. It seems that this is the current specification of the Google side. Unfortunately, in the current stage, this goal cannot be directly achieved.

Gists

This is a sample script for retrieving the data from Content-Type of ’text/event-stream’ using Javascript and Google Apps Script.

In the current stage, UrlFetchApp of Google Apps Script cannot be retrieved the data from Content-Type of ’text/event-stream’. This sample script can be used for achieving this as a workaround.

This sample script uses EventSource. So this script uses a dialog on Google Docs files (This sample uses Google Spreadsheet.).

Usage

1. Prepare Spreadsheet.

Create new 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. But, unfortunately, in the current stage, there is no methods for directly retrieving the range object from the range ID. In this sample sample script, as a workaround, the range ID is converted to the range object using Google Apps Script.

Gists

This is a sample workaround for letting users running Google Apps Script on Google Spreadsheet without both authorizing the scopes and showing the script.

The flow of this workaround is as follows.

1. 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.
2. User put a formula of =IMPORTML("WebApps URL", "xpath") to a cell.

By this flow, you can achieve to let users running Google Apps Script on Google Spreadsheet without both authorizing the scopes and showing the script.

Gists

This is a sample script for updating the values of “Sheet A” with the values of “Sheet B” using Google Apps Script. I often see this situation at Stackoverflow and other sites. So, in this post, I would like to introduce the sample script using Google Apps Script.

Sample script

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [src, dst] = ["Sheet1", "Sheet2"].map((e) => ss.getSheetByName(e));
  const obj = src
    .getRange("A2:B" + src.getLastRow())
    .getValues()
    .reduce((o, [a, b]) => ((o[a] = b), o), {});
  const values = dst
    .getRange("A2:A" + dst.getLastRow())
    .getValues()
    .map(([b]) => [obj[b] || ""]);
  dst.getRange(2, 2, values.length, 1).setValues(values);
}

Of course, this situation can be also achieved with the built-in formula of Spreadsheet. For example, when the above image is used, the same result with the column “B” can be obtained at the column “C” by putting a formula of =ARRAYFORMULA(VLOOKUP(A2:A11,Sheet2!A2:B6,2)) to the cell “C2”.

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. By this, I thought that this file picker will be useful for various scenes.

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.

• const res = rows.map((r) => headers.reduce((o, h, j) => Object.assign(o, { [h]: r[j] }), {})); can be also replaced with const res = rows.map((r) => headers.reduce((o, h, j) => (o[h] = r[j], o), {}));.

Gists

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

Gists

This is a sample script for putting all response values from Google Form to Google Spreadsheet using Google Apps Script.

Sample script

Please copy and paste the following script to the script editor of Google Spreadsheet and set the variables of formId and sheetName.

function myFunction() {
  const formId = "###"; // Please set the Google Form ID.
  const sheetName = "Sheet1"; // Please set the sheet name of sheet you want to put the values.

  // Retrieve all response values from Google Form.
  const form = FormApp.openById(formId);
  const headers = ["date", ...form.getItems().map(e => e.getTitle())];
  const values = [headers, ...form.getResponses().map((f) => {
    const timeStamp = f.getTimestamp();
    return f.getItemResponses().reduce((o, i) => {
      const r = i.getResponse();
      return Object.assign(o, {
        [i.getItem().getTitle()]: Array.isArray(r) ? r.join(",") : r,
      });
    }, { date: timeStamp });
  }).map((o) => headers.map((t) => o[t] || ""))];

  // Put the values to Spreadsheet.
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getRange(1, 1, values.length, values[0].length).setValues(values);
}

• When this script is run, all response values are retrieved from Google Form and put them to the Spreadsheet.

Note

• At Google Form, when the empty answer is submitted, the question has no value. By this, it is required to consider this. So at first, the titles are retrieved from the items, and the values are created using the item titles. I thought that this might be an important point.

References

• This sample script was used for the following threads.
  • Storing all forms answers in one single file
  • How can I get the responses directly form a Google Form and send then to a new spreadsheet?

Gists

In this report, it has investigated the large decimal numbers and the exponential notation for Google Spreadsheet. When the large decimal numbers are put to the Spreadsheet, the Spreadsheet automatically sets the display value using the exponential notation. In this report, the result when the values are retrieved by Spreadsheet service and Sheets API is shown.

Sample script

At first, please create new Spreadsheet and open the script editor. And please copy and paste the following script. And, please enable Sheets API at Advanced Google services.

Gists

This is a sample script for compiling the continuous numbers using Google Apps Script. For example, the values of [4, 5, 9, 3, 10, 5, 11, 7, 7, 13, 1] are converted to ["1","3-5","7","9-11","13"].

Sample script

const compilingNumbers = (ar) => {
  const { values } = [...new Set(ar.sort((a, b) => a - b))].reduce(
    (o, e, i, a) => {
      if (
        o.temp.length == 0 ||
        (o.temp.length > 0 && e == o.temp[o.temp.length - 1] + 1)
      ) {
        o.temp.push(e);
      } else {
        if (o.temp.length > 0) {
          o.values.push({ start: o.temp[0], end: o.temp[o.temp.length - 1] });
        }
        o.temp = [e];
      }
      if (i == a.length - 1) {
        o.values.push(
          o.temp.length > 1
            ? { start: o.temp[0], end: o.temp[o.temp.length - 1] }
            : { start: e, end: e }
        );
      }
      return o;
    },
    { temp: [], values: [] }
  );
  return values;
};

// Please run this function.
function main() {
  const ar = [4, 5, 9, 3, 10, 5, 11, 7, 7, 13, 1]; // This is sample values.

  const values = compilingNumbers(ar);
  console.log(values);

  const res = values.map(({ start, end }) =>
    start == end ? start.toString() : `${start}-${end}`
  );
  console.log(res);
}

When this script is run, console.log(values) and console.log(res) show [{"start":1,"end":1},{"start":3,"end":5},{"start":7,"end":7},{"start":9,"end":11},{"start":13,"end":13}] and ["1","3-5","7","9-11","13"], respectively. From this result, it is found that the continuous numbers were compiled.

Gists

This is a sample script for retrieving the list of all emails of Microsoft account and putting them to Google Spreadsheet using Google Apps Script.

I updated OnedriveApp to v1.2.0 by adding 1 method for retrieving the access token and 7 methods for managing emails of Microsoft account. By this, the emails got to be able to be gotten and sent using Microsoft account using OnedriveApp with Google Apps Script.

Gists

• Published: September 15, 2021

• Updated: September 17, 2021

  • From the discussions, added data by changing the wait time of LockService for Web Apps.

Kanshi Tanaike

Introduction

When the users try to write to a Spreadsheet using a form, the developers have to consider the concurrent submission from the form. For example, when multiple users submit the data with the form simultaneously, all data are possibly not to be saved to the Spreadsheet. So it is considered that it is important to know the information about the concurrent writing to Google Spreadsheet using a form. In this report, such a situation was investigated.

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. In this report, the hidden rows and showing rows by the filter view are retrieved using Sheets API.

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.

1. Retrieving 1st empty cell of specific column by searching from TOP of sheet

2. Retrieving 1st NON empty cell of specific column by searching from BOTTOM of sheet

Gists

This is a sample script for creating the colorful buttons on Google Spreadsheet on Google Apps Script.

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

Gists

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

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. And please run openSidebar(). By this, the side bar is opened to the Spreadsheet.

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. Or the TextFinder is used in a loop. In this case, the process cost is higher than that using Sheets API. By using the bathUpdate method of Sheets API, the process cost is reduced.

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. The proposed script can measure the process cost for the built-in functions and custom functions on Google Spreadsheet. The script is created with using Google Apps Script. When the process cost can be known for the built-in functions and custom functions, it is considered that it will be useful for the developers using Google Spreadsheet.

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. Other users cannot be edited.

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. By this, the buttons are created to the cells.

Gists

This is a sample script for retrieving all values from all sheets from URL of 2PACX- of Web Published Google Spreadsheet using Python.

In this post, it supposes that the Google Spreadsheet has already been published for Web. Ref

Flow

The flow of this method is as follows.

1. Retrieve XLSX data from the URL of web published Google Spreadsheet as BytesIO data.
   • The URL is like https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml.
2. XLSX data is parsed with openpyxl.
3. Retrieve all values from all sheets.

Sample script

Please set spreadsheetUrl.

Gists

This is a sample script for retrieving all values from all sheets from URL of 2PACX- of Web Published Google Spreadsheet using Node.js.

In this post, it supposes that the Google Spreadsheet has already been published for Web. Ref

Before you use this sample script, please install SheetJS js-xlsx.

Flow

The flow of this method is as follows.

1. Retrieve XLSX data from the URL of web published Google Spreadsheet as the buffer data.
   • The URL is like https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml.
2. XLSX data is parsed with SheetJS js-xlsx.
3. Retrieve all values from all sheets.

Sample script

Please set spreadsheetUrl.

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.

1. From the client side, send the URL of web published Google Spreadsheet to the Web Apps created by Google Apps Script.
   • The URL is like https://docs.google.com/spreadsheets/d/e/2PACX-###/pubhtml.
2. Return the byte array of XLSX data from the Web Apps to the client side.
3. At the client side, the XLSX data is parsed with SheetJS js-xlsx.
4. Retrieve all values from all sheets.

Usage

1. Prepare script.

Please copy and paste the following script to the script editor and save it.

Gists

This is a sample script for sorting the cells on Google Spreadsheet with the background colors using Google Apps Script.

Unfortunately, in the current stage, it seems that sort(sortSpecObj) of Class Range cannot directly sort by the background colors of cells. But when Sheets API is used, this goal can be achieved. Here, “SortRangeRequest” of the method of “spreadsheets.batchUpdate” in Sheets API is used.

Flow

The flow of this sample script is as follows.

Gists

This is a sample script for setting alternate background colors for rows in Google Spreadsheet using Google Apps Script.

It has already been known when the conditional formatting rule and custom function are used, this can be simply achieved. Ref In this report, I would like to introduce the method for using Google Apps Script.

Sample script

In this sample script, the values of column “A” are checked.

function myFunction() {
  const colors = { color1: "#f4cccc", color2: "#d9ead3" };

  const sheet = SpreadsheetApp.getActiveSheet();
  const ranges = sheet
    .getRange("A1:A" + sheet.getLastRow())
    .getValues()
    .reduce(
      (o, [b], i) => {
        if (b != o.temp) {
          o.temp = b;
          o.c++;
        }
        o[["color1", "color2"][o.c % 2]].push(`${i + 1}:${i + 1}`);
        return o;
      },
      { color1: [], color2: [], c: 0, temp: "" }
    );
  Object.entries(colors).forEach(([k, v]) =>
    sheet.getRangeList(ranges[k]).setBackground(v)
  );
}

• The process cost of this script can be reduced by using RangeList.

Reference

• Class RangeList

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. functionForSheet1 is the function name of function which is run when Sheet1 is edited.
    Sheet2: functionForSheet2,
  };

  const sheetName = e.range.getSheet().getSheetName();
  if (sheets[sheetName]) {
    sheets[sheetName](e);
  }
}

// In this sample, when Sheet1 is edited, this function is run.
function functionForSheet1(e) {
  console.log("Sheet1 was edited.");

  // do something
}

// In this sample, when Sheet2 is edited, this function is run.
function functionForSheet2(e) {
  console.log("Sheet2 was edited.");

  // do something
}

• In this sample script, when the cells of “Sheet1” and “Sheet2” are edited, functionForSheet1() and functionForSheet2() are run, respectively. When other sheets are edited, no functions are run.
• In this sample script, onEdit of the simple trigger is used. When the functions you want to run include the methods which are required to authorize, please use the installable trigger.

Note

• This method can be also used for other event triggers like OnChange, OnSelectionChange and so son.

References

• Simple Triggers
• Installable Triggers
• Event Objects

Gists

In order to print and export as PDF file, this is a sample script for converting Spreadsheet to Spreadsheet which has the custom header and footer.

In this sample script, DocsServiceApp, which is Google Apps Script library, is used. And, in this case, the Spreadsheet with the custom header and footer is created as new Spreadsheet.

Before you use this script, please install DocsServiceApp and enable Drive API at Advanced Google services.

Overview

This is a Google Apps Script library for supporting Document service, Docs API, Spreadsheet service, Sheets API, Slides service and Slides API. The aim of this library is to compensate the processes that they services cannot achieve.

Description

The Google services, which are Document service, Docs API, Spreadsheet service, Sheets API, Slides service and Slides API, are growing now. But, unfortunately, there are still the processes that they cannot done. I created this GAS library for supporting the Google services.

Another approach

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

Old approach

Gists

This is a sample script for converting a range in Google Spreadsheet as an image data using Google Apps Script. Unfortunately, there are no methods for directly converting the range in Google Spreadsheet as an image data in the built-in functions. So in this case, as a workaround, Charts Service is used.

Gists

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

Pattern 1

In this pattern, the drawing is used as the button.

Gists

This is a sample script for directly using the request body of the string JSON for Google APIs with googleapis of golang.

At googleapis for golang, when Google API is used, it is required to create the request body like this sample script. I have several contacts for creating about such request body. I thought that such script might be a bit difficult for users. I thought that when the string JSON object is directly used for this, it might be useful. So I would like to introduce about this. When this was useful for your situation, I’m glad.

Gists

This is a sample script for setting the number format of cells on Google Spreadsheet using batchUpdate in Sheets API with golang. In this case, googleapis for golang is used. The script of the authorization can be seen at the official document.

Sample script

In this script, the number format of the column “A” is changed to yyyy-mm-dd hh:mm:ss. And, please include https://www.googleapis.com/auth/spreadsheets to the scopes.

sheetId := 12345678  // Please set the sheet ID which is not Spreadsheet ID. Please be careful this.

repeatCellRequest := &sheets.RepeatCellRequest{
    Fields: "userEnteredFormat.numberFormat",
    Range: &sheets.GridRange{
        SheetId:          int64(sheetId),
        StartRowIndex:    0,
        StartColumnIndex: 0,
        EndColumnIndex:   1,
    },
    Cell: &sheets.CellData{
        UserEnteredFormat: &sheets.CellFormat{
            NumberFormat: &sheets.NumberFormat{
                Pattern: "yyyy-mm-dd hh:mm:ss",
                Type:    "DATE",
            },
        },
    },
}
requestBody := &sheets.BatchUpdateSpreadsheetRequest{
    Requests: []*sheets.Request{&sheets.Request{
        RepeatCell: repeatCellRequest,
    }},
}
resp, err := srv.Spreadsheets.BatchUpdate(bookID, requestBody).Do()
if err != nil {
    log.Fatal(err)
}
fmt.Printf("%#v\n", resp)

References

• Method: spreadsheets.batchUpdate
• RepeatCellRequest
• How to format a cell with Google Sheets API v4 and Go?
  • I answered above sample script at this thread.

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. But the direction for calculating the length of texts in the unit of pixel cannot be directly used. Because as a test case, when I compared the text length (pixel) calculated from the font size and the cell width (pixel), those were different. By this, here, I would like to introduce a workaround using other direction. The base flow of this workaround is as follows.

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. I believe that such methods will be added in the future update. I think that when this is implemented, it might be added to TextFormatRun.

Gists

This sample script is for expanding a1Notations using Google Apps Script. This was updated from this sample script.

Sample script

function expandA1Notations_(a1Notations, maxRow, maxColumn) {
  maxRow = maxRow || "1000";
  maxColumn = maxColumn || "Z";

  // Ref: https://stackoverflow.com/a/21231012/7108653
  const columnToLetter = column => {
    let temp,
      letter = "";
    while (column > 0) {
      temp = (column - 1) % 26;
      letter = String.fromCharCode(temp + 65) + letter;
      column = (column - temp - 1) / 26;
    }
    return letter;
  };
  const letterToColumn = letter => {
    let column = 0,
      length = letter.length;
    for (let i = 0; i < length; i++) {
      column += (letter.charCodeAt(i) - 64) * Math.pow(26, length - i - 1);
    }
    return column;
  };

  const reg1 = new RegExp("^([A-Z]+)([0-9]+)$");
  const reg2 = new RegExp("^([A-Z]+)$");
  const reg3 = new RegExp("^([0-9]+)$");
  return a1Notations.map(e => {
    const a1 = e.split("!");
    const r = a1.length > 1 ? a1[1] : a1[0];
    const [r1, r2] = r.split(":");
    if (!r2) return [r1];
    let rr;
    if (reg1.test(r1) && reg1.test(r2)) {
      rr = [r1.toUpperCase().match(reg1), r2.toUpperCase().match(reg1)];
    } else if (reg2.test(r1) && reg2.test(r2)) {
      rr = [
        [null, r1, 1],
        [null, r2, maxRow]
      ];
    } else if (reg1.test(r1) && reg2.test(r2)) {
      rr = [r1.toUpperCase().match(reg1), [null, r2, maxRow]];
    } else if (reg2.test(r1) && reg1.test(r2)) {
      rr = [[null, r1, maxRow], r2.toUpperCase().match(reg1)];
    } else if (reg3.test(r1) && reg3.test(r2)) {
      rr =
        Number(r1) > Number(r2)
          ? [
              [null, "A", r2],
              [null, maxColumn, r1]
            ]
          : [
              [null, "A", r1],
              [null, maxColumn, r2]
            ];
    } else if (reg1.test(r1) && reg3.test(r2)) {
      rr = [r1.toUpperCase().match(reg1), [null, maxColumn, r2]];
    } else if (reg3.test(r1) && reg1.test(r2)) {
      let temp = r2.toUpperCase().match(reg1);
      rr =
        Number(temp[2]) > Number(r1)
          ? [
              [null, temp[1], r1],
              [null, maxColumn, temp[2]]
            ]
          : [temp, [null, maxColumn, r1]];
    } else {
      throw new Error(`Wrong a1Notation: ${r}`);
    }
    const obj = {
      startRowIndex: Number(rr[0][2]),
      endRowIndex: rr.length == 1 ? Number(rr[0][2]) + 1 : Number(rr[1][2]) + 1,
      startColumnIndex: letterToColumn(rr[0][1]),
      endColumnIndex:
        rr.length == 1
          ? letterToColumn(rr[0][1]) + 1
          : letterToColumn(rr[1][1]) + 1
    };
    let temp = [];
    for (let i = obj.startRowIndex; i < obj.endRowIndex; i++) {
      for (let j = obj.startColumnIndex; j < obj.endColumnIndex; j++) {
        temp.push(columnToLetter(j) + i);
      }
    }
    return temp;
  });
}

// When you use this script, please run main().
function main() {
  const a1Notations = ["A1:E3", "B10:W13", "EZ5:FA8", "AAA1:AAB3"];
  const res = expandA1Notations_(a1Notations);
  console.log(res);
}

Result

[
    ["A1","B1","C1","D1","E1","A2","B2","C2","D2","E2","A3","B3","C3","D3","E3"],
    ["B10","C10","D10","E10","F10","G10","H10","I10","J10","K10","L10","M10","N10","O10","P10","Q10","R10","S10","T10","U10","V10","W10","B11","C11","D11","E11","F11","G11","H11","I11","J11","K11","L11","M11","N11","O11","P11","Q11","R11","S11","T11","U11","V11","W11","B12","C12","D12","E12","F12","G12","H12","I12","J12","K12","L12","M12","N12","O12","P12","Q12","R12","S12","T12","U12","V12","W12","B13","C13","D13","E13","F13","G13","H13","I13","J13","K13","L13","M13","N13","O13","P13","Q13","R13","S13","T13","U13","V13","W13"],
    ["EZ5","FA5","EZ6","FA6","EZ7","FA7","EZ8","FA8"],
    ["AAA1","AAB1","AAA2","AAB2","AAA3","AAB3"]
]

Retrieve duplicated cells

When this script is used, the duplicated cells can be retrieved as follows.

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.fromCharCode(temp + 65) + letter;
      column = (column - temp - 1) / 26;
    }
    return letter;
  };

  // Expand range1.
  const ar = rangeList1.getRanges().reduce((ar, r) => {
    const startRow1 = r.getRow();
    const endRow1 = startRow1 + r.getNumRows();
    const startColumn1 = r.getColumn();
    const endColumn1 = startColumn1 + r.getNumColumns();
    for (let j = startRow1; j < endRow1; j++) {
      for (let k = startColumn1; k < endColumn1; k++) {
        ar.push(columnToLetter(k) + j);
      }
    }
    return ar;
  }, []);

  // Expand range2.
  const map = rangeList2.getRanges().reduce((m, r) => {
    const startRow2 = r.getRow();
    const endRow2 = startRow2 + r.getNumRows();
    const startColumn2 = r.getColumn();
    const endColumn2 = startColumn2 + r.getNumColumns();
    for (let j = startRow2; j < endRow2; j++) {
      for (let k = startColumn2; k < endColumn2; k++) {
        m.set(columnToLetter(k) + j, null);
      }
    }
    return m;
  }, new Map());

  return ar.filter(e => map.has(e));
};

const main = () => {
  const range1 = ["B3:C7", "D6:E9"]; // Please input range1 as a1Notation.
  const range2 = ["A2:B3", "C7:D10"]; // Please input range2 as a1Notation.

  const sheet = SpreadsheetApp.getActiveSheet();
  const res = getOverwrappedRanges_(
    sheet.getRangeList(range1),
    sheet.getRangeList(range2)
  );
  console.log(res); // <--- ["B3","C7","D7","D8","D9"]
};