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.

Gists

This is a sample script for retrieving clientId using Google Apps Script.

var accessToken = ScriptApp.getOAuthToken();
var url = "https://www.googleapis.com/oauth2/v3/tokeninfo?access_token=" + accessToken;
var params = {
  method: "post",
  headers: {"Authorization": "Bearer " + accessToken}
};
var res = UrlFetchApp.fetch(url, params).getContentText();
var clientId = JSON.parse(res).azp;
Logger.log(clientId)

Gists

This is a sample script for downloading a file using a button of dialog box on Google Docs (Spreadsheet, Document and Slides).

Please use this sample script at script editor on Google Docs (Spreadsheet, Document and Slides). And please set file ID in the script.

FLow :

The flow of this sample script is as follows.

1. Run dialog().
   • Open a dialog.
2. When users click a download button, retrieve file ID at GAS side.
3. Create download URL from the file ID. Download URL and filename are sent to download(obj) of Javascript.
4. Create a tag for downloading and click it at Javascript side.

• By this, users can download the file of file ID.

Code.gs

function dialog() {
  var html = HtmlService.createHtmlOutputFromFile('download');
  SpreadsheetApp.getUi().showModalDialog(html, 'Sample dialog'); // If you use other Google Docs, please modify here.
}

function getDownloadUrl() {
  var id = "### file id ###";

  var file = DriveApp.getFileById(id);
  return {
    url: file.getDownloadUrl().replace("?e=download&gd=true",""),
    filename: file.getName()
  };
}

download.html

<input type="button" value="download" onclick="getUrl()" />
<script>
  function getUrl() {
    google.script.run.withSuccessHandler(download).getDownloadUrl();
  }

  function download(obj) {
    var d = document.createElement('a');
    d.href = obj.url;
    d.download = obj.filename;
    d.click();
  }
</script>

Gists

Introduction

Recently, I have introduced a GAS library and a CLI tool for rearranging files in GAS project. Those are RearrangeScripts and ggsrun. Because today, I found a new way for rearranging files, I would like to introduce it although there is a restriction.

By the recent Google-update, users become able to create folders in GAS project. The new way uses this. At first, please see the demonstration GIF animation.

Demo

Gists

This sample script is for retrieving an instance of user-interface environment for Spreadsheet, Document and Slides. When I create applications which use user interface (for example, sidebar, dialog and so on), the user interface can be used for Spreadsheet, Document and Slides. If the application doesn’t use the methods depend on Spreadsheet, Document and Slides, this script can give 3 choices to users.

function getUi() {
    var ui;
    try {
        ui = SpreadsheetApp.getUi();
    } catch(e) {}
    try {
        ui = DocumentApp.getUi();
    } catch(e) {}
    try {
        ui = SlidesApp.getUi();
    } catch(e) {}
    return ui || null;
}

function main() {
    var ui = getUi();
    if (ui) {
        ui.alert('Hello, world!');
    }
}

Gists

This is a sample script for combining and mixing 2 objects. Each object is an array which included a dictionary type. When the key of the dictionary object is the same, the values are mixed.

This can be also used for Google Apps Script.

Input

var obj1 = [
    {"key1": ["value1a1", "value1a2"]},
    {"key1": ["value1aa1", "value1aa2"]},
    {"key2": ["value2a1", "value2a2"]},
    {"key3": ["value3a1", "value3a2"]},
];
var obj2 = [
    {"key1": ["value1b1", "value1b2"]},
    {"key3": ["value3b1", "value3b2"]},
    {"key3": ["value3bb1", "value3bb2"]},
    {"key4": ["value4b1", "value4b2"]},
];

Output

[
    {"key1": ["value1a1", "value1a2", "value1b1", "value1b2", "value1aa1", "value1aa2"]},
    {"key2": ["value2a1", "value2a2"]},
    {"key3": ["value3a1", "value3a2", "value3b1", "value3b2", "value3bb1", "value3bb2"]},
    {"key4": ["value4b1", "value4b2"]}
]

Sample script :

Javascript :

function mixture(obj1, obj2) {
    Array.prototype.push.apply(obj1, obj2);
    var temp = [];
    var res = [];
    obj1.forEach(function(e, i){
        temp[i] = !~temp.indexOf(Object.keys(e)[0]) ? Object.keys(e)[0] : false;
        if (temp[i]) {
            res.push(e);
        } else {
            res.forEach(function(f, j){
                if (Object.keys(f)[0] == Object.keys(e)[0]) {
                    Array.prototype.push.apply(res[j][Object.keys(f)[0]], e[Object.keys(e)[0]]);
                }
            });
        }
    });
    return res;
}

var obj1 = [
    {"key1": ["value1a1", "value1a2"]},
    {"key1": ["value1aa1", "value1aa2"]},
    {"key2": ["value2a1", "value2a2"]},
    {"key3": ["value3a1", "value3a2"]},
];
var obj2 = [
    {"key1": ["value1b1", "value1b2"]},
    {"key3": ["value3b1", "value3b2"]},
    {"key3": ["value3bb1", "value3bb2"]},
    {"key4": ["value4b1", "value4b2"]},
];
var res = mixture(obj1, obj2);
console.log(JSON.stringify(res))

CoffeeScript :

This is a sample script for coffeescript.

Gists

This sample script is for adding object to object by javascript.

Script :

var obj = {
  key1: "value1",
  key2: "value2",
  key3: "value3"
};
var obj1 = {
  key4: "value4",
  key5: "value5",
  key6: "value6"
};
Object.assign(obj, obj1);
console.log(obj);

Result :

{ key1: 'value1',
  key2: 'value2',
  key3: 'value3',
  key4: 'value4',
  key5: 'value5',
  key6: 'value6' }

jsfiddle demo

Reference :

• Object.assign()