Gists

This is a sample script for achieving the pseudo OnEdit trigger for Google Spreadsheet without the simple and the installable triggers using Google Apps Script.

Today, I saw a question at Stackoverflow. The goal of this question is as follows.

• There is a Google Spreadsheet created by a service account.
• Goal is to use OnEdit trigger on this Spreadsheet.

I thought a workaround for achieving this goal.

Gists

On October 12, 2021, Google Forms API had been announced. Ref On December 7, 2021, Google Forms API had been released as open beta. Ref In the current stage, when the users join the Early Adopter Program, they can use Google Forms API of the beta version. Ref

By using Google Forms API, what Google Forms service cannot achieve got to be able to be achieved by Google Forms API. In this report, I would like to introduce how to use Google Forms API and the sample script of the situations which cannot be achieved by Google Forms service.

Now, Google Forms API can be used in open beta. The official post is https://workspaceupdates.googleblog.com/2021/12/google-forms-api-now-available-in-open.html.

Official document of Forms API

After V8 runtime was released, there was a bug that when the file is sent from HTML form to Google Apps Script side using google.script.run, the file blob was the invalid data.

From Apps Script Pulse by Martin Hawksey, it was found that the invalid blob of sending the file of HTML form to Google Apps Script side using google.script.run has finally been resolved. In this case, this script can be used with V8 runtime.

Gists

This is a sample script for retrieving the data from Content-Type of ’text/event-stream’ using Javascript and Google Apps Script.

In the current stage, UrlFetchApp of Google Apps Script cannot be retrieved the data from Content-Type of ’text/event-stream’. This sample script can be used for achieving this as a workaround.

This sample script uses EventSource. So this script uses a dialog on Google Docs files (This sample uses Google Spreadsheet.).

Usage

1. Prepare Spreadsheet.

Create new Google Spreadsheet.

Gists

This is a sample script for achieving the pseudo OnEdit trigger for Google Document using Google Apps Script.

In the current stage, there is not OnEdit trigger for Google Document. Ref But I sometimes have the case that I want to use OnEdit trigger. So, as the current workaround, I created this sample script. I think that this workaround can be also used for Google Slides, Google Form and also Google Spreadsheet. In the case of Spreadsheet, what the existing OnEdit trigger and the time-driven trigger cannot do can be achieved.

Gists

This is a workaround for converting the range ID to the range object on Google Spreadsheet using Google Apps Script.

When the named range is put to a cell as the hyperlink as follows,

the hyperlink is like #rangeid=123456789. When this link is clicked, it moves to the cells of the named range. So it is considered that this value of #rangeid=123456789 includes the information about the range of the named range. But, unfortunately, in the current stage, there is no methods for directly retrieving the range object from the range ID. In this sample sample script, as a workaround, the range ID is converted to the range object using Google Apps Script.

Gists

This is a sample script for exporting all thumbnail images retrieved from Google Slides as a zip file using Google Apps Script.

Sample script

Before you use this script, please enable Slides API at Advanced Google services. Ref

function myFunction() {
  const presentationId = "###"; // Please set Google Slides ID.
  const folderId = "###"; // Please set the folder ID.
  const outputFilename = "###"; // Please set the output filename.

  const blobs = SlidesApp.openById(presentationId)
    .getSlides()
    .map((e, i) =>
      UrlFetchApp.fetch(
        Slides.Presentations.Pages.getThumbnail(
          presentationId,
          e.getObjectId(),
          {
            "thumbnailProperties.mimeType": "PNG",
            "thumbnailProperties.thumbnailSize": "LARGE",
          }
        ).contentUrl
      )
        .getBlob()
        .setName(`page${("000" + (i + 1)).slice(-3)}.png`)
    );
  DriveApp.getFolderById(folderId).createFile(
    Utilities.zip(blobs).setName(outputFilename + ".zip")
  );
}

ResumableUploadForGoogleDrive_js was updated to v2.0.0.

• v2.0.0 (November 15, 2021)

  1. New Class ResumableUploadToGoogleDrive2 was added. By this, the large file which is over the memory in the local PC can be uploaded by the resumable upload.

Overview

This is a Javascript library to achieve the resumable upload for Google Drive.

Description

When a file more than 5 MB is uploaded to Google Drive with Drive API, the resumable upload is required to be used. I have already published the sample script for “Resumable Upload for Web Apps using Google Apps Script”. Ref In this case, Web Apps is used. Here, I would like to introduce the script for the resumable upload created by only Javascript. Unfortunately, in the current stage, at google-api-javascript-client, there are no methods for the resumable upload. And, I thought that when this function is created as a Javascript library, it might be useful for users. Also that library is also useful for me. So I created this. If this was useful for your situation, I’m glad.

Gists

This is a sample workaround for letting users running Google Apps Script on Google Spreadsheet without both authorizing the scopes and showing the script.

The flow of this workaround is as follows.

1. Create Web Apps created by Google Apps Script and deploy it as Web Apps. As the returned value, the XML data is returned.
   • Your script can be included in this script.
2. User put a formula of =IMPORTML("WebApps URL", "xpath") to a cell.

By this flow, you can achieve to let users running Google Apps Script on Google Spreadsheet without both authorizing the scopes and showing the script.