Can Python determine if there is an Image in a Google Sheet cell?

Question

By my understanding, there are two methods to add an Image to a cell in a Google Sheet.

• 1 : By using the =IMAGE formula
• 2 : By going through Insert > Image > Insert image in cell

I have a "database" sheet that contains images. It's a pain to maintain, so I'm automating it using Method 1, but there is already a large number using Method 2.

The Issue: Python (or perhaps more specifically, gspread) doesn't seem to be able to detect an Image when method 2 is used. This makes separating cells that do/don't contain images needlessly difficult.

Question: Is it possible? If so, how?

Provided is the minimal reproducible example

import gspread

gc = gspread.service_account(JSON_filePath)
spreadsheet = gc.open(sheet_name)

sheet = spreadsheet.worksheet("Images")

data = sheet.get_all_values()

for row in data:
   print(row)

I've added an extra row with the formula: =ARRAYFORMULA(if(ISBLANK(C2:C),TRUE,FALSE)) This successfully reads if the cell contains an image or not. However, I do not want this column in the spreadsheet if I can avoid it.

The images below show example rows using Method 2. The First image is the output of the above script, and the second is the corresponding Google Sheet screenshot.

Answer 1

I think that in the current stage, the method of get_all_values() cannot detect the existence of the image in a cell. But, I guessed that when "Method: spreadsheets.get" of Sheets API is used, this might be able to be achieved.

When this is reflected in a sample script, it becomes as follows.

I noticed that from your showing script, you are using gspread. So, in this sample, the client of gspread is used.

Sample script:

import gspread
from googleapiclient.discovery import build

client = gspread.service_account(JSON_filePath) # This client is from your showing script.

# Please set the Spreadsheet ID and the a1Notation of range.
spreadsheetId = "###"
a1Notation = "Sheet1!C1:C"

service = build("sheets", "v4", credentials=client.auth)
obj = service.spreadsheets().get(spreadsheetId=spreadsheetId, fields="sheets(data(rowMetadata,rowData(values(userEnteredValue))))", ranges=[a1Notation]).execute()
data = obj["sheets"][0]["data"][0]
res = []
lenRowData = len(data["rowData"])
lenRowMetadata = len(data["rowMetadata"])
for i in range(lenRowMetadata):
    if lenRowData > i and "values" in data["rowData"][i]:
        v = data["rowData"][i]["values"][0]
        if "userEnteredValue" in v and (len(v["userEnteredValue"].keys()) == 0 or "formulaValue" in v["userEnteredValue"]):
            res.append("Image")
            continue
    res.append("No image")
print(res)

For example, when the images are put into column "C" of "Sheet1" in your showing sample Spreadsheet, please use a1Notation = "Sheet1!C1:C".

As a sample result, in your showing spreadsheet image, the following result is obtained. It supposes that the first "No image" is row 1178 in this sample result.

[
  ,
  ,
  ,
  "No image",
  "Image",
  "Image",
  "Image",
  "No image",
  "No image",
  "No image",
  "Image",
  "Image",
  "No image",
  "No image",
  "Image",
  "No image",
  ,
  ,
  ,
]

Note:

• From your question, this sample script supposes that the cells of your column have manually put images, images put by formula, and empty. Please be careful about this.

Reference:

• Method: spreadsheets.get