Gists

When it uses Google Sheets API v4, GridRange is used for it as the range property. These sample scripts are for converting from a1Notation to GridRange. You can chose from following 2 scripts. Both scripts can retrieve the same result.

Script 1 :

This is from me.

function a1notation2gridrange1(sheetid, a1notation) {
  var data = a1notation.match(/(^.+)!(.+):(.+$)/);
  var ss = SpreadsheetApp.openById(sheetid).getSheetByName(data[1]);
  var range = ss.getRange(data[2] + ":" + data[3]);
  var gridRange = {
    sheetId: ss.getSheetId(),
    startRowIndex: range.getRow() - 1,
    endRowIndex: range.getRow() - 1 + range.getNumRows(),
    startColumnIndex: range.getColumn() - 1,
    endColumnIndex: range.getColumn() - 1 + range.getNumColumns(),
  };
  if (!data[2].match(/[0-9]/)) delete gridRange.startRowIndex;
  if (!data[3].match(/[0-9]/)) delete gridRange.endRowIndex;
  return gridRange;
}

Script 2 :

String.prototype.to10 was used for this script. String.prototype.to10 is from Alexander Ivanov. I think that String.prototype.to10 is a clever solution.

Gists

Here, I introduce 2 scripts for uploading files to Slack using Node.js as samples. These 2 sample scripts are for uploading files to Slack.

Sample script 1:

• You can upload the zip file by converting byte array as follows.
  • At first, it builds form-data.
  • Adds the zip file converted to byte array and boundary using Buffer.concat().
  • This is used as body in request.

Basically, this is almost the same to the method using GAS.

Gist

These sample scripts are for requesting multipart post using Google Apps Script.

In most cases, the multipart request is used for uploading files. So I prepared 2 sample situations as follows. For each situation, the request parameters are different.

1. Upload a file from Google Drive to Slack.
2. Convert an excel file to Spreadsheet on Google Drive using Drive API v3.

Multipart post is required for these situations.

1. Uploading Files From Google Drive To Slack

Curl Code

In order to use this sample, please retrieve access token for uploading file to Slack.

Gist

This is a sample script for retrieving images on Spreadsheet.

Unfortunately, there are no methods for retrieving directly images on spreadsheet using GAS. So I use the method which retrieves URL from =image(URL) and retrieves the image from the URL.

In this case, =image(URL) has to be in the cell. Images embedded by insertImage() cannot be retrieved.

At first, please confirm them.

Sample script :

var cell = "A1"; // Cell address with the function of "=image()"
var filename = "samplename"; // Output filename

var image = SpreadsheetApp.getActiveSheet().getRange(cell).getFormula();
var blob = UrlFetchApp.fetch(image.match(/\"(.+)\"/)[1]).getBlob();
DriveApp.createFile(blob.setName(filename));

Flow :

1. Retrieve =image(URL) using getFormula().
2. Retrieve URL from =image(URL).
3. Retrieve file blob using UrlFetchApp.fetch() from the URL.
4. Output the file blob as a file.

This sample is for retrieving array coordinates of duplicated elements.

Script :

var inputdata = ["a", "b", "b", "c", "d", "c", "e", "a", "f", "g"];

var dic = {};
var result = [];
inputdata.forEach(function(e, i){
  if (dic[e]) {
    result[i] = 'Duplicated';
  } else {
    result[i] = null;
  }
  dic[e] = "temp";
});

Logger.log(JSON.stringify(result))
Logger.log([i for (i in result) if(result[i]=='Duplicated')])

Result :

[null,null,"Duplicated",null,null,"Duplicated",null,"Duplicated",null,null]
[2, 5, 7]

Gists

This is a sample of Google Apps Script. This script is for retrieving all files and folders under a folder on Google Drive. All files and folders in the specific folder can be retrieved.

If you want to retrieve file list with all files and folders on Google Drive, please use DriveApp.getRootFolder().getId() as folderId.

When there are a lot of files in the folder, it may be over the limitation time to execute script.

ggsrun was updated to v.1.2.2

1. For Google Docs (spreadsheet, document, slide and drawing), since I noticed that the revision files would not be able to be retrieved using Drive API v3, I modified this using new workaround.
   • The new workaround is to use Drive API v2. drive.revisions.get of Drive API v2 can retrieve not only the revision list, but also the export links. I thought of the use of the export links. This became the new workaround.
   • For the files except for Google Docs, the revision files can be retrieved using Drive API v3.
   • The usage is here.

I don’t know when this workaround will not be able to be used. But if this could not be used, I would like to investigate of other method.

This method was updated at July 12, 2017.

In order to use this, at first, please retrieve your access token and enable Drive API.

1. File ID

Retrieve file id from file name.

curl -X GET -sSL \
    -H 'Authorization: Bearer ### Access token ###' \
    'https://www.googleapis.com/drive/v3/files?q=name="### FileName ###"&fields=files(id,name)'

Reference : https://developers.google.com/drive/v3/reference/files/list

2. Revision ID

Retrieve revision id from file id.

curl -X GET -sSL \
    -H 'Authorization: Bearer ### Access token ###' \
    'https://www.googleapis.com/drive/v3/files/### FileID ###/revisions?fields=revisions(id%2CmodifiedTime)'

Reference : https://developers.google.com/drive/v3/reference/revisions/list

Gist

Overview

This is a sample script for creating the pseudo browser using Google Spreadsheet.

Description

I unexpectedly noticed this. I think that this is for off-line browsing using HTML data. So there are many limitations. At first, please confirm them.

• Limitations
  • It cannot move from opened site to other outside site. If the outer site is opened as a new wind, your own browser is opened and move there.
  • For URL, it can move directories which is one low. But it cannot move directories more than two deeper.
    • Because it retrieves the HTML data from the inputted URL using UrlFetchApp. It cannot move to the out side of data retrieved by UrlFetchApp.
  • The access to the site opened by inputting URL is run at Google side.
  • Javascript on the opened site is run at your local PC side.
  • User-Agent cannot be changed from Mozilla/5.0 (compatible; Google-Apps-Script).

What I thought the interesting is that the appearance of the site has been maintained at this pseudo browser. And, by using this, the sites which are slow speed at your environment might be able to be opened smoothly.

Gist

This is a sample script for downloading files from Google Drive under no authorization using browser. By using this sample, you can make other users download files from your Google Drive. Even if the other users are not Google users, they can download the files.

Demo

This is a demonstration for downloading files from Google Drive under no authorization using browser. From the top document, You can see that an user who is not owner of Google Drive is downloading files.