RichTextApp was updated to v1.3.0
You can see the detail information here https://github.com/tanaikech/RichTextApp
Gists
There is Class TextFinder in Spreadsheet service for Google Apps Script. Ref The TextFinder can search and replace the texts in the Spreadsheet using Google Apps Script. There is the method for createTextFinder in Class Spreadsheet, Class Sheet and Class Range. When these methods are used, you can search and replace the texts for all sheets in a Spreadsheet, the specific sheet, and the specific range in the specific sheet.
Google is proud to announce the Google Forms API! The Forms API is currently available in Restricted Beta, with Open Beta expected to follow in Q4.
Forms API
Gists
This is a sample script for sending multiple emails using the batch request with Gmail API using Google Apps Script. When multiple emails are sent using “GmailApp.sendEmail” and “MailApp.sendEmail”, a loop is used. But in this case, the process cost becomes high. In this post, I would like to introduce the sample script for reducing the process cost under this situation. Gmail API can be requested with the batch request. The batch request can be processed with the asynchronous process. By this, I thought that the process cost for sending multiple emails. So, this sample script sends multiple emails using the batch request with Gmail API.
Gists
This is a sample script for putting all response values from Google Form to Google Spreadsheet using Google Apps Script.
Sample script
Please copy and paste the following script to the script editor of Google Spreadsheet and set the variables of formId and sheetName.
function myFunction() {
const formId = "###"; // Please set the Google Form ID.
const sheetName = "Sheet1"; // Please set the sheet name of sheet you want to put the values.
// Retrieve all response values from Google Form.
const form = FormApp.openById(formId);
const headers = ["date", ...form.getItems().map(e => e.getTitle())];
const values = [headers, ...form.getResponses().map((f) => {
const timeStamp = f.getTimestamp();
return f.getItemResponses().reduce((o, i) => {
const r = i.getResponse();
return Object.assign(o, {
[i.getItem().getTitle()]: Array.isArray(r) ? r.join(",") : r,
});
}, { date: timeStamp });
}).map((o) => headers.map((t) => o[t] || ""))];
// Put the values to Spreadsheet.
SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getRange(1, 1, values.length, values[0].length).setValues(values);
}
- When this script is run, all response values are retrieved from Google Form and put them to the Spreadsheet.
Note
- At Google Form, when the empty answer is submitted, the question has no value. By this, it is required to consider this. So at first, the titles are retrieved from the items, and the values are created using the item titles. I thought that this might be an important point.
References
- This sample script was used for the following threads.
Gists
In this report, it has investigated the large decimal numbers and the exponential notation for Google Spreadsheet. When the large decimal numbers are put to the Spreadsheet, the Spreadsheet automatically sets the display value using the exponential notation. In this report, the result when the values are retrieved by Spreadsheet service and Sheets API is shown.
Sample script
At first, please create new Spreadsheet and open the script editor. And please copy and paste the following script. And, please enable Sheets API at Advanced Google services.
Regardless if you use Cloud to build, modernize, train, teach, or even for fun - in our eyes you are an Innovator.
The Innovators program is here to accelerate your learning and growth on Google Cloud, and to recognize you for the contributions you make to the broader Cloud community.
We recognize all these individuals for being at the top of their game technically - and for going further to inspire, invigorate, and challenge the Google Cloud community and our product teams by sharing their technical knowledge and contributing to conferences, open source projects, forums, blogs, workshops, community events, and social media.
Gists
This is a sample script for sending Gmail with the title and body including Emoji using Google Apps Script.
Sample script
This sample script uses Gmail API. So please enable Gmail API at Advanced Google services. Ref
const convert_ = ({ to, emailFrom, nameFrom, subject, textBody, htmlBody }) => {
const boundary = "boundaryboundary";
const mailData = [
`MIME-Version: 1.0`,
`To: ${to}`,
nameFrom && emailFrom ? `From: "${nameFrom}" <${emailFrom}>` : "",
`Subject: =?UTF-8?B?${Utilities.base64Encode(
subject,
Utilities.Charset.UTF_8
)}?=`,
`Content-Type: multipart/alternative; boundary=${boundary}`,
``,
`--${boundary}`,
`Content-Type: text/plain; charset=UTF-8`,
``,
textBody,
``,
`--${boundary}`,
`Content-Type: text/html; charset=UTF-8`,
`Content-Transfer-Encoding: base64`,
``,
Utilities.base64Encode(htmlBody, Utilities.Charset.UTF_8),
``,
`--${boundary}--`,
].join("\r\n");
return Utilities.base64EncodeWebSafe(mailData);
};
// Please run this function.
function main() {
const obj = {
to: "###", // Please set the email for `to`.
emailFrom: "###", // Please set the email for `from`.
nameFrom: "sample name",
subject: "Hello World 😃⭐",
textBody: "sample text body 😃⭐",
htmlBody: "<p>Hello World 😃⭐</p>",
};
Gmail.Users.Messages.send({ raw: convert_(obj) }, "me");
}
- In order to use Emoji in the subject, the subject value is converted to the base64 data. And, it is used as
=?UTF-8?B?###?=. Ref
- About the method for including Emoji to the email body, I have answered it at this thread of Stackoverflow.
Reference
Gists
This is a sample script for compiling the continuous numbers using Google Apps Script. For example, the values of [4, 5, 9, 3, 10, 5, 11, 7, 7, 13, 1] are converted to ["1","3-5","7","9-11","13"].
Sample script
const compilingNumbers = (ar) => {
const { values } = [...new Set(ar.sort((a, b) => a - b))].reduce(
(o, e, i, a) => {
if (
o.temp.length == 0 ||
(o.temp.length > 0 && e == o.temp[o.temp.length - 1] + 1)
) {
o.temp.push(e);
} else {
if (o.temp.length > 0) {
o.values.push({ start: o.temp[0], end: o.temp[o.temp.length - 1] });
}
o.temp = [e];
}
if (i == a.length - 1) {
o.values.push(
o.temp.length > 1
? { start: o.temp[0], end: o.temp[o.temp.length - 1] }
: { start: e, end: e }
);
}
return o;
},
{ temp: [], values: [] }
);
return values;
};
// Please run this function.
function main() {
const ar = [4, 5, 9, 3, 10, 5, 11, 7, 7, 13, 1]; // This is sample values.
const values = compilingNumbers(ar);
console.log(values);
const res = values.map(({ start, end }) =>
start == end ? start.toString() : `${start}-${end}`
);
console.log(res);
}
When this script is run, console.log(values) and console.log(res) show [{"start":1,"end":1},{"start":3,"end":5},{"start":7,"end":7},{"start":9,"end":11},{"start":13,"end":13}] and ["1","3-5","7","9-11","13"], respectively. From this result, it is found that the continuous numbers were compiled.
Gists
This is a sample script for sending Outlook emails using Microsoft account with Google Apps Script.
Before you use this script, please install OnedriveApp which is Google Apps Script library. Ref And, please authorize your Microsoft account for using Microsoft Graph API. Ref
Sample script
function myFunction() {
const obj = [
{
to: [{ name: "### name ###", email: "### email address ###" }, , ,],
subject: "sample subject 1",
body: "sample text body",
cc: [{ name: "name1", email: "emailaddress1" }, , ,],
},
{
to: [{ name: "### name ###", email: "### email address ###" }, , ,],
subject: "sample subject 2",
htmlBody: "<u><b>sample html body</b></u>",
attachments: [blob],
bcc: [{ name: "name1", email: "emailaddress1" }, , ,],
},
];
const prop = PropertiesService.getScriptProperties();
const odapp = OnedriveApp.init(prop);
const res = odapp.sendEmails(obj);
console.log(res);
}
In this sample script, 2 emails are sent using Microsoft Graph API with your Microsoft account. By this, both Outlook Emails and Google Emails can be used by Google Apps Script.