AppsScriptPulse

Benchmark: Process Costs for Searching Values with TextFinder in Google Sheets using Google Apps Script

Here, I would like to report the process costs for searching values in Spreadsheet using Google Apps Script (GAS). When the values are searched in Google Spreadsheet, the following 3 patterns can be considered. Ref

  1. Retrieve all values using getValues, and the values are searched from the retrieved array.
  2. Use TextFinder.
  3. Use Query language.

TextFinder is a relatively new addition to Google Apps Script with was released on April 5, 2019TextFinder is part of the Spreadsheet Service and provides methods for finding or replacing text within a range, sheet or spreadsheet. There have been some reports on Stack Overflow that the TextFinder methods can be relatively slow to execute compared to using native JavaScript techniques. This post by Tanaike explores the process time of TextFinder against other techniques. Click through to the source for the results and code for running the experiment.

Source: Benchmark: Process Costs for Searching Values in Spreadsheet using Google Apps Script

Converting Range in Google Spreadsheet as Image using Google Apps Script · tanaike

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.

Source: Converting Range in Google Spreadsheet as Image using Google Apps Script · tanaike

Retrieving Data from QR code on Google Slides using Google Apps Script · tanaike

This is a sample script for decoding a QR code put in Google Slides using Google Apps Script. In this case, Javascript is used at the opened dialog. And Canvas API and jsQR are used.

This is a nice example of how you can easily drop in existing JavaScript libraries into your Apps Script project by using a HTML dialog. The post also has some nice tricks for handing image data, in particular, from getDataFromQRCode() the image data is passed into the HtmlService:

const html = HtmlService.createTemplateFromFile("index");
html.image = JSON.stringify(blob.getBytes()); // adding image data
SlidesApp.getUi().showModalDialog(html.evaluate(), "sample");

This is added in client side with a printing scriptlet:

image.src = `data:image/png;base64,${btoa(String.fromCharCode(...new Uint8Array(Int8Array.of(...JSON.parse(<?= image ?>)).buffer)))}`; 

Another way you could achieve this is base64 encoding the image server side with Utilities.base64Encode().

Source: Retrieving Data from QR code on Google Slides using Google Apps Script · tanaike

Cropping Images in Google Slides using Google Apps Script

This is a sample script for cropping images in the Google Slides using Google Apps Script.

Useful snippet from Tanaike which shows how you can crop images in Google Slides by using the replace(blobSource, crop) method. The  post also highlights a limitation with the image manipulation methods available around crop centering.

Source: Cropping Images in Google Slides using Google Apps Script · tanaike

Converting SVG Format to PNG Format using Google Apps Script · tanaike

This is a sample script for converting the SVG image data to PNG image data using Google Apps Script. Unfortunately, in the current stage, there are no methods for directly converting the SVG to PNG in Google Drive service. But it can be achieved by Drive API

Nice trick to get .svg files stored in Google Drive in a raster format.

Source: Converting SVG Format to PNG Format using Google Apps Script · tanaike

Highlighting Row and Column of Selected Cell in Google Sheets using Google Apps Script · tanaike

This is a sample script for highlighting the row and column of the selected cell using Google Apps Script … and the OnSelectionChange event trigger

Nice little script solution which could be a very useful addition to your script projects as an accessibility feature.

Source: Highlighting Row and Column of Selected Cell using Google Apps Script · tanaike

Using Google Apps Script to disable custom functions assigned to Google Sheets buttons/images to prevent simultaneous execution · tanaike

This is a sample script for disabling the buttons put on Google Spreadsheet using Google Apps Script. When a script is run by clicking a button on Google Spreadsheet, there is the case that you don’t want to make users run the script in duplicate.

Nice little Google Apps Script snippet that prevents a function assigned to an image or drawing in Google Sheets from being run simultaneously. The clever bit is the code uses Google Apps Script to modify the function assigned to the button/image when it is running.

Source: Disabling Buttons Put on Google Spreadsheet using Google Apps Script · tanaike

Change Tab Detection on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script

Change Tab Detection on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script – submit.md

An example script from Kanshi TANAIKE which lets you test the new onSelectionChange(e) simple event trigger in Google Sheets.

The onSelectionChange(e) trigger runs automatically when a user changes the selection in a spreadsheet.

In the example shared this is used to detect the user changing Google Sheet tab.

Source: Change Tab Detection on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script

EncodeApp by tanaike (The Thinker)

EncodeApp is a GAS library for retrieving the encoding set (charset) and doing URL encode with the specific encoding set using Google Apps Script (GAS). – tanaikech/EncodeApp

Library’s project key: 1DsJdRQ9D6nXgbxVVvOroM3EYJOcB197Isvt2Sl4sziW3m9IqqeB9YoWy

Source: tanaikech/EncodeApp

Google Apps Script growth analysis based on Stackoverflow analysis

Source: Kanshi Tanaike

An interesting analysis prepared by Kanshi Tanaike of the ‘google-apps-script’ tag usage on Stackoverflow which shows that since 2016 there has been an increase in questions related to the tag with a bigger increase in 2019. Clicking through to the source post you’ll also see the data shows that new users asking questions about Google Apps Script is growing quicker than those who are answering them.

Source: Trend of google-apps-script Tag on Stackoverflow 2020

Figma to Google Slides using Google Apps Script

Image credit: Figma

In this sample script, all pages in the Figma file are retrieved and the retrieved pages are put to new Google Slides as the image.

This post is useful for not only showing how to insert images into Google Slides, but also for featuring Figma which might be a useful tool for G Suite Add-on developers looking for an easy way to prototype/design application interfaces.

Source: Figma to Google Slides using Google Apps Script · tanaike

Extracting website HTML tables using Google Apps Script

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

For those frustrated by the limitations of the Google Sheets =IMPORTHTML() function this might be a useful Google Apps Script alternative solution. As part of this the script fetches any publicly accessibly webpage and turns any tables into a 2D array.

Source: Parsing HTML using Google Apps Script · tanaike

Subscribe to Apps Script Pulse...