This sample script converts a spreadsheet to excel file, and overwrites the excel file to the existing excel file. When you use this script, at first, please confirm whether Drive API is enabled at Google API console. Because the existing excel file is overwritten, the file name and file ID are not changed.

function overWrite(src_spreadsheetId, dst_excelfileId) {
  var accesstoken = ScriptApp.getOAuthToken();
  return UrlFetchApp.fetch(
    "https://www.googleapis.com/upload/drive/v3/files/" +
    dst_excelfileId +
    "?uploadType=multipart",
    {
      method: "PATCH",
      headers: {Authorization: "Bearer " + accesstoken},
      contentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
      payload: function(a, s) {
        return UrlFetchApp.fetch(
          "https://www.googleapis.com/drive/v3/files/" +
          s +
          "/export?mimeType=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
          {
            method: "GET",
            headers: {Authorization: "Bearer " + a},
            muteHttpExceptions: true
          }
        ).getBlob().getBytes();
      }(accesstoken, src_spreadsheetId)
    }
  ).getContentText();
}

Converting Spreadsheet to PDF

This sample script converts from a spreadsheet to a PDF file which has all sheets in the spreadsheet. When you use this, please enable Drive API at Google API console.

var spreadsheetId = "#####";
var folderId = "#####";
var outputFilename = "#####";

DriveApp.getFolderById(folderId)
    .createFile(UrlFetchApp.fetch(
      "https://www.googleapis.com/drive/v3/files/" +
        spreadsheetId +
        "/export?mimeType=application/pdf",
      {
        method: "GET",
        headers: {Authorization: "Bearer " + ScriptApp.getOAuthToken()},
        muteHttpExceptions: true
      })
    .getBlob())
    .setName(outputFilename);

gogauth is a CLI tool to retrieve easily access token for using APIs on Google.

Today, it was updated with big changes. Please check it out.

https://github.com/tanaikech/gogauth

Recently, I had been looking for creating an image from coordinate data. Unfortunately I have never found them. So I made this. This Google Apps Script (GAS) library creates an image file from coordinate data.

You can see the detail information at https://github.com/tanaikech/CreateImg.

There is a part where I would like to improve in this library. That’s convByteSlice(). I think that there is the method to be faster about the part. If you know much about the logical operation using GAS, if you teach me about the improvements. I’m so glad.

Here, I would like to introduce a comprehension of GAS.

Input :

var data = [[[0], [1], [2], [3]], [[4], [5], [6], [7]]];

Output :

[[0.0, 2.0], [0.0, 2.0]]

Pattern 1

var a = [];
for (var i=0; i<data.length; i++) {
  var temp = [];
  for (var j=0; j<data[i].length; j++) {
    if (data[i][j][0] % 2 == 0) temp.push(j);
  }
  a.push(temp);
}
Logger.log(a)

Pattern 2

var b = [];
data.forEach(function(e1){
  var temp = [];
  e1.forEach(function(e2, i2){
    if (e2[0] % 2 == 0) temp.push(parseInt(i2, 10));
  });
  b.push(temp);
});
Logger.log(b)

Pattern 3

var c = [[parseInt(i, 10) for (i in e) if (e[i][0] % 2 == 0)] for each (e in data)];
Logger.log(c)

GAS can use JavaScript 1.7. So it can write as above.

These scripts can be executed on Script Editor. But, in order to use these, you have to enable Drive API of Advanced Google services and of Google API Console. “Drive API v2” can be used at Google Apps Script by enabling Drive API of Advanced Google services and of Google API Console.

How to use it is as follows.

1. In the script editor, select Resources > Advanced Google services

2. In the dialog that appears, click the on/off switch for Drive API v2.

This is a sample GAS script to create an Excel file, which was downloaded from web, as Spreadsheet. By using Drive API, it can be achieved without access token.

Script :

function downloadFile(fileURL, folder) {
  var filename = fileURL.match(".+/(.+?)([\?#;].*)?$")[1];
  var response = UrlFetchApp.fetch(fileURL);
  var rc = response.getResponseCode();
  var blob = response.getBlob();
  var resource = {
    "mimeType": "application/vnd.google-apps.spreadsheet",
    "parents": [{id: folder}],
    "title": filename
  };
  var res = Drive.Files.insert(resource, blob);
  var fileInfo = [rc, res.title, blob.getBytes().length, res.id];
  return fileInfo;
}

Result :

[
    200,
    sample.xlsx,
    10000.0,
    ## file id ##
]

This sample removes duplicate JSON elements for a value of a certain key. When the value of the certain key is removed, only a first duplicate element is left. Also I had wanted to be used for Google Apps Script. So it became like this.

Script :

function removeDup(arr, key){
    var temp = [];
    var out = [];
    arr.forEach( function (e, i) {
        temp[i] = (temp.indexOf(e[key]) === -1) ? e[key] : false;
        if (temp[i]) out.push(e);
    });
    return out;
}

JSON :

This sample flattens a nested array using CoffeeScript.

flatten = (array) ->
    array.reduce(((x, y) -> if Array.isArray(y) then x.concat(flatten(y)) else x.concat(y)), [])

console.log flatten [1, [2, 3, [4, 5]], 6, [7, [8, [9], 10] ,11 , 12], 13]

>>> [ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13 ]

There are a lot of APIs on Google. When we use Google Drive APIs, they usually have “fields” as a resource. The parameter “fields” gives various information which is selected to us. This is one of important parameters. And this can be used at Google Apps Script (GAS) although that version is v2. About how to use it, there are some documents. But it is difficult to find how to use it at GAS. So I would like to write down here as a memorandum. Most parameters for APIs have to be expressed as JSON. However, the expressions are different for each API. I would like to introduce this using some samples. This is for GAS.