Report

Netatmo API had been down from Aug. 8, 2017 21:30 JST to Aug. 9, 2017 17:30 JST. Now it’s working.

I got an e-mail from Netatmo. They say that the issue was solved.

Report

Netatmo API had been down from Aug. 7, 2017 00:00 a.m. JST to Aug. 7, 2017 07:30 a.m. JST. Now it’s working.

Gists

When the shared files on Google Drive is downloaded, it is necessary to change the download method by the file size. The boundary of file size when the method is changed is about 40MB.

File size < 40MB

CURL

filename="### filename ###"
fileid="### file ID ###"
curl -L -o ${filename} "https://drive.google.com/uc?export=download&id=${fileid}"

File size > 40MB

When it tries to download the file with more than 40MB, Google says to download from following URL.

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.