how to check item is exists and renew value only in python gspread

Question

How to check item is exists and renew value only, if not exists and add new one?

For example:

I have an item 1 and value 1 already in my sheet, and then I get new value of item 1, I want to renew value 1 only, otherwise, if I get new item 2 and value 2, I want to add this in new columns.

I don't know how to write code, I search it long time but cannot found, could anyone help me? Many thanks!

The script below, the steps are:

first step, check my gmail get keyword 1

second, use keyword search datas in website (beautifulsoup module)

the last step, upload datas to google sheet (gspread module)

def Check_emailbox(box='Inbox', lab='SUBJECT', title='[PASS]'):    

    global email_content, report_info1, my_msg, report_info

    dirpath = 'XXX'

    with open(dirpath) as act:
        content = act.read()

    my_act = yaml.load(content, Loader=yaml.FullLoader)
    user, password = my_act['user'], my_act['password']

    imapUrl = 'imap.gmail.com'
    my_mail = imaplib.IMAP4_SSL(imapUrl)
    my_mail.login(user, password)
    print('Login gmail account seccess.')

    my_mail.select(box)
    key = lab                                       
    value = title                                   
    _, data = my_mail.search(None, key, value)
    mail_id_list = data[0].split()
    msg_id = mail_id_list[-1]    
    res, data = my_mail.fetch(msg_id, '(RFC822)')
    report_info = []
    if res == 'OK':
        raw_msg_txt = data[0][1]
        try:
            my_msg = email.message_from_bytes(raw_msg_txt)
            print('Subject: ', my_msg['subject'])
            print('From: ', my_msg['from'])
            print('Time: ', my_msg['date'])
            print('------------------------------------------------------------------------------------')
            print('Content:')
            for part in my_msg.walk():
                email_content = part.get_payload()
                report_info.append(email_content)
                report_info1 = ''.join('%s' % id for id in report_info)
                print(report_info1, type(report_info1))
                # print('Hide info, if want to see detail, unmark previous code')
            print('------------------------------------------------------------------------------------')
            # my_mail.store(msg_id, '-FLAGS', '\SEEN')

        except AttributeError:
            my_msg = email.message_from_string(raw_msg_txt)
            print('AttributeError: ', my_msg)


    return email_content, my_msg, report_info, report_info1

Check_emailbox()

keyName = re.findall(r'Daily Report :  (.*?)$', report_info1)
fwName = ''.join(keyName)
print(fwName) 
# ↑ This data will be upload to sheet, and this is main item for check:
#    if "feName" is exists, renew below datas only, if not exists, add new one in next row.
    

fwVersion = ''.join(re.findall(r'\d-(.*?)-', fwName)).rsplit('.',1)[0]
print(fwVersion)   

# connect to the website and use beautifulsoup
ele = requests.get('XXXXXX')
felement = BeautifulSoup(ele.text, 'html.parser')
# print(felement.prettify())

fwinfo = felement.find(['a'], text = fwName)
fwhref = fwinfo.get('href')
print('Info: ', fwinfo)
print(fwhref)

rowid = ''.join(re.findall(r'data/(.*?)$', fwhref))
print('Download id is: ', rowid)

fwlink = 'XXXXXXXXX' + rowid
print('Download link: ', fwlink)

json_key = "XXXXXXX"
spread_url = ['https://spreadsheets.google.com/feeds']
connect_auth = SAC.from_json_keyfile_name(json_key, spread_url)
google_sheets = gspread.authorize(connect_auth)
sheet = google_sheets.open_by_key('XXXXXXXXX').worksheet('Pass Data')
Sheets = sheet

upload = []
upload.append(fwName)
upload.append(fwVersion)
upload.append(rowid)
upload.append(fwlink)

Sheets.append_row(upload)

print('==== Uplod to Google Sheet Done. ====')

Answer 1

In your situation, how about the following modification?

Modified script:

In this case, please use your google_sheets.

# Please set your values here.
fwName = "###"
fwVersion = "###"
rowid = "###"
fwlink = "###"

sheet = google_sheets.open_by_key('XXXXXXXXX').worksheet("Pass Data")
values = sheet.get_all_values()[2:]
obj = {}
for i, r in enumerate(values):
    obj[r[0]] = i + 3
if obj.get(fwName):
    sheet.update("B" + str(obj.get(fwName)), [[fwVersion, rowid, fwlink]], value_input_option="USER_ENTERED")

• When this script is run, first, the values are retrieve from the sheet. And, by searching the value of column "A", new value is put to the searched row.

Note:

• I prepared this modified script using your sample image. In your sample image, the 1st 2 rows are header rows. And, the search column is the column "A". I used them. So, when you change your Spreadsheet, this script might not be able to be used. Please be careful about this.

References:

• update(range_name, values=None, **kwargs)
• get_all_values(**kwargs)