CODEWITHSUNDEEP
pythongspread
Liz
Liz

Reputation: 11

Should I be able to use gspread with Python to update a cell in Google Sheets with a formula/function?

I'm trying to set a cell value to a formula/function to use in the Google Sheet, the function will reference other tabs in the workbook. Whenever it updates the cell with the function, it has an apostrophe (') at the beginning of the function. When I remove the apostrophe in Google Sheets, the function performs as I hoped but I'm trying to automate this process.

This is the code I use to set the cell value:

func = '=IF(NOT(ISERROR(MATCH("' + c + '", Dictionary!A:A, 0))), VLOOKUP("' + c + '", Dictionary!A:D, 4,FALSE), "' + c + '")'

cells.append(
    Cell(row=rnum+1, col=cnum+1, value=func)

The function is sent to the Google Sheet but looks like this, I don't see the apostrophe in the cell until I click on it: '=IF(NOT(ISERROR(MATCH("Word", Dictionary!A:A, 0))), VLOOKUP("Word", Dictionary!A:D, 4,FALSE), "Word")

Is this a bug? When I debug, I am able to see the function as a string with no apostrophes except for those that the object uses to classify it as a string, which makes me wonder if the gspread library is accidentally sending the function to the cell with the apostrophe.

EDIT: The work around I found was to use update_acell to find the specific cells that needed the formula. It's not perfect but it works for now. I'd still love to know why the extra apostrophe is being added. Thank you!!

Upvotes: 1

Views: 1280

Answers (1)

Tanaike
Tanaike

Reputation: 201338

When you put the value of cells using update_cells, how about this modification?

Modification points:

  • In this modification, value_input_option="USER_ENTERED" is added to update_cells. By this, the formula is put to the cell as the formula.
  • When update_cells is used without value_input_option="USER_ENTERED", the default value of value_input_option is RAW. By this, ' is added to the top character of the formula. I think that this is the reason of your issue.

Modified script:

func = '=IF(NOT(ISERROR(MATCH("' + c + '", Dictionary!A:A, 0))), VLOOKUP("' + c + '", Dictionary!A:D, 4,FALSE), "' + c + '")'
cells.append(Cell(row=rnum+1, col=cnum+1, value=func))

worksheet.update_cells(cells, value_input_option="USER_ENTERED")  # Modified

References:

Upvotes: 1

Related Questions