Image crop with AppScripts in Sheets or Drive

user_3215372 0
Viewed 443

I am evaluating Google Sheets with AppScripts for a report generator. The requirements include inserting images from Google Drive. One of the input image has a fix format, which I need to crop. ( So the dimensions of the original image and the dimensions of the crop is known.)

I am looking for a solution to achieve this outcome, but I am struggling. I looked into the following methods:

  • No image crop function in the Drive API
  • No image crop ( only resize) for the Sheets OverGridImage object
  • No image crop for the =IMAGE() function
  • Manually you can create a drawing, add an image and crop it:
    • But you can't do this from AppScripts
    • You can't access the image within the drawing as an OverGridImage object. ( Which would allow you to replace it's image.)
  • You can use the Slides API replace(imageUrl, crop)
    • Is it possible to embed/link the slide into the sheet via AppScript?

Is there any other workaround? ( Even if it means creating a new file in drive?)

EDIT: Obviously there is the solution to call an external API, or to use GCP services such as CloudFunctions ( which are not free). However I am hoping for an AppScript solution. ( Or using a compatible JS library.)

google-drive-api google-apps-script google-sheets-api google-sheets
1 Answers

I believe your goal as follows.

  • You want to achieve the crop of image using Google Apps Script.

Issue and workaround:

Unfortunately, in the current stage, there are no methods for directly cropping image using Google Apps Script. This has already been mentioned by your comment. So in this answer, I would like to propose a workaround for cropping image using Google Apps Script.

In this workaround, I use Microsoft Powerpoint and Google Slides. The flow of this workaround is as follows.

  1. Create Microsoft Powerpoint (PPTX data) by setting the page size.
    • In this case, the width and height of the image left by the crop are set. This is used as a window of image.
    • When the converted PPTX data is unzipped, the data can be analyzed as the XML data. Fortunately, at Microsoft Docs, the detail specification is published as Open XML. So in this case, Microsoft Docs like XLSX, DOCX and PPTX can be analyzed using XmlService of Google Apps Script. In this workaround, the PPTX data is directly created by including the page size. I think that this method will be also useful for other situations.
    • When the method of "presentations.create" of Slides API can be correctly used, this is not required. Ref
  2. Convert PPTX data to Google Slides.
  3. Insert the image, that you want to do the crop, to the created Google Slides.
    • At that time, the crop of "top" and "left" is subtracted from the origin of coordinates. Because the origin of coordinates of Google Slides is the upper-left corner.
  4. The thumbnail image is retrieved using the method of "presentations.pages.getThumbnail" in Slides API.

By above flow, the cropped image can be retrieved as a blob.

In this sample script, an image is cropped using the parameters using Google Apps Script. In this case, the script reflecting above flow is a bit complicated. So here, I would like to introduce the sample script using a Google Apps Script library. Ref Of course, you can see the whole script there.

Sample script:

Before you use this script, please install ImgApp of the Google Apps Script library. Ref And in this case, please enable Drive API and Slides API at Advanced Google services.

function myFunction() {
  const id = "###"; // If you want to crop the image on Google Drive, please set the file ID of the image here.

  const object = {blob: DriveApp.getFileById(id).getBlob(), crop: {t: 50, b: 100, l: 200, r: 100}};
  const blob = ImgApp.editImage(object);
  DriveApp.createFile(blob);
}
  • This sample script crops an image on Google Drive and the cropped image is created to the root folder.
    • crop: {t: 50, b: 100, l: 200, r: 100} means that the crop size of t, b, l and r are the top, bottom, left and right, respectively. The unit is pixel.
  • About the maximum image size, you can see it at Limitations for Inserting Images to Google Docs. In this sample script, 25,000,000 pixels^2 is the maximum size of the image.

References:

user_71086530
Related