Gists

This is a sample script for converting the values of Google Spreadsheet to an object using Google Apps Script.

Sample script

function myFunction() {
  const sheetName = "Sheet1";
  const [headers, ...rows] = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName).getDataRange().getValues();
  const res = rows.map((r) => headers.reduce((o, h, j) => Object.assign(o, { [h]: r[j] }), {}));
  console.log(res);
}

• When this script is run, the above sample image can be retrieved.

• In this sample script, the 1st row of the sheet is used as the header row.

RichTextApp was updated to v1.3.0

• v1.3.0 (October 20, 2021)

  1. Added a new method of RangeToHTMLTableForSpreadsheet. In this method, the range on Google Spreadsheet is converted to a HTML table. Using this method, for example, you can send the specific range in the Spreadsheet as an email by including a HTML table.

• You can see the sample HTML table at https://jsfiddle.net/oq9x458e/.
• I used this method for this thread of Stackoverflow.

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.

Announcing the Google Forms API

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.
  • Storing all forms answers in one single file
  • How can I get the responses directly form a Google Form and send then to a new spreadsheet?

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.

Google Cloud Innovators

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.

Champions

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

• I have answered this sample script at this thread of Stackoverflow

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.