RichTextApp was updated to v1.4.0

• v1.4.0 (May 25, 2022)

  1. Added a new method of ReplaceTextToRichText. In this method, the text in a cell is converted to the richtext.

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

Gists

This is a sample script for exporting the Tabulator data to Google Drive using Google Apps Script.

As the sample, a dialog on Google Spreadsheet is used. So, please copy and paste the following scripts to the container-bound script of Google Spreadsheet.

Google Apps Script side: Code.gs

const saveFile = (e) => DriveApp.createFile(Utilities.newBlob(...e)).getId();

// Please run this script.
const openDialog = (_) =>
  SpreadsheetApp.getUi().showModalDialog(
    HtmlService.createHtmlOutputFromFile("index"),
    "sample"
  );

HTML & Javascript side: index.html

<link
  href="https://cdnjs.cloudflare.com/ajax/libs/tabulator/5.2.4/css/tabulator.min.css"
  rel="stylesheet"
/>
<script
  type="text/javascript"
  src="https://cdnjs.cloudflare.com/ajax/libs/tabulator/5.2.4/js/tabulator.min.js"
></script>

<div id="table"></div>
<input type="button" value="ok" onclick="download();" />
<script>
  const type = "csv"; // In this sample, you can select "csv" or "json".
  const filename = "sample"; // Please set the filename.

  const table = new Tabulator("#table", {
    data: [...Array(5)].map((_, i) =>
      [...Array(5)].reduce(
        (o, _, j) => ((o[`sample${j + 1}`] = `sample${i + 1}`), o),
        {}
      )
    ),
    autoColumns: true,
    downloadReady: function (fileContents, blob) {
      const fr = new FileReader();
      fr.onload = (e) =>
        google.script.run
          .withSuccessHandler((id) => console.log(id))
          .saveFile([
            [...new Int8Array(e.target.result)],
            blob.type,
            `${filename}.${type}`,
          ]);
      fr.readAsArrayBuffer(blob);
      return false;
    },
  });

  function download() {
    table.download(type, `${filename}.${type}`);
  }
</script>

• When openDialog is run with the script editor, a dialog is opened on Spreadsheet. And, you can see the table (as shown in the top of this post) and a button. When “ok” button is clicked, this table is exported as a CSV data and save it as a file in the root folder of Google Drive.

Gists

This is a sample script for requesting to Gate API v4 using Google Apps Script.

The official document of Gate API v4 is here. Recently, I answered this thread. In that case, in order to convert the sample python script to Google Apps Script, the script for retrieving the signature might be a bit complicated. So, here, I would like to introduce this.

Sample python script from official document

This is a sample python script from official document.

Gists

This is a sample script for retrieving the text positions in the text data using Google Apps Script.

For example, in order to set the rich text style the part of text from the text data, this sample script will be useful.

Sample situation 1

The sample situation is as follows.

sample1, sample2, sample3, sample4, sample5
sample1, sample2, sample3, sample4, sample5
sample1, sample2, sample3, sample4, sample5

In this sample, the text positions of sample2 and sample5 are retrieved from this sample text data.

Gists

This is a sample script for retrieving and parsing the XML data from Google Workspace Update Blog and putting it to Google Spreadsheet using Google Apps Script.

At Google Workspace Update Blog, the XML data is provided. By this, the retrieved XML data is parsed with XmlService, and the data is put to Google Spreadsheet. Recently, I got a request for this. So I created this sample script. When this was useful for your situation, I’m glad.

Gists

This is a sample script for retrieving the values of subscriberCount of the channel from the video URLs of YouTube using Google Apps Script.

In this sample, the video URLs are retrieved from Spreadsheet. And, the retrieved values of subscriberCount are put to the Spreadsheet. The sample Spreadsheet is as follows.

Sample script

Please copy and paste the following script to the script editor of Spreadsheet. Before you use this script, please enable YouTube Data API v3 at Advanced Google services. Ref And, please set the sheet name.

Gists

This is a sample script for splitting and processing an array every n length using Google Apps Script. When I prepare a sample script with Google Apps Script, I sometimes have the situation that it is required to split and process an array every n length. This sample script is for achieving this situation.

Please set limit. This sample script splits the sample array every 3 length.

When you use this script with Google Apps Script, please enable V8 runtime.

Gists

This is a report for obtaining the values from GOOGLEFINANCE using Google Apps Script. When I tested to retrieve the values from GOOGLEFINANCE function on Google Spreadsheet using Google Apps Script, I noticed that the values can be retrieved.

When I had tested this before, I had got the value of #N/A. About retrieving the values from GOOGLEFINANCE function on Google Spreadsheet, I had known “Historical GOOGLEFINANCE data no longer accessible outside of Google Sheets”. By this situation, #N/A had been returned when the value had been retrieved using a script.

Gists

This is a sample script for reducing the image data size using Google Apps Script. You might have a situation where you might want to reduce the data size of image data using Google Apps Script. Here, using Google Apps Script, I would like to introduce a sample script for reducing the data size of the image data by reducing the image quality.

Limitations

In the current stage, by the specification of Google side, there are the following limitations.

Gists

This is a sample script for expanding the rows in Google Spreadsheet using Google Apps Script. The sample situation is as follows.

Sample situation

Input

Output

Sample script

function myFunction() {
  const expandedColumns = [2, 3, 4, 5]; // Please set the expanded columns you expect.
  const delimiter = "\n"; // Please set the delimiter.
  const srcSheetName = "Sheet1"; // Please set the source sheet name.
  const dstSheetName = "Sheet2"; // Please set the destination sheet name.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [srcSheet, dstSheet] = [srcSheetName, dstSheetName].map((s) =>
    ss.getSheetByName(s)
  );
  const [head, ...values] = srcSheet.getDataRange().getValues();
  const res = [
    head,
    ...values.flatMap((r) => {
      const { v, max } = expandedColumns.reduce(
        (o, c, i) => {
          const s = r[c - 1].split(delimiter);
          o.v[c - 1] = s;
          const len = s.length;
          if (i == 0) {
            o.max = len;
          } else {
            o.max = o.max > len ? o.max : len;
          }
          return o;
        },
        { v: {}, max: 0 }
      );
      return [...Array(max)].map((_, j) =>
        r.map((c, k) => (!v[k] ? c : v[k][j] || null))
      );
    }),
  ];
  dstSheet.getRange(1, 1, res.length, res[0].length).setValues(res);
}

• When this script is run, the above sample situation can be obtained.
• For example, when you change const expandedColumns = [2, 3, 4, 5]; to const expandedColumns = [5];, only the column “E” is expanded.