tanaikech

Adjusting Text Length to Fit in Cell Width on Google Spreadsheet using Google Apps Script

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.

1. Retrieve the cell width.
2. Automatically resizing the cell width using autoResizeColumn.

tanaikech

Workaround: Putting Multiple Hyperlinks to a Cell using Sheets API

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.

So, in this post, I would like to introduce for putting the multiple hyperlinks using Sheets API as a current workaround.

tanaikech

Highlighting Row and Column of Selected Cell using Google Apps Script

This is a sample script for highlighting the row and column of the selected cell using Google Apps Script. For this, the OnSelectionChange event trigger is used.

Demo

Sample script

tanaikech

Detecting Quickly Checked Checkboxes on Google Spreadsheet using Google Apps Script

Abstract

This is a report for detecting quickly checked checkboxes on Google Spreadsheet using Google Apps Script. It supposes that when the checkbox is checked, a function of Google Apps Script is run by the event trigger. In this case, when the multiple checkboxes on Google Spreadsheet are checked quickly, the script cannot be run for all checked checkboxes, because of the response speed of the event trigger. It is considered that to understand the response of event trigger is useful for creating the application for Spreadsheet. In this report, the detection of quickly checked checkboxes on Google Spreadsheet using Google Apps Script has been investigated. From this result, it led to understanding the response of event trigger.

Demo

tanaikech

Characteristics of Response for onSelectionChange

Abstract

I have already reported about "Change Tab Detection on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script". Ref It is considered that when the situation which uses the event trigger of onSelectionChange is thought, the response speed is important. So, here, I investigated the characteristics of response for the event trigger of onSelectionChange.

Demo

tanaikech

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

onSelectionChange has been released at April 22, 2020. But this couldn't be used at the released day. But now, I could confirm that this got to be able to be used. So in order to test this event trigger, I prepared a simple sample script. This is a sample script for detecting the change tab on Google Spreadsheet using onSelectionChange Event Trigger with Google Apps Script.

Demo

Usage

tanaikech

Updated Specification of Google Spreadsheet: Multiple Hyperlinks to a Cell

Recently, it seems that the specification of Google Spreadsheet was updated. Before this, when a cell has only one hyperlink. In this case, the hyperlink was given to a cell using =HYPERLINK("http://www.google.com/", "Google") as following figure.

But by the recent update, a cell got to be able to have multiple hyperlinks as following figure. In this case, the hyperlinks are set by the RichTextValue object.

tanaikech

Hiding and Deleting Rows and Columns on Google Spreadsheet using Google Apps Script

These are the sample scripts for hiding and deleting rows and columns on Google Spreadsheet using Google Apps Script. I sometimes see the questions for hiding and deleting rows and columns on Spreadsheet at Stackoverflow. So here, I would like to introduce the sample scripts for this.

In this case, when the process costs of the scripts created by using Spreadsheet service and Sheets API are compared, the cost of script created by Sheets API is much lower than that of script created by Spreadsheet service. So when the rows and columns of your Spreadsheet is large and you can use Sheets API, I recommend to use Sheets API.

Sample situation

As the sample situation, the following sample Spreadsheet is used.

jrichardsz

https://sites.google.com/site/scriptsexamples/custom-methods/gs-objects/examples

function makeObjectsExample() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange().getValues();  
  var dataObjects = Objapp.rangeToObjects(range);
  Logger.log(dataObjects[1]['headerA']);
}


function camelArrayExample(){

tanaikech

Dynamically Updating Custom Menu of Google Spreadsheet using Google Apps Script

This is a sample script for dynamically updating the custom menu of Google Spreadsheet using Google Apps Script.

Demo

In this demonstration, when the Spreadsheet is opened, 5 functions added to the custom menu. You can see that when a column is added and deleted, the custom menu is updated.