Google app script can not insert image to spreadsheet sometimes

Question

My project has been written by Ruby on Rails, since google-api-client gem does not support inserting the image to spreadsheet through sheet apis, so I am using Google app script to handle this task. Most of times, the inserting has been success with this calling:

@app_script.run_script(script_id, request) 

I just provide my script for anyone who're curious: https://script.google.com/a/vectorgroup.vn/d/1ndcgpfJMj3YdKj0pEvHWz0pF4NtcQyR1Qg8wj7ZnpKfIwP2UsH0xaYq4/edit?splash=yes

The problem is sometimes, the calling is failed with below error:

Error detail: [{\"@type\"=>\"type.googleapis.com/google.apps.script.v1.ExecutionError\", \"scriptStackTraceElements\"=>[{\"function\"=>\"addImageOnSpreadsheet\"}], \"errorMessage\"=>\"Exception: Error retrieving image from URL or bad URL: https://drive.google.com/uc?id=1MS6KMfua7kZCSGMUhny4kDUvalxTkoKJ&export=download\", \"errorType\"=>\"ScriptError\"}]" 2020-04-06T06:03:33.821Z pid=26645 tid=tz8bh class=ImageTakerWorker jid=d847bf91beea8aeb4a30b042 elapsed=11.689 INFO: done Error: 3" "Error detail: [{\"@type\"=>\"type.googleapis.com/google.apps.script.v1.ExecutionError\", \"scriptStackTraceElements\"=>[{\"function\"=>\"addImageOnSpreadsheet\"}], \"errorMessage\"=>\"Exception: Error retrieving image from URL or bad URL: https://drive.google.com/uc?id=1G9EDgvygwVztMG66FArZ6BEFpzW71izA&export=download\", \"errorType\"=>\"ScriptError\"}]" 2020-04-06T08:00:28.330Z pid=26645 tid=tz7zl class=ImageTakerWorker jid=a6b4fcb47db15f71dbf1d3f5 elapsed=6.514 INFO: done "Insert image ERROR: #, #" "Error: 3" "Error detail: [{\"@type\"=>\"type.googleapis.com/google.apps.script.v1.ExecutionError\", \"scriptStackTraceElements\"=>[{\"function\"=>\"addImageOnSpreadsheet\"}], \"errorMessage\"=>\"Exception: Error retrieving image from URL or bad URL: https://drive.google.com/uc?id=1_3KzBDDgpINMCNkEZj2LivqdUaxFKZNT&export=download\", \"errorType\"=>\"ScriptError\"}]" 2020-04-06T08:00:28.941Z pid=26645 tid=tzahx class=ImageTakerWorker jid=8010f8c64cb9a0efa672b713 elapsed=7.121 INFO: done

The issue seems happen with url image, somehow google app script could not extract image data. But when issue happen, I just copy and paste above image url in error log to browser manually, it can download the image successfully. Note that I already set the public for image url.

Below is my google app script part:

class GoogleAppScript
  APPLICATION_NAME = "Insert image to spreadsheet".freeze

  def initialize(user_id, sheet_id, options = {})
    @user = User.find(user_id)
    @sheet_id = sheet_id
    @from_class = options[:from_class]
    @service = options[:service]
    @keyword = options[:keyword]
    @sheet_name = options[:service] == "google" ? "G" + options[:keyword].strip : "Y" + options[:keyword].strip
    @image_file_id = options[:image_file_id]
    @google_authorization = GoogleAuthorization.new(@user).authorize
    @app_script = Google::Apis::ScriptV1::ScriptService.new
    @app_script.client_options.application_name = APPLICATION_NAME
    @app_script.authorization = @google_authorization
  end

  def execute
    script_id = "1ndcgpfJMj3YdKj0pEvHWz0pF4NtcQyR1Qg8wj7ZnpKfIwP2UsH0xaYq4"
    url_image = "https://drive.google.com/uc?id=#{@image_file_id}&export=download"

    start_col = 1
    if @from_class == "ImageX"
      start_row = 51
      width = 692
      height = 1500
    else # "ImageY"
      start_row = @keyword.last == " " ? 30 : 9
      width = 694
      height = 418
    end
    request = Google::Apis::ScriptV1::ExecutionRequest.new(
      function: "addImageOnSpreadsheet",
      parameters: [@sheet_id, @sheet_name, url_image, start_col, start_row, width, height],
    )

    begin
      response = @app_script.run_script(script_id, request)
      if response.error
        # Retry until success
        # ImageTakerWorker.perform_async(@user.id, @sheet_id, @sheet_name, @image_file_id)
        p "Insert image ERROR: #{response}, #{response.error}"
        p "Error: #{response.error.code}"
        p "Error detail: #{response.error.details}"
      else
        p "Insert image successfully"
      end
    rescue Google::Apis::ClientError
      # Handle the error...
    end
  end
end

Anyone have experience on this?

Answer 1

This is my understanding:

• In your situation, when an image is inserted to Google Spreadsheet, an error of Server error occurs.
• In this case, it is considered that the size of image is more than the limitation. Reference
• You want to resize the image when the limitation size for inserting to Google Spreadsheet is over.

Issue and workaround:

In this case, even when setWidth and setHeight are used, the error cannot be removed. Because the error occurs when the image is inserted. So in this answer, I would like to propose to insert the image by resizing at a Google Apps Script library.

Modified script:

When your script is modified, it becomes as follows. Before you use this script, please install a GAS library of ImgApp.

function addImageOnSpreadsheet(spreadsheet_id, sheet_name, image_file_id, column, row, width, height) {
  let blobSource = DriveApp.getFileById(image_file_id).getBlob();
  const obj = ImgApp.getSize(blobSource);
  if (obj.height * obj.width > 1048576) {
    blobSource = ImgApp.doResize(image_file_id, width).blob;
  }
  let image = SpreadsheetApp.openById(spreadsheet_id).insertImage(blobSource, column, row);
  image.setWidth(width).setHeight(height);
}

• As a test, I recommend to directly run the script at the script editor.

Note:

• In this case, the following 3 scopes are used. Although I'm not sure about your whole script, please add those scopes. Please be careful this. In your case, please add the scopes and reauthorize again. By this, the valid access token can be used.
  • https://www.googleapis.com/auth/drive
  • https://www.googleapis.com/auth/script.external_request
  • https://www.googleapis.com/auth/spreadsheets

References:

• Limitations for Inserting Images to Google Docs
• ImgApp

Added:

Sample script:

This sample script is for receiving multiple file IDs.

function addImageOnSpreadsheet(ar) {
  ar.forEach(o => {
    let blobSource = DriveApp.getFileById(o.image_file_id).getBlob();
    const obj = ImgApp.getSize(blobSource);
    if (obj.height * obj.width > 1048576) {
      blobSource = ImgApp.doResize(o.image_file_id, o.width).blob;
    }
    var image = SpreadsheetApp.openById(o.spreadsheet_id).insertImage(blobSource, o.column, o.row);
    image.setWidth(o.width).setHeight(o.height);
  });
}

• For example ar is like as follows.

    ar = [
      {spreadsheet_id: "###", image_file_id: "###", column: ##, row: ##, width: ###, height: ###},
      {spreadsheet_id: "###", image_file_id: "###", column: ##, row: ##, width: ###, height: ###},
      ,
      ,
    ];