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)
}
