pythongoogle-sheetsgspreadinsert-image

Insert images in Google Sheets specific cell with gspread

I would like to insert image to google sheet, like in the attached screenshot.

And following is my code, but it's failed.

cell_list = getsheet.range('A24:I44')
with open(image_path, 'rb') as f:
    image_data = f.read()
getsheet.update_cells(cell_list)

enter image description here

Solution

  • From your following reply,

    Yes, I want to put an image over the cells, for seeing the image quickly and conveniently. How can I use Google Apps Script? Can it be used with python script?

    The sample scripts of Google Apps Script and Python are as follows.

    Usage:

    1. Prepare Spreadsheet

    Please prepare a Google Spreadsheet and open the script editor.

    2. Google Apps Script

    Please copy and paste the following sample script to the script editor.

    function doPost(e) {
  if (!e || !e.postData) {
    console.error("No event object");
    return ContentService.createTextOutput("No event object");
  }
  const { spreadsheetId, sheetName, row, column, imageData, mimeType } = JSON.parse(e.postData.contents);
  SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName).insertImage(`data:${mimeType};base64,${imageData}`, column, row);
  return ContentService.createTextOutput("Done.");
}

    3. Deploy Web Apps

    The detailed information can be seen in the official document.

    Please set this using the script editor.

    1. On the script editor, at the top right of the script editor, please click "click Deploy" -> "New deployment".
    2. Please click "Select type" -> "Web App".
    3. Please input the information about the Web App in the fields under "Deployment configuration".
    4. Please select "Me" for "Execute as".
    5. Please select "Anyone" for "Who has access to the app:".
      • In this sample, as a simple approach, the request can be done without the access token.
    6. Please click "Deploy" button.
    7. On the script editor, at the top right of the script editor, please click "click Deploy" -> "Test deployments".
    8. Copy Web Apps URL. It's like https://script.google.com/macros/s/###/exec.

    4. Testing

    In order to test this, please use the following Python script. Please set your Web Apps URL to webAppsUrl. And, please set the file and path of the PNG image to file, your Spreadsheet ID, and sheet name to obj.

    If your image is Jpeg, please modify mimeType to image/jpeg.

    import base64
import json
import requests


file = "sample.png"
webAppsUrl = "https://script.google.com/macros/s/###/exec"
obj = {
    "spreadsheetId": "###",
    "sheetName": "Sheet1",
    "row": "2",
    "column": "2",
    "imageData": base64.b64encode(open(file, 'rb').read()).decode('ascii'),
    "mimeType": "image/png"
}
res = requests.post(webAppsUrl, data=json.dumps(obj))
print(res.text)

    When this script is run, when the above Web Apps is correctly deployed, an image is put over the cells as the anchor cell "B2".

    Note:

    References: