Search code examples
pythongoogle-sheetsgoogle-sheets-apigspread

Image sent to Google Sheets with gspread python appears tiny

I need to send image to google sheets using gspread. I am not finding documentation for a function that will work appropriately for it on docs.gspread.org. As shown in my code snippet below, I am currently using the command

worksheet.append_row(['testpic.png', '=IMAGE(\"{}\")'.format(
    'https://raw.githubusercontent.com/eliasdon/picturetest/main/download.png')], value_input_option='USER_ENTERED')

but the picture that gets appended is very tiny and cannot be resized even manually as shown in the attached picture

enter image description here

I found this guide on google sheets enter image description here but I wasn't able to get it to work. I tried to implement it like so:

worksheet.append_row(['IMAGE("https://www.google.com/images/srpr/logo3w.png", 4, 50, 100)'], value_input_option='USER_ENTERED')
worksheet.append_row(['IMAGE("https://www.google.com/images/srpr/logo3w.png", 4, 50, 100)'])

Code:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# google scope
scope = ["https://spreadsheets.google.com/feeds", 'https://www.googleapis.com/auth/spreadsheets',
         "https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/drive"]

# read credentials from json
creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", scope)

# get access using creds
client = gspread.authorize(creds)

# open sheet
sheet = client.open("test")

# open worksheet witin sheet
worksheet = sheet.worksheet('check')

# append pic
worksheet.append_row(['testpic.png', '=IMAGE(\"{}\")'.format(
    'https://raw.githubusercontent.com/eliasdon/picturetest/main/download.png')], value_input_option='USER_ENTERED')

How do I send picture to google sheets and make it scale either to original size or to a set dimension (x,y)?

Solution

  • I thought that in your situation, in order to change the size of image, it is required to change the size of the cell. When this is reflected to your script, it becomes as follows.

    Modified script:

    From:

    worksheet.append_row(['testpic.png', '=IMAGE(\"{}\")'.format(
    'https://raw.githubusercontent.com/eliasdon/picturetest/main/download.png')], value_input_option='USER_ENTERED')

    To:

    res = worksheet.append_row(['testpic.png', '=IMAGE(\"{}\")'.format(
    'https://raw.githubusercontent.com/eliasdon/picturetest/main/download.png')], value_input_option='USER_ENTERED')

width = 300 # The cell width. By this, the width of image is changed.
height = 200 # The cell height. By this, the height of image is changed.
row = int(re.findall("\\w+![A-Z]+([0-9]+)", res['updates']['updatedRange'], re.S)[0])
requests = [{
    "updateDimensionProperties": {
        "properties": {
            "pixelSize": height
        },
        "range": {
            "sheetId": worksheet.id,
            "dimension": "ROWS",
            "startIndex": row - 1,
            "endIndex": row
        },
        "fields": "pixelSize"
    }
},
    {
    "updateDimensionProperties": {
        "properties": {
            "pixelSize": width
        },
        "range": {
            "sheetId": worksheet.id,
            "dimension": "COLUMNS",
            "startIndex": 1,
            "endIndex": 2
        },
        "fields": "pixelSize"
    }
}
]
spreadsheet.batch_update({"requests": requests})

    • In this modification, import re is also used.

    • When you use this script, the cell size of 2nd column of the appended row is changed.

    References: