Gist

Overview

This GAS sample is for retrieving access token to use OneDrive APIs using Google Apps Script.

In this script, the authorization code is automatically retrieved.

Demo

Usage

In order to use this, both accounts of Google and OneDrive (MSN) are required.

Google side

1. Copy and paste the sample script to your script editor. You can use the standalone script for this.
2. Deploy Web Apps.
   • On the Script Editor
     • File
     • -> Manage Versions
     • -> Save New Version
     • Publish
     • -> Deploy as Web App
     • -> At Execute the app as, select “your account”
     • -> At Who has access to the app, select “Only myself”
     • -> Click “Deploy”
     • -> Copy URL of “latest code” (This is important!)
     • -> Click “OK”
3. URL of “latest code” is https://script.google.com/macros/s/###/dev. So please modify this URL. Replace from “dev” to “usercallback” for the URL. And copy this modified URL.
   • From : https://script.google.com/macros/s/###/dev
   • To : https://script.google.com/macros/s/###/usercallback

OneDrive side

1. Log in to Microsoft Azure portal.
2. Search “Azure Active Directory” at the top of text input box. And open “Azure Active Directory”.
3. Click “App registrations” at the left side bar.
   • In my environment, when I used Chrome as the browser, no response occurred. So in that case, I used Microsoft Edge.
4. Click “New registration”
   1. app name: “sample app name”
   2. Supported account types: “Accounts in any organizational directory (Any Azure AD directory - Multitenant) and personal Microsoft accounts (e.g. Skype, Xbox)”
   3. Redirect URI (optional): Web
      • URL: https://script.google.com/macros/s/###/usercallback
   4. Click “Register”
5. Copy “Application (client) ID”.
6. Click “Certificates & secrets” at the left side bar.
   1. Click “New client secrets”.
   2. After input the description and select “expire”, click “Add” button.
   3. Copy the created secret value.

By above operation, the preparation is done.

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.