Gists

Introduction

On March 14, 2022, it reported about “Google Sheets doubles cell limit”. Ref By this update, now, the users can use 10,000,000 cells in a Google Spreadsheet. This is great news for us. When I tried to handle 10,000,000 cells in a Google Spreadsheet using Google Apps Script, it was found that there were various important points. In this report, I would like to introduce the important points for handling 10,000,000 cells in Google Spreadsheet using Google Apps Script.

Gists

This is a sample script for replacing the multiple paragraphs on Google Document with a regex using Google Apps Script. There is the method of replaceText(searchPattern, replacement) for replacing the text on Google Document. Ref For example, when a text of sample1 is replaced with sample2, this can be achieved by a script like DocumentApp.getActiveDocument().getBody().replaceText("sample1", "sample2"). But, when the texts for replacing are the multiple paragraphs, this script cannot be used. Namely, it is considered that in the current stage, replaceText cannot be used for replacing the multiple paragraphs. In this post, I would like to introduce a sample script for replacing the texts with the multiple paragraphs using Google Apps Script.

Gists

This is a sample script for simply converting HTML to plain text using Google Apps Script.

Sample values

HTML (input value)

<div id="sample1">sample text1</div>
<div id="sample2">sample text2</div>
<ul id="sample3">
  <li>sample list 1</li>
  <li>sample list 2</li>
</ul>
<table id="sample4">
  <tbody>
    <tr>
      <td>a1</td>
      <td>b1</td>
      <td>c1</td>
    </tr>
    <tr>
      <td>a2</td>
      <td>b2</td>
      <td>c2</td>
    </tr>
  </tbody>
</table>

Text (output value)

sample text1
sample text2

   - sample list 1
   - sample list 2

a1 b1 c1
a2 b2 c2

Sample script

function myFunction() {
  const sampleHTML = `<div id="sample1">sample text1</div>
<div id="sample2">sample text2</div>
<ul id="sample3">
  <li>sample list 1</li>
  <li>sample list 2</li>
</ul>
<table id="sample4">
  <tbody>
    <tr>
      <td>a1</td>
      <td>b1</td>
      <td>c1</td>
    </tr>
    <tr>
      <td>a2</td>
      <td>b2</td>
      <td>c2</td>
    </tr>
  </tbody>
</table>`;
  const temp = GmailApp.createDraft("", "", "", { htmlBody: sampleHTML });
  const plainText = temp.getMessage().getPlainBody();
  temp.deleteDraft();
  console.log(plainText);
}

• This method uses GmailApp.createDraft for converting HTML to plain text. When a draft email is created with GmailApp.createDraft by giving an HTML body, when the message content is retrieved with getPlainBody(), the plain text is retrieved. This method uses this situation.
• When this sample script is run, the result in “Sample values” section can be obtained.

Note

• This method is a simple conversion from HTML to plain text. So I’m not sure whether this method can be used for all HTML data. Please be careful about this.

References

• createDraft(recipient, subject, body, options)
• getPlainBody()

Gists

This is a sample Google Apps Script for processing the values in Google Spreadsheet. In this sample situation, each row is merged using the same header title.

In this sample script, the sample input and output situations are as follows.

Sample situation

Input:

Output:

On April 13, 2022, “Additional functionality for the Apps Script Integrated Development Environment (IDE) Script Editor” has been reported.

They say as follows.

We’re now adding several new features to the IDE to help achieve functional parity with the legacy IDE experience. These features are:

• Script Properties
• Add-on Testing
• Time Zone Setting
• Rhino Debugging

By this update, I believe that the script editor will be more useful.

Gists

This is a sample script for creating quizzes in Google Form using Google Forms Service with Google Apps Script.

Usage

1. Prepare questions and answers.

In this sample, the questions and answers are prepared using Spreadsheet as follows.

2. Sample script.

This script is container-bound script of the above Spreadsheet.

Gists

This is a sample script for creating quizzes in Google Form using Google Forms API with Google Apps Script. Recently, Google Forms API has been officially published, and it got to be able to be used by users. By this, quizzes in Google Form can be created using Google Forms API.

Here, there is one thing that can be achieved by Google Forms API. When Google Forms API is used, each choice in each question can be shuffled. This cannot be achieved with Google Forms Service (FormApp).

Gists

This is a sample script for retrieving the summary of Google Document using Google Apps Script. Recently, a blog of Auto-generated Summaries in Google Docs has been posted. I thought that this is very interesting function. I thought that when this function is released, checking each summary of a lot of Google Document will be much useful for simply confirming the document content. And also, I thought that when all summaries can be retrieved using a script, it will be also useful. In this post, I would like to introduce to retrieve the summary of Google Document using Google Apps Script.

Gists

This is a sample Google Apps Script for processing the values in Google Spreadsheet. In this sample situation, each column are merged using the same header title.

In this sample script, the sample input and output situations are as follows.

Sample situation

Input: “Sheet1”

Output: “Sheet2”

Gists

Introduction

In this report, I would like to introduce to apply the spread syntax and the destructuring assignment to Google Spreadsheet with Google Apps Script. The destructuring assignment can be used without V8 runtime. But, the spread syntax is required to be used with V8 runtime. Recently, I often saw the script using them at Stackoverflow. And also, I have sometimes gotten the questions related to the spread syntax and the destructuring assignment. So, I thought that I would like to introduce in my blog.