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.

OnedriveApp was updated to v1.0.2.

Moved the instance of PropertiesService.getScriptProperties() to outside of this library. When there is the PropertiesService.getScriptProperties() inside the library, it was found that the parameters that users set was saved to the library. So this was modified. I’m sorry that I couldn’t notice this situation.

GitHub of OnedriveApp

OnedriveApp was updated to v1.0.1.

Added a method for retrieving access token and refresh token using this library.

By added this method, OneDrive APIs can be used by only this library.

GitHub of OnedriveApp

Gists

This sample script is for changing values by checking duplicated values of JSON for Javascript.

Please see the following script. There is an array with a JSON data with 3 keys and 3 values. It is found that the values for each element duplicate. These duplicated values are changing by adding numbers.

I use this for managing filenames. This script also can be used for Google Apps Script. If this was useful for you, I’m glad.