Gists

This is a sample script for retrieving the start and end of the month in a year using Google Apps Script and Javascript.

Sample script

function myFunction() {
  const year = 2023; // Please set year you expect.
  const res = [...Array(12)].map((_, i) =>
    [0, 1].map((e) => new Date(year, i + e, 1 - e))
  );
  console.log(res);

  console.log(res.map(([a, b]) => [a.toDateString(), b.toDateString()]));
}

Testing

https://jsfiddle.net/mLrhqwgo/

When this script is run, the following value is obtained with console.log(res.map(([a, b]) => [a.toDateString(), b.toDateString()])).

[
  ["Sun Jan 01 2023", "Tue Jan 31 2023"],
  ["Wed Feb 01 2023", "Tue Feb 28 2023"],
  ["Wed Mar 01 2023", "Fri Mar 31 2023"],
  ["Sat Apr 01 2023", "Sun Apr 30 2023"],
  ["Mon May 01 2023", "Wed May 31 2023"],
  ["Thu Jun 01 2023", "Fri Jun 30 2023"],
  ["Sat Jul 01 2023", "Mon Jul 31 2023"],
  ["Tue Aug 01 2023", "Thu Aug 31 2023"],
  ["Fri Sep 01 2023", "Sat Sep 30 2023"],
  ["Sun Oct 01 2023", "Tue Oct 31 2023"],
  ["Wed Nov 01 2023", "Thu Nov 30 2023"],
  ["Fri Dec 01 2023", "Sun Dec 31 2023"]
]

Gists

Published: January 3, 2023

Kanshi Tanaike

Introduction

At Stackoverflow, a lot of people post questions and answers to the questions every day. There are various tags in Stackoverflow. A lot of discussions are performed at each tag. Their discussions bring important information and are much useful for a lot of people. As one of the tags, there is “google-apps-script”. I sometimes discuss the questions with that tag. When we see the discussions, we can notice that the discussions are changed and progressed over time, because “Google Apps Script” which is the origin of the tag is updated. This report thinks this change is the trend of the tag of “google-apps-script”. This trend includes the number of questions, questioners, answerers, and tags added to the tag of “google-apps-script”. The trend of the tag of “google-apps-script” is deeply related to the progression of Google Apps Script and the various applications for Google Apps Script.

Gists

This is a simple sample script for filtering JSON objects using Google Apps Script.

In the current stage, V8 runtime can be used with Google Apps Script. By this, when you want to filter a JSON object, you can use the following sample script.

Sample script

In this sample script, obj is filtered by the value of the even number.

const obj = { key1: 1, key2: 2, key3: 3, key4: 4, key5: 5 };
const res = Object.fromEntries(
  Object.entries(obj).filter(([, v]) => v % 2 == 0)
);
console.log(res); // {"key2":2,"key4":4}

• When v % 2 == 0 is modified to v % 2 == 1, you can filter the JSON object with the odd number like {"key1":1,"key3":3,"key5":5}.

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.