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.

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.