Accessing formulae in Google Sheets

Anvil Q&A
#1

I have a Google Sheet that contains rows with column headers. It’s super easy to use this API, by the way, thank you for that. Two columns contain a formula like =IMAGE("url.com/image.jpg"). Accessing those cells returns an empty string. The documentation suggests print(ws[1, 2].input_value) returns the original formula, but I’m accessing cells by column headers, so I never have a cell object to call input_value on.

I think there are two ways of handling this: either there’s some hidden method that returns the cell address and I then call input_value, or there’s another way of accessing the cell value that doesn’t return an empty string.

How do I access the URL contained in a formula? Thanks folks!

My abbreviated code:

def build_database():
  sheet = app_files.data_spreadsheet
  worksheets = []
  worksheets.append(sheet["Tops"])
  worksheets.append(sheet["Headwear"])
  worksheets.append(sheet["Accessories"])

  for worksheet in worksheets:
    for row in worksheet.rows:
        app_tables.gifts.add_row(
          name=row["name"],
          color1=row["color1"],
          color2=row["color2"],
          style=row["style"],
          closet_image=row["closetimage"][8:-2])
#2

What about using enumerate to loop over fields/rows? This way you can get integer values for fields and rows…and then use the notation you referred to in order to access the cell input value.

#3

Yep, that’ll do it! Untested, quick code for posterity:

for worksheet in worksheets:
  # find the closetimage column, save for later
  for col_num, label in enumerate(worksheet.fields):
    if label == "closetimage":
      img_col_num = col_num
      break
  # TODO: handle worksheets that don't have the label we want
  # iterate over rows, saving the data we want
  for row_num, row in enumerate(worksheet.rows):
    # parse the image URL first
    img_url = worksheet[row_num, img_col_num].input_value[8:-2]
    app_tables.gifts.add_row(
      name=row["name"],
      color1=row["color1"],
      color2=row["color2"],
      style=row["style"],
      closet_image=img_url)
#4

The code above works without much tweaking. The big thing I missed was that Google Sheets rows and columns start their numbering at 1, not 0, so you need to add 1 to row_num and img_col_num to get to the right address.

#5

perfect - enumerate has a start keyword as well. Which I didn’t know until recently:

for row_num, row in enumerate(worksheet.rows, start=1):
4 Likes