By updating on May 25, 2022, the content got to be able to be embedded as a full page in the new Google site. Ref. This is very good news for me.

For example, with this update, the Web Apps created by Google Apps Script can be published by embedding to the Google site as a full page. By this, the URL of the Google site, and Google Analytics can be used. I have already changed my Web Applications. You can see them at https://tanaikech.github.io/webapps/

Gists

This is a sample script for replacing values in the cells on Google Spreadsheet using Google Apps Script.

Sample situation

The sample situation is as follows.

Sample script

This sample script used Sheets API. So, please enable Sheets API at Advanced Google services.

function myFunction() {
  const obj = {
    sample1: "ab",
    sample2: "cd",
    sample3: "ef",
    sample4: "gh",
    sample5: "ij",
    sample6: "kl",
    sample7: "mn",
    sample8: "op",
    sample9: "qr",
  };
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetId = ss.getSheetByName("Sheet1").getSheetId(); // Please set the sheet name you want to use this script.

  const requests = Object.entries(obj).map(([k, v]) => ({
    findReplace: {
      find: `(${k})`,
      matchCase: true,
      sheetId,
      replacement: `[${v}]`,
    },
  }));
  requests.push({
    findReplace: { find: ",", matchCase: true, sheetId, replacement: "" },
  });
  Sheets.Spreadsheets.batchUpdate({ requests }, ss.getId());
}

• In this script, one sheet is used. Also, you can use this script to all sheets in a Google Spreadsheet.

Reference

• FindReplaceRequest

Gists

This is a sample script for uploading files to Google Drive with asynchronous process using Python.

Sample script

import aiohttp
import asyncio
import json

folder_id = "###" # Please set the folder ID you want to put.
token = "###" # Please set your access token.
url = "https://www.googleapis.com/upload/drive/v3/files"


async def workers(file):

    async with aiohttp.ClientSession() as session:
        metadata = {"name": file["filename"], "parents": [folder_id]}
        data = aiohttp.FormData()
        data.add_field("metadata", json.dumps(metadata), content_type="application/json; charset=UTF-8")
        data.add_field("file", open(file["path"], "rb"))
        headers = {"Authorization": "Bearer " + token}
        params = {"uploadType": "multipart"}
        async with session.post(url, data=data, params=params, headers=headers) as resp:
            return await resp.json()


async def main():
    # Please set the filenames and the file paths as follows.
    fileList = [
        {"filename": "sample1", "path": "./sample1.png"},
        ,
        ,
        ,
    ]
    works = [asyncio.create_task(workers(e)) for e in fileList]
    res = await asyncio.gather(*works)
    print(res)


asyncio.run(main())

• When this script is run, the files of fileList are uploaded to Google Drive with the asynchronous process.

Note

• This sample supposes that your access token can be used for uploading files to Google Drive using Drive API. Please be careful about this.

Reference

• I answered this sample script for this thread of Stackoverflow

Gists

This is a sample script for parsing JSON data array by expanding the header row using Google Apps Script.

Sample script

function myFunction() {
  const obj = [
    { key1: "value1", key2: "value2", key3: "value3" },
    { key4: "value1", key5: "value2", key6: "value3" },
    { key7: "value1", key8: "value2", key9: "value3" },
  ];

  const headers = Object.keys(
    obj.reduce((o, e) => (Object.keys(e).forEach((k) => (o[k] = true)), o), [])
  );
  const values = [headers, ...obj.map((o) => headers.map((k) => o[k] || null))];

  SpreadsheetApp.getActiveSheet()
    .clearContents()
    .getRange(1, 1, values.length, values[0].length)
    .setValues(values);
}

Testing

When this script is run, the following result is obtained. It is found that the header row is expanded by including all keys.

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.