Gists

This is a sample script for efficiently creating Web Apps using a Google Apps Script library.

When a Google Apps Script library is used for creating Web Apps, the following advantage can be obtained.

• The script of the client-side can be simpler. Because most scripts for constructing Web Apps are included in the Google Apps Script library.
• When the script of Web Apps (In this case, the script of Google Apps Script library is modified.) is modified, the latest script is reflected in the Web Apps, immediately. Because when the Google Apps Script library is used as the latest version when the script of the library is modified, the client can use the latest script of the library, immediately. So, the downtime of Web Apps can be reduced.
  • By this, it is not required to manually reflect the latest version of the script to the Web Apps.
• When you can change the script of Web Apps by changing the deployed version of the library.

The sample script for explaining this is as follows.

Gists

This is a sample script for high-efficiency finding and replacing many values in Google Spreadsheet with the low process cost using Google Apps Script.

When the various values are replaced in Google Spreadsheet using Google Apps Script, I’m worried about the process cost. So, in this report, I would like to introduce a sample script for high-efficiency achieving this.

Gists

This is a sample script for updating the destination sheet by the source sheet in Google Spreadsheet using Google Apps Script.

The sample situation is as follows.

Sample script

function myFunction() {
  const sheetNames = ["Sheet1", "Sheet2"];

  // Retrieve values from source and destination sheets.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [srcSheet, dstSheet] = sheetNames.map((s) => ss.getSheetByName(s));
  const [srcValues, dstValues] = [srcSheet, dstSheet].map((s) =>
    s.getDataRange().getValues()
  );

  // Create an array for updating the destination sheet.
  const srcObj = srcValues.reduce((o, r) => ((o[r[0]] = r), o), {});
  const values = [
    ...dstValues.map(([a, ...v]) => {
      if (srcObj[a]) {
        const temp = srcObj[a];
        delete srcObj[a];
        return temp;
      }
      return [a, ...v];
    }),
    ...Object.values(srcObj),
  ];

  // Update the destination sheet.
  dstSheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

• In this sample script, “Sheet1” and “Sheet2” are the source and destination sheets, respectively.

Gists

This is a sample script for detecting whether the specific cells on Google Spreadsheet are manually moved using Google Apps Script.

In this case, the named range, OnChange trigger, and PropertiesService are used.

Usage:

1. Create a named range.

As a sample, please create a named range to the cells “A2:B2” as “sampleNamedRange1”. Ref

2. Prepare sample script.

Please copy and paste the following script to the script editor of Spreadsheet. And, please install OnChange trigger to the function installedOnChange.

Gists

In this post, I would like to introduce the method for using RichTextValue with a custom function of Google Apps Script.

This sample is for this thread in Stackoverflow.

In this thread, the OP’s goal is as follows.

• Put a text to a cell. In this case, use a hyperlink in a part of the text.
• This is required to be achieved using a custom function.

In the current stage, in order to reflect the hyperlink in a part of the text, it is required to use setRichTextValue of Google Apps Script. In this case, this method cannot be used with the custom function. This is the current specification.

Gists

This is a sample script for retrieving icons of each mimeType on Google Drive using Google Apps Script.

Sample script

Sample list of mimeType is from this official document.

function getIcons() {
  const iconSize = 256; // Pixels
  const mimeTypes = [
    "application/vnd.google-apps.audio",
    "application/vnd.google-apps.document",
    "application/vnd.google-apps.drive-sdk",
    "application/vnd.google-apps.drawing",
    "application/vnd.google-apps.file",
    "application/vnd.google-apps.folder",
    "application/vnd.google-apps.form",
    "application/vnd.google-apps.fusiontable",
    "application/vnd.google-apps.jam",
    "application/vnd.google-apps.map",
    "application/vnd.google-apps.photo",
    "application/vnd.google-apps.presentation",
    "application/vnd.google-apps.script",
    "application/vnd.google-apps.shortcut",
    "application/vnd.google-apps.site",
    "application/vnd.google-apps.spreadsheet",
    "application/vnd.google-apps.unknown",
    "application/vnd.google-apps.video",
  ];
  const imageUrls = mimeTypes.map(
    (e) =>
      `https://drive-thirdparty.googleusercontent.com/${iconSize}/type/${e}`
  );
  const blobs = UrlFetchApp.fetchAll(imageUrls).map((r, i) =>
    r.getBlob().setName(`${mimeTypes[i].split("/")[1]}.png`)
  );
  const zip = Utilities.zip(blobs, "sampleIcons.zip");
  DriveApp.createFile(zip);
}

• When this script is run, a ZIP file including icons is created to the root folder.

Gists

Updated on January 25, 2024

Overview:

This is a report for challenging exporting the selected cells on Spreadsheet as an image using Google Apps Script and Javascript.

Description:

This report is based on this question by Max Makhrov. When I saw this question, I remembered that there are many questions for asking this in Stackoverflow. And, I thought that when this is achieved, it will be useful for the owner of this question and a lot of users. So, I have discussed this in his question.

Javascript Library for Cropping Image by Border

Overview

This is a Javascript library for cropping images by the border.

Description

When an image is used, there is a case where I wanted to simply crop the image by a script. In this Javascript library, the image is cropped by a border. The sample situation is as follows.

In this sample situation, a red rectangle is enclosed by a border (1 pixel) with “#000000”. By this border, this library crops the red rectangle. In this case, the 1-pixel border is not included in the resulting image.

Gists

This is a sample script for clearing the discrete cell values on multiple sheets using Google Apps Script.

There might be a case where you want to clear the values of the discrete cells in the multiple sheets using Google Apps Script. In this post, I would like to introduce the efficient script for achieving this.

Sample script 1

Please copy and paste the following script to the script editor of the Google Spreadsheet you want to use.

Gists

This is a sample script for the resumable upload using Axios with Node.js.

Sample script

In this sample script, as a sample situation in order to explain the resumable upload, the file data is loaded from the local PC, and the data is uploaded to Google Drive with the resumable upload.

const axios = require("axios");
const fs = require("fs").promises;

async function sample() {
  const filepath = "./###"; // Please set the filename and file path of the upload file.

  const new_access_token = "###"; // Please set your access token.
  const name = "###"; // Please set the filename on Google Drive.
  const mimeType = "###"; // Please set the mimeType of the uploading file. I thought that when this might not be required to be used.

  // 1. Prepare chunks from loaded file data.
  const split = 262144; // This is a sample chunk size.
  const data = await fs.readFile(filepath);
  const fileSize = data.length;
  const array = [...new Int8Array(data)];
  const chunks = [...Array(Math.ceil(array.length / split))].map((_) =>
    Buffer.from(new Int8Array(array.splice(0, split)))
  );

  // 2. Retrieve endpoint for uploading a file.
  const res1 = await axios({
    method: "POST",
    url: "https://www.googleapis.com/upload/drive/v3/files?uploadType=resumable",
    headers: {
      Authorization: `Bearer ${new_access_token}`,
      "Content-Type": "application/json",
    },
    data: JSON.stringify({ name, mimeType }),
  });
  const { location } = res1.headers;

  // 3. Upload the data using chunks.
  let start = 0;
  for (let i = 0; i < chunks.length; i++) {
    const end = start + chunks[i].length - 1;
    const res2 = await axios({
      method: "PUT",
      url: location,
      headers: { "Content-Range": `bytes ${start}-${end}/${fileSize}` },
      data: chunks[i],
    }).catch(({ response }) =>
      console.log({ status: response.status, message: response.data })
    );
    start = end + 1;
    if (res2?.data) console.log(res2?.data);
  }
}

sample();

Testing

When this sample script is run, the following result is obtained.