Skip to content

Instantly share code, notes, and snippets.

Kanshi TANAIKE tanaikech

View GitHub Profile
@tanaikech
tanaikech / submit.md
Last active Jul 8, 2020
Statistics of appscript Tag on Stackoverflow
View submit.md

Statistics of appscript Tag on Stackoverflow

This is the statistics for the tag appscript on Stackoverflow. This statistics are retrieve from Stackoverflow using Stackexchange API. About 2020, the data is retrieved from 2020-01-01 to 2020-07-01.

Recently, this tag is modified by editing the tags. So I think that it has to be said that this statistics data is obtained at 2020-07-06.

Measurement result

@tanaikech
tanaikech / submit.md
Last active Jul 8, 2020
Adjusting Text Length to Fit in Cell Width on Google Spreadsheet using Google Apps Script
View submit.md

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
tanaikech / submit.md
Created Jun 29, 2020
Report: Processing to Create New File to Specific Folder using Drive API
View submit.md

Report: Processing to Create New File to Specific Folder using Drive API

In this report, I would like to report for processing to create new file to the specific folder using Drive API. When the new file is created to the specific folder using Drive API, the property of parents with the value of folder ID is included in the request body of the method "Files: create". About this process, I had thought that the file is directly created to the specific folder. But, I could confirm that the new file is created by the following process.

  1. Create new file to the root folder.
  2. Move the created file to the specific folder.

These process is done by one API call. In this report, I would like to introduce the experimental result for confirming above process. In this case, Drive API v3 is used with Google Apps Script.

Sample script

@tanaikech
tanaikech / submit.md
Last active Jun 27, 2020
Update JSON object using jq
View submit.md

Update JSON object using jq

Achieving the following conversion using jq. In this conversion, when key2 is true, updated_ is added to the value of key1 and the value of key2 is 2 times and key2a is added by adding updated_ to the value as new property.

Conversion

From

[
@tanaikech
tanaikech / submit.md
Created Jun 26, 2020
Search Dialog Sample using TextFinder with Google Apps Script
View submit.md

Search Dialog Sample using TextFinder with Google Apps Script

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.

@tanaikech
tanaikech / submit.md
Created Jun 26, 2020
Workaround: Putting Multiple Hyperlinks to a Cell using Sheets API
View submit.md

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
tanaikech / submit.md
Created Jun 24, 2020
Managing Texts on Google Slides using Google Apps Script
View submit.md

Managing Texts on Google Slides using Google Apps Script

This is a sample script for managing the texts on Google Slides using Google Apps Script. Recently, I got the request like this. I published this here, because I thought that this might be also useful for other users.

Demo

In this demonstration, the text of {{baz}} on Google Slides are searched and replaced to other text, and also, the text style is changed.

@tanaikech
tanaikech / submit.md
Created Jun 23, 2020
Workaround: Correctly Exporting Charts on Google Spreadsheet as Images using Google Apps Script
View submit.md

Workaround: Correctly Exporting Charts on Google Spreadsheet as Images using Google Apps Script

This is a sample script for correctly exporting the charts on Google Spreadsheet as the images using Google Apps Script. In the current stage, using Google Apps Script, when the charts on Google Spreadsheet are exported as the images, it seems that the exported images are not the same with the original one on Google Spreadsheet. About this, today, I could notice that I had answered for 2 questions. Q1, Q2 And also, I had already been reported this at the issue tracker. Ref

In this post, as the current workaround, I would like to introduce the method for exporting the chart on Google Spreadsheet as the same images with the original one.

Sample situation

As a sample situation, the following chart is used.

@tanaikech
tanaikech / submit.md
Created Jun 19, 2020
Retrieving Difference Between 2 Arrays using Google Apps Script
View submit.md

Retrieving Difference Between 2 Arrays using Google Apps Script

This is a sample script for retrieving the difference between 2 arrays, which are the old values and the new values, using Google Apps Script. In my environment, I sometimes have the situation that it is required to retrieve the difference between 2 arrays. So I prepared this as a sample script. I think that this can be also used at Javascript and Node.js. If this was also useful for your situation, I'm glad.

Sample script

In this sample script, the difference of the values of oldValues and newValues is retrieved as an object.

const getDiffFrom2Arrays = (oldValues, newValues) => {
@tanaikech
tanaikech / submit.md
Last active Jun 12, 2020
Highlighting Row and Column of Selected Cell using Google Apps Script
View submit.md

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

You can’t perform that action at this time.