Gists

Experiment

It has investigated the differences between the documents of “Search for Files” for v2 and v3. The following table shows the comparison of v2 and v3 for “Valid fields for files.list”. The column of DriveApp.searchFiles() means whether the query can be used for DriveApp.searchFiles().

Result

As the result, it is considered that DriveApp.searchFiles() is used as Drive API v2 yet.

ggsrun was updated to v.1.3.4

• v1.3.4 (January 2, 2018)

  1. Added new option for downloading ‘bound-scripts’ of Google Sheets, Docs, or Forms file.
     • When the bound-scripts are downloaded, the project name cannot be retrieved because Drive API cannot be used for the bound-scripts. So when the bound-scripts are downloaded, the project ID had been used previously. Such filename is not easily to be seen. By this additional option, users can give the filename when it downloads the bound-scripts.
     • The usage is here
  2. Removed a bug.
     • When a project is downloaded, script ID in the project is added to the top of each downloaded script as a comment. There was a problem at the character using for the comment out. This was modified.

You can check this and download ggsrun at https://github.com/tanaikech/ggsrun.

I have reported because I found a bug for scopes of Slides. The detail is the following URL.

https://issuetracker.google.com/issues/71448583

Gists

This sample script is converted this sample script (javascript) to Google Apps Script. The point for converting is signature as shown in the following sample script.

• At Bitfinex API, after "/api/" + apiPath + nonce + rawBody is encrypted using HMAC SHA-384, the data of byte array is converted to HEX.
  • In Google Apps Script, there is no the method for this.
  • The data which was encrypted by Utilities.computeHmacSignature() is the bytes array of the signed hexadecimal.
  • On the other hand, at this sample script for javascript, the data which was encrypted by crypto.createHmac('sha384', apiSecret).update(signature).digest('hex') is the string of the unsigned hexadecimal.

In order to achieve above, I made the method of bytesToHex().

Gists

This sample script is for splitting string by N characters for batch-file. In this sample, after it retrieves N characters from the first character of STR, the N characters are removed from STR. This is repeated until the end of STR.

Sample script :

@ECHO OFF
SETLOCAL ENABLEDELAYEDEXPANSION
SET "STR=ABCDEFGHIJKLMNOPQRSTUVWXYZ"

REM Split STR by N characters
SET "N=2"

:LOOP
SET "RES=%RES%!STR:~0,%N%! "
SET "STR=!STR:~%N%!"
IF DEFINED STR GOTO LOOP
ECHO "%RES:~0,-1%"

Result :

• N=2

"AB CD EF GH IJ KL MN OP QR ST UV WX YZ"

• N=5

"ABCDE FGHIJ KLMNO PQRST UVWXY Z"

Reference :

• https://stackoverflow.com/questions/48018220/

RearrangeScripts was updated. Added “About” to the menu of add-on. By this, users can see the help document easily.

• Add-on
• GitHub

Gists

In this report, I would like to introduce a workaround for automatically recalculating custom functions on Spreadsheet.

1. Situation

The sample situation is below. This is a sample situation for this document.

• There are 3 sheets with “sheet1”, “sheet2” and “sheet3” of sheet name in a Spreadsheet.
• Calculate the summation of values of “A1” of each sheet using a custom function.
• Sample script of the custom function is as follows.

function myFunction(e) {
    var r = 0;
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    for (var i in sheets) {
        r += sheets[i].getRange(e).getValue();
    }
    return r;
}

2. Workaround for recalculating

When =myFunction("A1") is put in a cell, the custom function sums each “A1” of “sheet1”, “sheet2” and “sheet3”. But in this case, when “A1” of one of 3 sheets is changed, the custom function is not recalculated.

RearrangeScripts was published as an add-on application

Recently, I have reported RearrangeScripts for rearranging scripts in a GAS project. At that time, I got messages and mails from many developers. They said that you should publish this as an add-on. So, this was released. Now you can search “RearrangeScripts” as an add-on for Spreadsheet. If this is helpful for you, I’m happy.

• Add-on
• GitHub

Demo

Gist

This is a sample script for decoding Gmail body with Japanese language using Python.

msg = service.users().messages().get(userId='me', id=id).execute()
parts = msg['payload']['parts']
for e in parts:
    msg = base64.urlsafe_b64decode(e['body']['data']).decode('utf-8').encode('cp932', "ignore").decode('cp932')
    print(msg)

Gists

This is a document for explaining the difference between given values and retrieved values for shapes on Google Slides. When a shape is created to a slide using Slides API, most users give the size of height and width as pt. When the size is retrieved from the created shape as pt, the size is often difference from the given size.

For example, when a square shape is created by giving the height and width of 100 pt, the size which is retrieved from the created square becomes 99.99212598425197 pt for the height and width.