Gists

When an image is inserted to Google Docs (Spreadsheet, Document and Slides) using the method of insertImage using Google Apps Script, there is the case that the error occurs. The error messages are “server error” and “invalid image data”. Here, I would like to introduce the limitations for inserting images to Google Docs. As the result, it was found that the limitation is due to both the mimeTypes and the area of image rather than the file size.

Gists

This is a sample Node.js script to retrieve access token from Service Account of Google without using googleapis.

const cryptor = require('crypto');
const request = require('request');

const privateKey = "###"; // private_key of JSON file retrieved by creating Service Account
const clientEmail = "###"; // client_email of JSON file retrieved by creating Service Account
const scopes = ["https://www.googleapis.com/auth/drive.readonly"]; // Sample scope

const url = "https://www.googleapis.com/oauth2/v4/token";
const header = {
  alg: "RS256",
  typ: "JWT",
};
const now = Math.floor(Date.now() / 1000);
const claim = {
  iss: clientEmail,
  scope: scopes.join(" "),
  aud: url,
  exp: (now + 3600).toString(),
  iat: now.toString(),
};

const signature = Buffer.from(JSON.stringify(header)).toString('base64') + "." + Buffer.from(JSON.stringify(claim)).toString('base64');

var sign = cryptor.createSign('RSA-SHA256');
sign.update(signature);
const jwt = signature + "." + sign.sign(privateKey, 'base64');

request({
  method: "post",
  url: url,
  body: JSON.stringify({
    assertion: jwt,
    grant_type: "urn:ietf:params:oauth:grant-type:jwt-bearer",
  }),
}, (err, res, body) => {
  if (err) {
    console.log(err);
    return;
  }
  console.log(body);
});

Gists

There are 2 sample scripts.

1. Create new Spreadsheet using a text value as CSV data.
2. Overwrite the existing Google Document using a text value.

When you use these script, please enable Drive API and retrieve your access token.

Create New Spreadsheet using Text Value

const request = require('request');

const textData = "a1, b1, c1, d1, e1"; // This is used as CSV data.
const orgMimeType = "text/csv";
const orgFileName = "sample.csv";
const accessToken = "###"; // Access token
const metadata = {
  name: "convertedSampleCSV",
  mimeType: "application/vnd.google-apps.spreadsheet",
};

const url = "https://www.googleapis.com/upload/drive/v3/files?uploadType=multipart";
const boundary = "xxxxxxxxxxx";
var data = "--" + boundary + "\r\n";
  data += "Content-Disposition: form-data; name=\"metadata\"\r\n";
  data += "Content-Type: application/json; charset=UTF-8\r\n\r\n";
  data += JSON.stringify(metadata) + "\r\n";
  data += "--" + boundary + "\r\n";
  data += "Content-Disposition: form-data; name=\"file\"; filename=\"" + orgFileName + "\"\r\n";
  data += "Content-Type: " + orgMimeType + "\r\n\r\n";

var payload = Buffer.concat([
  Buffer.from(data, "utf8"),
  new Buffer(textData, 'binary'),
  Buffer.from("\r\n--" + boundary + "--", "utf8"),
]);

const options = {
  method: 'post',
  url: url,
  headers: {
    "Content-Type": "multipart/related; boundary=" + boundary,
    'Authorization': 'Bearer ' + accessToken,
  },
  body: payload,
};
request(options, (error, response, body) => {
  console.log(body);
});

Overwrite Existing Google Document using Text Value

This can be used when you don’t want to update the existing Google Docs without changing the file ID.

Gists

This is a sample script for deleting the positioned images on Google Document using Google Apps Script. In the current stage, unfortunately, there are no methods for deleting the positioned images in Class PositionedImage, yet. But when Google Docs API is used, the positioned images can be deleted.

When you use this script, please enable Google Docs API at Advanced Google Services and API console. You can see how to enable them at here

Gists

This is a sample script for modifying the searched text to the small capital letters using Google Apps Script. Unfortunately, in the current stage, there are no methods for modifying the part of texts to the small capital letters in Document Service, yet. But when Google Docs API is used, this can be achieved.

When you use this script, please enable Google Docs API at Advanced Google Services and API console. You can see how to enable them at here

Overview

This is a GAS library for notifying the change of number of comments, stars and forks of own Gists as an email using Google Apps Script.

Description

Recently, I noticed that when a comment was posted to own Gists, and the numbers of stars and forks of own Gists were changed, the notification mail is not sent. Also I knew that in the current stage, there are no official methods for notifying them, yet. For this situation, I thought an application for notifying them as an email can be created using Google Apps Script, and when such application can be easily to be used, it may be useful for other users. So I created this as a GAS library.

Gists

ggsrun is also a CLI application for using Google Drive.

Here, I would like to introduce a sample command. This is a sample command for uploading a file to a shared folder using ggsrun.

This situation supposes that the shared folder is https://drive.google.com/drive/folders/abcdefg?usp=sharing and the folder has the edit permission.

Sample command:

$ ggsrun u -f "sample.txt" -p "abcdefg" --serviceaccount "###JSON file of Service Account###"

• If you have already used OAuth2, you can upload the file by ggsrun u -f "sample.txt" -p "###folderId###".

Gists

Consumer (personal) version of Google+ is closed on April 2, 2019. By this, Apps Script community of Google+ is also closed. This is one of important communities for discussing. So in this post, I would like to introduce the other communities related to Google Apps Script.

1. As the next community of Apps Script community of Google+, Google Apps Script Community was launched.

   • This is also introduced at How to get help of official site like this.

2. Stackoverflow: https://stackoverflow.com/questions/tagged/google-apps-script

Gists

This is a sample script for parsing query parameters from an URL using Google Apps Script. Also this can be used at Javascript. The process cost becomes a bit lower than that of the script using the regular expression.

Sample script

function parseQuery(url) {
  var query = url.split("?")[1];
  if (query) {
    return query.split("&")
    .reduce(function(o, e) {
      var temp = e.split("=");
      var key = temp[0].trim();
      var value = temp[1].trim();
      value = isNaN(value) ? value : Number(value);
      if (o[key]) {
        o[key].push(value);
      } else {
        o[key] = [value];
      }
      return o;
    }, {});
  }
  return null;
}

// Please run this function when you test this script.
function main() {
  var url = "https://sampleUrl.com/sample?key1=value1&key2=value2&key1=value3&key3=value4&key2=value5";
  var res = parseQuery(url);
  Logger.log(res);
}

Result

{
  "key1": [
    "value1",
    "value3"
  ],
  "key2": [
    "value2",
    "value5"
  ],
  "key3": [
    "value4"
  ]
}

Gists

Introduction

At Stackoverflow, a lot of people post the questions and answers to the questions every day. There are various tags in Stackoverflow. A lot of discussions are performed at each tag. Their discussions bring the important information and are much useful for a lot of people. As one of tags, there is “google-apps-script”. I sometimes discuss at the questions with that tag. When we see the discussions, we can notice that the discussions are changed and progressed by the time, because “Google Apps Script” which is the origin of the tag is updated. This report thinks this change as the trend of tag of “google-apps-script”. This trend includes the number of questions, questioners, answerers and tags adding to the tag of “google-apps-script”. The trend of tag of “google-apps-script” is deeply related to the progression of Google Apps Script and the various applications for Google Apps Script.