Gists

This is a sample script for retrieving the overwrapped cells between 2 ranges on Google Spreadsheet using Google Apps Script. Please use this with enabling V8.

const getOverwrappedRanges_ = (rangeList1, rangeList2) => {
  if (
    rangeList1.toString() != "RangeList" ||
    rangeList2.toString() != "RangeList"
  ) {
    throw new Error("Input RangeList object.");
  }

  // Ref: https://stackoverflow.com/a/21231012/7108653
  const columnToLetter = column => {
    let temp,
      letter = "";
    while (column > 0) {
      temp = (column - 1) % 26;
      letter = String.fromCharCode(temp + 65) + letter;
      column = (column - temp - 1) / 26;
    }
    return letter;
  };

  // Expand range1.
  const ar = rangeList1.getRanges().reduce((ar, r) => {
    const startRow1 = r.getRow();
    const endRow1 = startRow1 + r.getNumRows();
    const startColumn1 = r.getColumn();
    const endColumn1 = startColumn1 + r.getNumColumns();
    for (let j = startRow1; j < endRow1; j++) {
      for (let k = startColumn1; k < endColumn1; k++) {
        ar.push(columnToLetter(k) + j);
      }
    }
    return ar;
  }, []);

  // Expand range2.
  const map = rangeList2.getRanges().reduce((m, r) => {
    const startRow2 = r.getRow();
    const endRow2 = startRow2 + r.getNumRows();
    const startColumn2 = r.getColumn();
    const endColumn2 = startColumn2 + r.getNumColumns();
    for (let j = startRow2; j < endRow2; j++) {
      for (let k = startColumn2; k < endColumn2; k++) {
        m.set(columnToLetter(k) + j, null);
      }
    }
    return m;
  }, new Map());

  return ar.filter(e => map.has(e));
};

const main = () => {
  const range1 = ["B3:C7", "D6:E9"]; // Please input range1 as a1Notation.
  const range2 = ["A2:B3", "C7:D10"]; // Please input range2 as a1Notation.

  const sheet = SpreadsheetApp.getActiveSheet();
  const res = getOverwrappedRanges_(
    sheet.getRangeList(range1),
    sheet.getRangeList(range2)
  );
  console.log(res); // <--- ["B3","C7","D7","D8","D9"]
};

• In this sample script, from the ranges of "B3:C7", "D6:E9" and "A2:B3", "C7:D10", the overwrapped cells are returned. In this case, ["B3","C7","D7","D8","D9"] is returned.

Gists

• March 22, 2020
  • Published.

Kanshi Tanaike

Introduction

V8 engine got to be able to be used at Google Apps Script. By this, I have reported about the process costs with and without using V8. Ref It is considered that knowing the process costs for various methods will be useful for creating the applications with Google Apps Script. Here, I would like to introduce the process costs of each situations under V8. The situations which measured the cost are as follows.

Gists

This is a sample script for retrieving the files and folders which have no parents in own Google Drive.

When you use this script, please enable Drive API at Advanced Google services.

Sample script

const myFunction = () => {
  const token = ScriptApp.getOAuthToken();
  const fields = decodeURIComponent(
    "nextPageToken,files(name,id,mimeType,parents)"
  );
  const q = decodeURIComponent("'me' in owners and trashed = false");
  let files = [];
  let pageToken = "";
  do {
    const res = UrlFetchApp.fetch(
      `https://www.googleapis.com/drive/v3/files?pageSize=1000&fields=${fields}&q=${q}&pageToken=${pageToken}`,
      { headers: { authorization: `Bearer ${token}` } }
    );
    const obj = JSON.parse(res);
    Array.prototype.push.apply(files, obj.files);
    pageToken = obj.nextPageToken;
  } while (pageToken);
  const result = files.filter(({ parents }) => !parents);
  console.log(result);
};

When you run the script, the files and folders which have no parents in own Google Drive are retrieved.

Web Application for searching Google Apps Script Library from Database was completed. So I published it as v1.0.0. Please check Google Apps Script Library Database.

• v1.7.4 (March 11, 2020)

  1. Recently, I noticed that new Google Apps Script project of the standalone script type cannot be created by the create method of Drive API. From now, in order to create the standalone Google Apps Script project, only Google Apps Script API is required to be used. Ref By this, I updated ggsrun. So the command for creating new GAS project is not changed.

     • $ ggsrun u -p ###folderId### -f sample.gs -pn sampleGASProjectName

You can check ggsrun at https://github.com/tanaikech/ggsrun.

Gists

Today, I noticed that new Google Apps Script project of the standalone script type cannot be created by the method of Files: create in Drive API. From now, in order to manage the Google Apps Script project, only Google Apps Script API is required to be used. By this, the following issues are brought.

• When the new standalone GAS project is created in the specific folder by uploading the local script, the following flow is required to be run.
  1. Create new standalone GAS project by Apps Script API.
  2. Put the local script to the created GAS project by updating the project with Apps Script API.
  3. Move the GAS project from the root folder to the specific folder using Drive API.

From now, 3 API calls are required to be used like above. By the way, this had been able to be achieved by one API call of the method of files.create in Drive API before.

Gists

This is a simple sample script for achieving the resumable upload to Google Drive using Python. In order to achieve the resumable upload, at first, it is required to retrieve the location, which is the endpoint of upload. The location is included in the response headers. After the location was retrieved, the file can be uploaded to the location URL.

In this sample, a PNG file is uploaded with the resumable upload using a single chunk.

Gists

This is a simple sample script for achieving the resumable upload to Google Drive using Node.js. In order to achieve the resumable upload, at first, it is required to retrieve the location, which is the endpoint of upload. The location is included in the response headers. After the location was retrieved, the file can be uploaded to the location URL.

In this sample, a PNG file is uploaded with the resumable upload using a single chunk.

Overview

FieldsBuilderForGoogleAPIs is a Web Application for building the fields value for using Google APIs. This is mainly used for developing the scripts for using Google APIs.

Demo

Description

After the API explorer was updated, the fields of APIs cannot be created by the web interface. But it is important for using the fields property. Because when the fields property is used, only the required values can be retrieved. This leads to the low cost. And even when * is used, there are some cases that all fields cannot be retrieved. So I created this as a Web Application. This web application can run with the standalone. No authorization and no scopes are required. This only creates the fields value for Google APIs.

Overview

This is a GAS library for retrieving and creating the albums and media items using Google Photo API using Google Apps Script (GAS).

Description

In the current stage, Google Photo API is not included in Advanced Google services. But in order to use Google Photo API with Google Apps Script, I created this as a GAS library. So in the current stage, in order to use this library, the following flow is required.