Gists

Flow :

In my sample script, the script was made using the Quickstart. The flow to use this sample script is as follows.

1. For Go Quickstart, please do Step 1 and Step 2.
2. Please put client_secret.json to the same directory with my sample script.
3. Copy and paste my sample script, and create it as new script file.
4. Run the script.
5. When Go to the following link in your browser then type the authorization code: is shown on your terminal, please copy the URL and paste to your browser. And then, please authorize and get code.
6. Put the code to the terminal.
7. When Done. is displayed, it means that the update of spreadsheet is done.

Request body :

For Spreadsheets.Values.BatchUpdate, BatchUpdateValuesRequest is required as one of parameters. In this case, the range, values and so on that you want to update are included in BatchUpdateValuesRequest. The detail information of this BatchUpdateValuesRequest can be seen at godoc. When it sees BatchUpdateValuesRequest, Data []*ValueRange can be seen. Here, please be carefull that Data is []*ValueRange. Also ValueRange can be seen at godoc. You can see MajorDimension, Range and Values in ValueRange.

ggsrun was updated to v.1.3.1

1. Recently, when scripts on local PC is uploaded to Google Drive as a new project, the time to create on Google became a bit long. (I think that this is due to Google Update.) Under this situation, when the script is uploaded, the timeout error occurs while the new project is created using the script. So the time until timeout of fetch was modified from 10 seconds to 30 seconds. By this, when the script is uploaded, no error occurs and the information of the created project is shown.
   • You can create a new project on Google Drive using scripts on local PC. The sample command is ggsrun u -f sample.gs1,sample2.gs,sample3.html -pn newprojectname

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

Gist

This sample script is for uploading image files to Slack using Incoming Webhooks by Google Apps Script.

When users try to upload image files to Slack using Incoming Webhooks, it has been known that although the access token is required to directly upload them, Incoming Webhooks can upload them by using the tag of image_url. In this sample script, it uploads image files (BMP, GIF, JPEG and PNG) on Google Drive to Slack using Incoming Webhooks. The script is written by Google Apps Script.

Gists

This sample script is for retrieving files with filename included special characters using Google Apps Script. The files are used on Google Drive.

The files with filename of special characters cannot be retrieved using DriveApp.getFilesByName(). This workaround solved this.

As a query parameter, name contains 'filename with special characters' is used. This contains is very important. name='filename with special characters' cannot retrieve such files. Today, it was found that name contains 'filename with special characters' is the workaround. I have been looking for this workaround for a while. Finally, I found this today. By using this method, filename included umlauts can be also retrieved.

Gists

This sample script is for removing duplicated values in array using CoffeeScript.

ar = ["a", "b", "c", "a", "c"]
res = ar.filter (e, i, s) -> s.indexOf(e) is i
console.log res

>>> [ 'a', 'b', 'c' ]

The result which was compiled by CoffeeScript is as follows.

var ar, res;
ar = ["a", "b", "c", "a", "c"];
res = ar.filter(function(e, i, s) {
  return s.indexOf(e) === i;
});
console.log(res);

Here, it introduces an application of Retrieving Spreadsheet ID from Range using Google Apps Script.

Please check this. https://github.com/tanaikech/getSpreadsheetByRange

Gists

This is a sample script for retrieving spreadsheet ID from a range using Google Apps Script. I sometimes want to retrieve spreadsheet ID from ranges. In such case, I always use this.

• Range
• -> Retrieve Sheet using getSheet()
• -> Retrieve Spreadsheet using getParent()
• -> Retrieve spreadsheet ID

var id = "123456789abcdefg";
var sheet = "Sheet";
var cells = "a1:b10";
var range = SpreadsheetApp.openById(id).getSheetByName(sheet).getRange(cells);

var id = range.getSheet().getParent().getId();

>>> id ---> 123456789abcdefg

ggsrun was updated to v.1.3.0

1. From this version, container-bound scripts can be downloaded. The container-bound script is the script created at the script editor on Google Sheets, Docs, or Forms file.
   • In order to download container-bound scripts, the project ID of container-bound scripts is required. The project ID can be retrieved as follows.
     • Open the project. And please operate follows using click.
       • -> File
       • -> Project properties
       • -> Get Script ID (This is the project ID.)
2. When a project is downloaded, the filename of HTML file had become .gs. This bug was modified.

The usage is here.

Gists

This is a sample script for retrieving values by header title for Spreadsheet. This is created by Google Apps Script. The main script is as follows.

Main script :

When the instance is retrieved, all data of the sheet is analyzed. So when the each value is retrieved, the speed is fast.

function GetValueByKey(sheetname) {
    return new getValueByKey(sheetname);
};

(function(r) {
  var getValueByKey;
  getValueByKey = (function() {
    getValueByKey.name = "getValueByKey";

    function getValueByKey(sheetname) {
      var alldata, e, header, ss;
      try {
        ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetname);
      } catch (error) {
        e = error;
        throw new Error("Error: No sheetname (" + sheetname + ").");
      }
      alldata = ss.getDataRange().getValues();
      header = alldata[0];
      alldata.shift();
      this.manageddata = (function(header, alldata) {
        var i, j, k, key, len, len1, result, temp, value;
        result = [];
        for (j = 0, len = alldata.length; j < len; j++) {
          value = alldata[j];
          temp = {};
          for (i = k = 0, len1 = header.length; k < len1; i = ++k) {
            key = header[i];
            temp[key] = value[i];
          }
          result.push(temp);
        }
        return {
          values: result,
          headerLength: header.length,
          dataLength: alldata.length
        };
      })(header, alldata);
    }

    getValueByKey.prototype.getValue = function(index, key) {
      return this.manageddata.values[index][key];
    };

    getValueByKey.prototype.getAllValues = function() {
      return this.manageddata;
    };

    return getValueByKey;

  })();
  return r.getValueByKey = getValueByKey;
})(this);

Demo

Following sheet is a sample sheet for this.

Gists

This is a sample script for exporting a project on Google Drive to local PC using Golang Quickstart. A file with refresh token is saved to the same directory with this script as go-quickstart.json. Before you run this script, please enable Drive API on your Google API console.

Points for exporting project

1. In order to export project, both drive.DriveScriptsScope and drive.DriveScope have to be included in the scope.
2. The mimeType for exporting has to be “application/vnd.google-apps.script+json”.

If you already have the file with refresh token, at first, please delete it and run this script. By this, the scopes of refresh token and access token are updated.