How to upload a blob (image) into a google sheet using google sheet api v 4

Question

I have a blob data. I would like to upload this to a cell in google sheet using google sheet API v4.

I have looked at the documentation here. https://developers.google.com/sheets/api/guides/values

I have also looked at SO questions here. Insert image into Google Sheets cell using Google Sheets API

result = service.spreadsheets().values().update(
spreadsheetId=spreadsheet_id, range=range_name,
valueInputOption=value_input_option, body=body).execute()

I am not seeing any service described to insert the blob as the image. Please help.

After suggestions below, we implemented the Webapp from here - Insert image into Google Sheets cell using Google Sheets API

This is how we are calling the web app from our python code

        dropoff_signature = "ZGF0YT <clip > WVhSaA=="
        web_app_url     = "https://script.google.com/macros/s/A < clip > y/exec"        
        image_data  = "data:image/png;base64," + dropoff_signature
        data_to_post = {            
            'spreadsheetid' : spreadsheet_Id, 
            'sheetname' : 'Sheet1',     
            'imageurl'  : image_data,              
            'column'    : 5, 
            'row'       : 5             
            }
        encoded_data = urllib.urlencode(data_to_post)
        # Send encoded data to application-2
        url_result = urlfetch.fetch(web_app_url, encoded_data, method='POST')           

We are seeing the following error in our Webapp.

result : 200 content : {"status":"error","defaultMessage":"Error retrieving image from URL or bad URL: data:image/png;base64, <clip> ","name":"Exception","fileName":"Code (Insert image into spreadsheet)","lineNumber":42,"stack":"\tat Code (Insert image into spreadsheet):42 (doPost)\n"}}

Can you please help?

Made this change. Still getting the bad URL error.

dropoff_signature = "ZGF0YTpp<clip>WVhSaA=="
        web_app_url     = "https://script.google.com/macros/s/A<clip>y/exec"        
        image_data  = "data:image/png;base64," + dropoff_signature
        data_to_post = {            
            'spreadsheetid' : spreadsheet_Id, 
            'sheetname' : 'Sheet1',     
            'imageurl'  : image_data,              
            'column'    : 5, 
            'row'       : 5             
            }
        # encoded_data = urllib.urlencode(data_to_post)
        # Send encoded data to application-2
        # url_result = urlfetch.fetch(web_app_url, encoded_data, method='POST')         
        url_result = urlfetch.fetch(url=web_app_url, payload=json.dumps(data_to_post), method='POST', headers={'Content-type': 'application/json'})



result : 200 content : {"status":"error","defaultMessage":"Error retrieving 
image from URL or bad URL: 
data:image/png;base64,Z<clip>A==","error": 
{"message":"Error retrieving image from URL or bad URL: data:image/png;base64,Z<clip>A==","name":"Exception","fileName":"Code (Insert image into spreadsheet)","lineNumber":42,"stack":"\tat Code (Insert image into spreadsheet):42 (doPost)\n"}}

Here is the Webapp that we are using.

function doGet(e) {
  return ContentService.createTextOutput("Authorization: Bearer " + 
  ScriptApp.getOAuthToken())
}

//
// Example curl command to insert an image:
// 
// curl -L -d '{ "spreadsheetid": "1xNDWJXOekpBBV2hPseQwCRR8Qs4LcLOcSLDadVqDA0E","sheetname": "Sheet1", "imageurl": "https://www.google.com/images/srpr/logo3w.png", "column": 1, "row": 1 }' \
// -H "Authorization: Bearer <INSERT TOKEN RETURNED FROM GET HERE>" \
// -H 'Content-Type: application/json' \
// https://script.google.com/a/tillerhq.com/macros/s/AKfycbzjFgIrgCfZTvOHImuX54G90VuAgmyfz2cmaKjrsNFrTzcLpNk0/exec
//

var REQUIRED_PARAMS = [
  'spreadsheetid', // example: "1xNDWJXOekpBBV2hPseQwCRR8Qs4LcLOcSLDadVqDA0E"
  'sheetname',     // Case-sensitive; example: "Sheet1"
  'imageurl',      // Can be an url such as "https://www.google.com/images/srpr/logo3w.png"
                   // or alternately "data:image/png;base64,iVBOR...<snip>...gg=="
  'column', // 1-based (i.e. top left corner is column 1)
  'row'     // 1-based (i.e. top left corner is row 1)
];

function doPost(e) {

  var result = {
    status: "ok",
    defaultMessage: "Image inserted."
  }

  try {
    var params = (e.postData && e.postData.type == "application/x-www-form-urlencoded") ? e.parameter
    : (e.postData && e.postData.type == "application/json") ? JSON.parse(e.postData.contents)
    : undefined;


    if (!params) throw new Error('Unsupported content-type, must be either application/x-www-form-urlencoded or application/json.');

    REQUIRED_PARAMS.forEach(function(requiredParam) {
      if (!params[requiredParam]) throw new Error('Missing required parameter ' + requiredParam);
    });

    SpreadsheetApp.openById(params.spreadsheetid).getSheetByName(params.sheetname).insertImage(params.imageurl, params.column, params.row);  

  } catch(e) {

    console.error(e); 

    result.status = "error";
    result.error = e;
    result.defaultMessage = e.message;

  }  

  return ContentService.createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON)  
}

Answer 1

Solution 1:

From your Python application you can use the following code to set an image with the IMAGE formula using Sheets API [1]. You would need to put your Spreadsheet ID and change the range where you want the image.

spreadsheet_id = '[SPREADSHEET-ID]'
range_name = 'D13'

service = build('sheets', 'v4', credentials=creds)

values = [
    [
       '=IMAGE("https://google.com","google")'
    ]
]
body = {
    'values': values
}
result = service.spreadsheets().values().update(
    spreadsheetId=spreadsheet_id, range=range_name,
    valueInputOption='USER_ENTERED', body=body).execute()

Solution 2:

If instead you want to use the insertImage function [2] from Apps Script, to insert an over the grid image into the Sheets and not an image linked to a cell. You can deploy a Web App [3] with a doPost() function where you can do this and call the Web App from your Python application using the service account credentials. Also you need to deploy the Web App to execute as "user accessing the web app" so all the requests you do from the Web App will be made with the service account credentials.

Python script:

from google.oauth2 import service_account
import requests
import json
import google.auth.transport.requests

SCOPES = ['https://www.googleapis.com/auth/drive', 'https://www.googleapis.com/auth/spreadsheets']
SERVICE_ACCOUNT_FILE = 'service_account.json'

credentials = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES)

delegated_credentials = credentials.with_subject('[USER-EMAIL-TO-IMPERSONATE]')
delegated_credentials.refresh(google.auth.transport.requests.Request())
token = delegated_credentials.token

headers = {'content-type': 'application/json', 'Authorization': 'Bearer ' + token}
url = '[WEB-APP-URL]'
data = {"file": '[blob]'}
response = requests.post(url, data=json.dumps(data), headers=headers)

Web App script:

function doPost(e) { 
  var ss = SpreadsheetApp.openById('[SPREADSHEET-ID]');
  var sheet = ss.getSheets()[0];
  var blob = DriveApp.getFileById("[IMAGE-ID]").getBlob();
  sheet.insertImage(blob, 4, 14);

  return ContentService.createTextOutput("Good");
}

I tested my code with an image obtained from Drive in the Web App. You could skip that part and send the blob directly from your Python application in the data payload.

To use a service account remember to give API access to all the scopes needed, you need to go to admin.google.com->security->Setting->Advance Settings->Manage API client access and use the service account client ID.

[1] https://developers.google.com/sheets/api/guides/values

[2] https://developers.google.com/apps-script/reference/spreadsheet/sheet#insertImage(BlobSource,Integer,Integer)

[3] https://developers.google.com/apps-script/guides/web