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. ====')
CodePudding user response:
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.