Search code examples
pythongoogle-sheetsgoogle-sheets-apigspread

Python/gspread - Changing contents of a cell based on contents of another cell

I'm just starting out with Python (v3.8) and am trying to apply it to a real project at work. I used a video tutorial for the Google Sheets API and have been able to manipulate cell data using it. Now, I am trying to use the syntax covered in the video to manipulate cell contents in this way, and I'm having trouble:

Let's say I have 20 rows + 10 columns of data - dates, short comments, etc. It should check for a particular string ("Sales") in each row of column H, then depending on whether or not that string is found, enter "Yes" or "No" in a blank column at the end (same row), say column J.

pip install gspread oauth2client
import gspread
from oauth2client.service_account import ServiceAccountCredentials

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"]

creds = ServiceAccountCredentials.from_json_keyfile_name("this_file.json", scope)

client = gspread.authorize(creds)

sheet = client.open("Spreadsheet1").sheet1

data = sheet.get_all_records()

column_H = sheet.range(8)
for p in column_H:
    if "Sales" in cell.value:
        sheet.update_cell("Yes")  #I'd like it to update the cell 2 rows to the right (column J)
    else:
        sheet.update_cell("No")

This is what I've got so far. As you can see, there's nothing in there that tells it to update the corresponding column J cell in the same row. And there might be other problems here too. Like I said, just starting.

Edit: Could a solution (conceptually) be something like...

    if "Sales" in cell.value:
        cell.value + 2 = "Yes"  #+2 to denote updating value of cell two to the right, rather than the cell it's evaluating
    else:
        cell.value + 2 = "No"

Just spitballin' here.

Solution
    • You want to put the value of "Yes" to the column "J" when the column "H" has the value of "Sales".
    • You want to put the value of "No" to the column "J" when the column "H" has NO the value of "Sales".
    • You want to achieve this using gspread with python.
    • You have already been able to get and put values for Google Spreadsheet with Sheets API.

    If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

    Modification point:

    • In this modification, at first, the values are retrieved from the column "H". After the value of "Sales" is checked for the retrieved values, the result values are put to the column "J".

    Modified script:

    Please modify your script as follows.

    From: 
    data = sheet.get_all_records()

column_H = sheet.range(8)
for p in column_H:
    if "Sales" in cell.value:
        sheet.update_cell("Yes")  #I'd like it to update the cell 2 rows to the right (column J)
    else:
        sheet.update_cell("No")
    To: 
    data = sheet.get_all_values()
values = ["Yes" if e[7] == "Sales" else "No" for e in data]
cells = sheet.range("J1:J%d" % len(values))
for i, e in enumerate(cells):
    e.value = values[i]
sheet.update_cells(cells)

    Note:

    • If you don't want to put "No", please modify to values = ["Yes" if e == "Sales" else "No" for e in data].

    References: