Gists
This is a sample script for converting the relative reference to the absolute reference and vice versa of A1Notation on Google Spreadsheet using Google Apps Script.
Description
A1Notation is used in the cells on Google Spreadsheet.
As the 1st sample, it supposes that a formula of =A1 is put into a cell “B1”. Under this condition, when the cell “B1” is copied to “B2” and “C1”, the cells “B2” and “C1” have the formulas of =A2 and =B1, respectively. This is the relative reference.
Gists
This is a sample script for comparing the file contents of files on Google Drive using Google Apps Script.
Sample script
Before you use this script, please enable Drive API at Advanced Google services. And also, please set the file IDs you want to check whether the file contents of the files are the same.
function checkFiles_(f, checks = ["md5Checksum", "sha1Checksum", "sha256Checksum"]) {
files = f.map(id => DriveApp.getFileById(id));
const fields = [...checks, "id"].join(",");
const o = files.reduce((o, f) => {
const mimeType = f.getMimeType();
if (["FOLDR", "SHORTCUT"].some(m => mimeType == MimeType[m])) {
throw new Error("Folders cannot be checked.");
}
let obj;
if (mimeType.includes("application/vnd.google-apps")) {
const name = f.getName();
f.setName("temp");
Utilities.sleep(2000);
obj = Drive.Files.insert({ title: "temp", mimeType: MimeType.PDF }, f.getBlob(), { supportsAllDrives: true, fields });
f.setName(name);
Drive.Files.remove(obj.id); // If an error occurs, please use DriveApp.getFileById(obj.id).setTrashed(true);
} else {
obj = Drive.Files.get(f.getId(), { supportsAllDrives: true, fields });
}
checks.forEach(e => o[e] = o[e] ? [...o[e], obj[e]] : [obj[e]]);
return o;
}, {});
return Object.values(o).every(e => [...new Set(e)].length == 1);
}
// Please run this function.
function main() {
const file1 = "###fileId1###"; // Please set your file ID of file 1.
const file2 = "###fileId2###"; // Please set your file ID of file 2.
const check = checkFiles_([file1, file2]);
const res = `"${file1}" and "${file2}" are${
check ? " " : " not "
}the same data.`;
console.log(res);
}
Gists
This is a sample script for exporting Google Documents as HTML with the image hyperlinks using Google Apps Script.
Recently, it seems that the specification for exporting Google Documents as HTML data has been changed. When a Google Document are exported as HTML data before, the images in the Google Document were the image hyperlinks, which are publicly shared. But, in the current stage, when a Google Document is exported as HTML data, the images in the Google Document are the data URL (base64 data) of the images. I guess that this might be related to enhancing the security. When the Google Document is exported as a ZIP file, the HTML and images are separated. But, in this case, the images are required to be included in a specific folder like “/images”. I’m worried that this might bring another issue.
Gists
This is a sample script for retrieving the release notes of Google Apps Script and Google APIs from RSS using Google Apps Script.
Recently, the release notes of Google Apps Script and Google APIs have been published as RSS. By this, the data got to be able to be easily retrieved using XmlService of Google Apps Script. Knowing the latest release notes will be useful for developing the applications. So, I would like to introduce the sample script for retrieving this information.
Gists
This is a sample script for inserting the paragraphs with the checkboxes in Google Documents using Google Apps Script.
In the current stage, Google Documents can create paragraphs with checkboxes as the paragraph bullet. But, unfortunately, this cannot be created by the Google Document service (DocumentApp). Fortunately, it seems that this got to be able to be achieved by Google Docs API. In this post, I would like to introduce a sample script for this.
Gists
Introduction
This is a simple workaround for starting an animation GIF on Google Slide by clicking.
When an animation GIF is inserted into a slide of Google Slides, the animation is automatically started. By this, the timing for starting cannot be controlled by the user side. In this post, I would like to introduce a workaround for resolving this issue.
Preparation
The sample flow is as follows.
Prepare a sample animation GIF.
- Create a new Google Slide.
- Insert the animation GIF into a slide.
- Insert a rectangle shape of the same size as the animation GIF, and put over this to the animation GIF. And then, please set the order of the created shape to “Send to back”. By this, the animation GIF can be seen.
- Select the animation GIF and open “Motion” from “Insert” → “Animation”.
- Use “Appear (On Click)”.
By this flow, the preparation is finished.
Overview
This is a Google Apps Script library for easily managing the template of Google Documents and Google Slides using Google Spreadsheet as a database using Google Apps Script.
Description
You might have situations where are required to create multiple Google Documents and Google Slides from the templates using Google Spreadsheet as a database with Google Apps Script. When the simple texts are replaced with the placeholders on the templates, this can be achieved by a simple script. However, there is the case that the images are required to be inserted into the templates. In this case, the script is complicated. The method for replacing the placeholders with the images is different between Google Documents and Google Slides. And, when I see questions on Stackoverflow, a lot of questions related to the template process using Google Documents and Google Slides can be seen. Furthermore, I personally have a lot of questions related to this from other users. From this situation, I thought that when managing the template process can be simply run, it will be useful for a lot of users. So, I created this library.
My report has been published at Champion Innovators Content Library and Google Cloud Medium publication.
If these reports will be useful, I’m glad.
Gists
Abstract
Recently, I felt a change like never before in the questions on Stackoverflow. In order to confirm this, in this report, the trend of “google-apps-script” tag on Stackoverflow in the first half (January 1st to June 1st) of 2023 has been investigated. From this report, in 2023 when the affection of COVID-19 has been reduced socially, the appreciable trend was confirmed to the questions including a tag of “google-apps-script”. It is guessed that the origin of this appreciable trend is due to AI chatbots. The appearance of AI chatbots might give us a phase of major change to the method for understanding the statistical data obtained from online sites.
Gists
Abstract
This report introduces the method for easily implementing HTML forms with a Google Spreadsheet as a database using Google Apps Script. There are 2 patterns for the HTML form using Google Apps Script. One is that an HTML form is put into the same Google Apps Script project. Another is that an HTML form is put to a different server from a Google Apps Script project. In this report, the methods for easily implementing both patterns are introduced using the sample scripts.