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.

My post of “Sending Multiple Emails using Batch Request with Gmail API using Google Apps Script” was featured at Google Workspace Developer Newsletter on October 2021.

Gists

This is a sample script for uploading a movie file on Google Drive to YouTube using Google Apps Script.

Before you use these scripts, please enable YouTube API at Advanced Google services. Ref

Sample script 1

This sample script uses YouTube API at Advanced Google services.

function myFunction() {
  const fileId = "###"; // Please set the file ID of movie file on the Google Drive.

  const res = YouTube.Videos.insert(
    { snippet: { title: "sample title", description: "sample description" } },
    ["snippet"],
    DriveApp.getFileById(fileId).getBlob()
  );
  console.log(res);
}

Sample script 2

This sample script requests to the endpoint of YouTube API with UrlFetchApp by creating the request body. This script requests with multipart/form-data.

Gists

This is a sample script for updating the values of “Sheet A” with the values of “Sheet B” using Google Apps Script. I often see this situation at Stackoverflow and other sites. So, in this post, I would like to introduce the sample script using Google Apps Script.

Sample script

function myFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const [src, dst] = ["Sheet1", "Sheet2"].map((e) => ss.getSheetByName(e));
  const obj = src
    .getRange("A2:B" + src.getLastRow())
    .getValues()
    .reduce((o, [a, b]) => ((o[a] = b), o), {});
  const values = dst
    .getRange("A2:A" + dst.getLastRow())
    .getValues()
    .map(([b]) => [obj[b] || ""]);
  dst.getRange(2, 2, values.length, 1).setValues(values);
}

Of course, this situation can be also achieved with the built-in formula of Spreadsheet. For example, when the above image is used, the same result with the column “B” can be obtained at the column “C” by putting a formula of =ARRAYFORMULA(VLOOKUP(A2:A11,Sheet2!A2:B6,2)) to the cell “C2”.

Gists

This is a sample script for reducing the table height of the table inserted from Google Spreadsheet to Google Slides using Google Apps Script.

Sample script

Please copy and paste the following script to the script editor of Google Slides. This sample script uses Slides API. So, please enable Slides API at Advanced Google services. Ref

Gists

This is a sample script for retrieving the glyph value from the list items of Google Document using Google Apps Script.

In the current stage, when the list is put to the Google Document, the count of glyph is automatically calculated. When the glyph values of the list items are tried to be retrieved using the manual operation and the script, unfortunately, the glyph values cannot be retrieved. Only the values of the list are retrieved. Unfortunately, it seems that in the current stage, there are no methods for directly retrieving the glyph value from the list items.

Gists

This is a report about images put with “=IMAGE(IMAGE_URL)” function on Google Spreadsheet.

Experiment

When “=IMAGE(IMAGE_URL)” is put to a cell “A1” on Spreadsheet, the image is shown in the cell as shown in the following image.

For this situation, when the following script is run,

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const range = sheet.getRange("A1");
range.copyTo(range, { contentsOnly: true });

The following result is obtained. In this case, the formula is removed and an image can be seen as shown in the following image.

GitHub

This is a sample script for the file picker using Google Apps Script and Javascript without 3rd party. I had created the same sample script before. Ref But, in the case of that script, jQuery is used. And, only Google Drive of own account could be used. In this sample script, 3rd party of jQuery is not used, and also, not only Google Drive of your own account, but also Google Drive of the service account can be used. By this, I thought that this file picker will be useful for various scenes.