Gists

This is a sample script for setting alternate background colors for rows in Google Spreadsheet using Google Apps Script.

It has already been known when the conditional formatting rule and custom function are used, this can be simply achieved. Ref In this report, I would like to introduce the method for using Google Apps Script.

Sample script

In this sample script, the values of column “A” are checked.

function myFunction() {
  const colors = { color1: "#f4cccc", color2: "#d9ead3" };

  const sheet = SpreadsheetApp.getActiveSheet();
  const ranges = sheet
    .getRange("A1:A" + sheet.getLastRow())
    .getValues()
    .reduce(
      (o, [b], i) => {
        if (b != o.temp) {
          o.temp = b;
          o.c++;
        }
        o[["color1", "color2"][o.c % 2]].push(`${i + 1}:${i + 1}`);
        return o;
      },
      { color1: [], color2: [], c: 0, temp: "" }
    );
  Object.entries(colors).forEach(([k, v]) =>
    sheet.getRangeList(ranges[k]).setBackground(v)
  );
}

• The process cost of this script can be reduced by using RangeList.

Reference

• Class RangeList

Gists

This is a sample Google Apps Script for running the specific function when the specific sheet is edited.

Sample script

Please copy and paste the following script to the container-bound script of Spreadsheet and set sheets object.

// When the cells are edited, this function is run by the fire of event trigger.
function onEdit(e) {
  // Please set the sheet name and function as follows.
  const sheets = {
    Sheet1: functionForSheet1, // Sheet1 is the sheet name. functionForSheet1 is the function name of function which is run when Sheet1 is edited.
    Sheet2: functionForSheet2,
  };

  const sheetName = e.range.getSheet().getSheetName();
  if (sheets[sheetName]) {
    sheets[sheetName](e);
  }
}

// In this sample, when Sheet1 is edited, this function is run.
function functionForSheet1(e) {
  console.log("Sheet1 was edited.");

  // do something
}

// In this sample, when Sheet2 is edited, this function is run.
function functionForSheet2(e) {
  console.log("Sheet2 was edited.");

  // do something
}

• In this sample script, when the cells of “Sheet1” and “Sheet2” are edited, functionForSheet1() and functionForSheet2() are run, respectively. When other sheets are edited, no functions are run.
• In this sample script, onEdit of the simple trigger is used. When the functions you want to run include the methods which are required to authorize, please use the installable trigger.

Note

• This method can be also used for other event triggers like OnChange, OnSelectionChange and so son.

References

• Simple Triggers
• Installable Triggers
• Event Objects

Gists

Introduction

Here, I would like to report the process costs for searching values in Spreadsheet using Google Apps Script (GAS). When the values are searched in Google Spreadsheet, the following 3 patterns can be considered. Ref

1. Retrieve all values using getValues, and the values are searched from the retrieved array.
2. Use TextFinder.
3. Use Query language.

In these cases, it has already been found that the lowest process cost is to use the Query language. And about finding values from an array, I have already been reported as “Benchmark: Search for Array Processing using Google Apps Script”. But I had never summarized the process costs for TextFinder and find values from an array. So in this report, I would like to introduce this. As the result, the importance of TextFinder for retrieving the row numbers and the row values by searching a value could be understand.

Gists

In order to print and export as PDF file, this is a sample script for converting Spreadsheet to Spreadsheet which has the custom header and footer.

In this sample script, DocsServiceApp, which is Google Apps Script library, is used. And, in this case, the Spreadsheet with the custom header and footer is created as new Spreadsheet.

Before you use this script, please install DocsServiceApp and enable Drive API at Advanced Google services.

ImgApp was updated to v1.3.0.

• v1.3.0 (September 24, 2020)
  • Added new method. Added editImage()
  • This method is for editing images. In the current stage, the image can be cropped. And several images can be merged as an image.

You can see the detail information here https://github.com/tanaikech/ImgApp

Overview

This is a Google Apps Script library for supporting Document service, Docs API, Spreadsheet service, Sheets API, Slides service and Slides API. The aim of this library is to compensate the processes that they services cannot achieve.

Description

The Google services, which are Document service, Docs API, Spreadsheet service, Sheets API, Slides service and Slides API, are growing now. But, unfortunately, there are still the processes that they cannot done. I created this GAS library for supporting the Google services.

FetchApp was updated to v1.0.2.

• v1.0.2 (September 19, 2020)

  1. From this version, when a blob is sent, the blob is sent to files.

You can check this at https://github.com/tanaikech/FetchApp.

Gists

This is a sample script for retrieving All URLs in Google Document using Google Apps Script. In this sample script, the method of “documents.get” in Google Docs API is used. By this, the URL can be retrieve using JSON.parse().

Sample script

Before you use this script, please enable Google Docs API at Advanced Google Services.

const documentId = "###"; // Please set the Google Document ID.

const content = Docs.Documents.get(documentId).body.content;
const urls = [];
JSON.parse(JSON.stringify(content), (k, v) => {
  if (k == "url") urls.push(v);
});
console.log(urls);

References

• Advanced Google Services
• Method: documents.get

Gists

This is a sample script for using the values submitted from the HTML form using Google Apps Script and Javascript. In this case, the values include the files.

Issue

<form>
  Text: <input type="text" name="sampleText1" /><br />
  Single file: <input type="file" name="sampleFile1" /><br />
  <input
    type="submit"
    name="button"
    value="submit"
    onclick="main(this.parentNode)"
  />
</form>
<script>
  function main(e) {
    google.script.run.sample(e);
  }
</script>

This is a simple sample script for sending the values of form to Google Apps Script. In this case, texts and file are sent. When the button is clicked, main() is run. In this case, this.parentNode is sent to google.script.run.sample(this.parentNode). At that time, at Google Apps Script side, the text value can be correctly retrieved. But the file cannot be correctly sent. When the file is created as a file on Google Drive, the file is broken. I think that the reason of this issue might be due to character code.

Another approach

10 Aug 2022: Report: Challenging Exporting Selected Cells on Spreadsheet as Image using Google Apps Script and Javascript

Old approach

Gists

This is a sample script for converting a range in Google Spreadsheet as an image data using Google Apps Script. Unfortunately, there are no methods for directly converting the range in Google Spreadsheet as an image data in the built-in functions. So in this case, as a workaround, Charts Service is used.