Gists

This sample script decrypts the salted base64 data of finance.yahoo.com using Google Apps Script.

Recently, when I saw the HTML of finance.yahoo.com, I noticed that the data is converted by the salted base64. In order to decrypt the data, it is required to use the key data. But, unfortunately, I couldn’t find the key data from the HTML. When I searched for it, I found this thread. From the thread, I could retrieve the key data. By this, I could a script for decrypting the salted base64.

Gists

This is a sample script for encrypting and decrypting with AES using crypto-js with Google Apps Script.

Unfortunately, in the current stage, Google Apps Script cannot encrypt and decrypt AES using the built-in functions. In this post, in order to achieve this, “crypto-js” is used from cdnjs.com ( https://cdnjs.com/libraries/crypto-js ). In the current stage, it seems that the main functions of crypto-js.min.js can be directly used with Google Apps Script. But, unfortunately, all functions cannot be used. Please be careful about this.

Gists

This is a sample script for rearranging columns on Google Spreadsheet using Google Apps Script.

Sample script

In this sample script, the initial columns of “header1”, “header2”, “header3”, “header4” and “header5” are rearranged to “header2”, “header5”, “header1”, “header4”, “header3”. This result can be seen at the above image.

As an important point, in this script, the header titles in the 1st row are used. Please be careful about this.

Gsits

This is a sample script for retrieving the values from a publicly shared Google Spreadsheet using an API key with Javascript.

Sample script

In this sample script, googleapis for Javascript is used.

<script async defer src="https://apis.google.com/js/api.js" onload="handleClientLoad()"></script>
<script>
function handleClientLoad() {
  const apiKey = "###"; // Please set your API key.
  const spreadsheetId = "###"; // Please set your Spreadsheet ID.

  gapi.load('client', async () => {
    await gapi.client.init({ apiKey, discoveryDocs: ["https://sheets.googleapis.com/$discovery/rest?version=v4"] });
    const { result } = await gapi.client.sheets.spreadsheets.values.get({ spreadsheetId, range: "Sheet1" });
    console.log(result);
  });
}

• In this sample, I used Method: spreadsheets.values.get. Of course, in this case, you can use the methods of Method: spreadsheets.get, Method: spreadsheets.values.batchGet.

Gists

In this post, I would like to introduce 2 sample scripts for copying and deleting the dimension groups in Google Spreadsheet using Google Apps Script.

Unfortunately, in the current stage, all dimension groups cannot be copied by one action, and also, all dimension groups cannot be deleted by one action. In this post, these can be achieved using Google Apps Script.

Gists

This report is a workaround for reflecting the latest Google Apps Script to the deployed Web Apps without redeploying.

Pattern 1

Of course, when the developer mode of https://script.google.com/macros/s/###/dev is used, the latest script can be used without redeploying.

But, in this case, only the permitted users can use it using the access token. when you want to achieve this using the endpoint of https://script.google.com/macros/s/###/exec without the access token, in order to reflect the latest script to Web Apps, it is required to redeploy. As another pattern, I would like to introduce a workaround for this situation.

Gists

This is a sample script for searching Gmail messages by Gmail Filters using Google Apps Script.

At Gmail, users can set the filter for the Emails. With this filter, users can filter the Emails of Gmail. But, when the users want to search by the installed filter using Google Apps Script, unfortunately, it seems that this cannot be directly achieved. For example, messages cannot be searched using a filter ID.

Gists

These are sample scripts for retrieving specific folders from Google Drive using Google Drive service (DriveApp) with Google Apps Script.

Retrieving folders in own Google Drive

const folders = DriveApp.searchFolders(
  `'${Session.getActiveUser().getEmail()}' in owners and trashed=false`
);
const res = [];
while (folders.hasNext()) {
  const folder = folders.next();
  res.push(folder.getName());
}
console.log(res);

Retrieving folders in shared Drives

const folders = DriveApp.searchFolders(
  `not '${Session.getActiveUser().getEmail()}' in owners and trashed=false`
);
const res = [];
while (folders.hasNext()) {
  const folder = folders.next();
  const owner = folder.getOwner();
  if (owner === null) {
    res.push(folder.getName());
  }
}
console.log(res);

Retrieving folders of sharedWithMe

const folders = DriveApp.searchFolders(`sharedWithMe`);
const res = [];
while (folders.hasNext()) {
  const folder = folders.next();
  res.push(folder.getName());
}
console.log(res);

Gists

This is a workaround for converting a Gmail message to a PNG image using Google Apps Script.

Sample script

Please set the message ID of Gmail.

function myFunction() {
  var id = "###"; // Please set your message ID of Gmail.

  var message = GmailApp.getMessageById(id);
  var date = Utilities.formatDate(
    message.getDate(),
    Session.getScriptTimeZone(),
    "yyyy-MM-dd HH:mm:ss"
  );
  var from = message.getFrom();
  var to = message.getTo();
  var subject = message.getSubject();
  var htmlBody = message.getBody();
  var imageBlob = Charts.newTableChart()
    .setDataTable(
      Charts.newDataTable()
        .addColumn(Charts.ColumnType.STRING, "")
        .addRow([`<p style="font-size: 120%">Date: ${date}</p>`])
        .addRow([`<p style="font-size: 120%">From: ${from}</p>`])
        .addRow([`<p style="font-size: 120%">To: ${to}</p>`])
        .addRow([`<p style="font-size: 120%">Subject: ${subject}</p>`])
        .addRow([htmlBody])
        .build()
    )
    .setOption("allowHtml", true)
    .setDimensions(512, 512)
    .build()
    .getBlob();

  DriveApp.createFile(imageBlob.setName("sample.png"));
}

• In this sample script, the HTML body is used.

My report has been featured by Google Workspace Developer Newsletter. Ref

The featured report is “Report: Implementing Pseudo 2FA for Web Apps using Google Apps Script”.