Skip to content

Instantly share code, notes, and snippets.

Avatar

Kanshi TANAIKE tanaikech

View GitHub Profile
@tanaikech
tanaikech / submit.md
Created Jul 10, 2022
Retrieving Values of Dropdown List of Smart Chips on Google Document using Google Apps Script
View submit.md

Retrieving Values of Dropdown List of Smart Chips on Google Document using Google Apps Script

This is a sample script for retrieving the values of dropdown list of the smart chips on Google Document using Google Apps Script.

At August 23, 2021, 3 Classes for retrieving the smart chips have been added to Google Apps Script. But, in the current stage, unfortunately, all values of the smart chips cannot be retrieved by the Classes. For example, the dropdown list of the smart chips cannot be retrieved using the 3 Classes.

When the type of dropdown list of the smart chips is retrieved using the 3 Classes, UNSUPPORTED is returned. And also, when Google Docs API is used, "content": "\n" is returned. So, in the current stage, there are no methods for directly retrieving the value of dropdown list of the smart chips. Of course, I believe that this will be resolved in the future update at the Google side.

In this report, as the current workaround, I would like to proposed a workaround for retrieving the valu

@tanaikech
tanaikech / submit.md
Last active Jul 6, 2022
Removing Invalid Named Range of #REF from Google Spreadsheet using Google Apps Script
View submit.md

Removing Invalid Named Ranges from Google Spreadsheet using Google Apps Script

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.

Unfortunately, in the current stage, it seems that the named ranges of #REF cannot be retrieved by Google Spreadsheet service (SpreadsheetApp) and Sheets API. By this, the named ranges of #REF cannot be directly removed by the current specification. And, it seems that this has already been reported to the Google issue tracker. Ref

@tanaikech
tanaikech / submit.md
Last active Jul 4, 2022
Converting A1Notation to GridRange and vice versa using Google Apps Script without any Scopes
View submit.md

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

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.

Sample script

/**
@tanaikech
tanaikech / submit.md
Created Jun 18, 2022
Shortening a Long URL using Firebase Dynamic Links API with Google Apps Script
View submit.md

Shortening a Long URL using Firebase Dynamic Links API with Google Apps Script

This is a sample script for shortening a long URL using Firebase Dynamic Links API with Google Apps Script.

IMPORTANT

Before you use this script, please create a new Firebase project and link it to your Google Cloud Platform Project. Ref And, please enable Firebase Dynamic Links API at the API console. And then, please create your API key from your Google Cloud Platform Project.

Sample script

@tanaikech
tanaikech / submit.md
Created Jun 16, 2022
Protecting Cells of Spreadsheet by Clicking Checkbox using Google Apps Script
View submit.md

Protecting Cells of Spreadsheet by Clicking Checkbox using Google Apps Script

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.

@tanaikech
tanaikech / submit.md
Created Jun 15, 2022
Parsing XML Data in Google Apps Script using IMPORTXML
View submit.md

Parsing XML Data in Google Apps Script using IMPORTXML

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

The flow of this workaround is as follows.

  1. Create a XML data in Google Apps Script on Google Drive as a XML file.
  2. Publicly share the XML file as a viewer.
@tanaikech
tanaikech / submit.md
Last active Aug 8, 2022
Benchmark: Process cost for HTML Template using Google Apps Script
View submit.md

Benchmark: Process cost for HTML Template using Google Apps Script

Introduction

When we use HTML in the Google Apps Script project, in order to show the values from the Google Apps Script side, the HTML template is used. When I used the HTML template with a large value, I understood that the process cost can be reduced by devising a script. In this report, I would like to introduce the process cost of the HTML template using the benchmark.

As the result, it was clearly found that when an HTML table is created with Google Apps Script and shows the HTML table with the HTML template, the process cost becomes low. And also, it was found that in order to reduce the process cost for using the HTML template, it is required to prepare the HTML data with the Google Apps Script side.

Experimental procedure

@tanaikech
tanaikech / submit.md
Last active Jun 23, 2022
Report: Publishing Various Google Docs with Same URL using Google Apps Script
View submit.md

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

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

@tanaikech
tanaikech / submit.md
Created May 30, 2022
Replacing Values in Cells on Google Spreadsheet using Google Apps Script
View submit.md

Replacing Values in Cells on Google Spreadsheet using Google Apps Script

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.

@tanaikech
tanaikech / submit.md
Created May 28, 2022
Uploading Files to Google Drive with Asynchronous Process using Python
View submit.md

Uploading Files to Google Drive with Asynchronous Process using Python

This is a sample script for uploading files to Google Drive with asynchronous process using Python.

Sample script

import aiohttp
import asyncio
import json