daily pastebin goal
57%
SHARE
TWEET

Untitled

a guest May 17th, 2018 110 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. #acces the documents on Drive
  2. QC = gc.open_by_key("FIRST KEY").sheet1
  3. lastEdited = gc.open_by_key("SECOND KEY").sheet1
  4.  
  5. #get values from columns and convert to lists
  6. QC_PEID = QC.col_values(4)
  7. lastEdited_PEID = lastEdited.col_values(1)
  8.  
  9. #iterate by rows and check if value from each row appears in the second document
  10. for value in QC_PEID:
  11.     ind = QC_PEID.index(value)
  12.     if value in lastEdited_PEID:
  13.         QC.update_cell(ind, 14, 'Bingo!')
  14.    
  15. for value in QC_PEID:
  16.         ind = QC_PEID.index(value)
  17.         if value in lastEdited_PEID:
  18.             QC.update_cell(ind, 14, 'Bingo!')
  19.             **lastEdited_PEID.remove('value')**
  20.    
  21. for value in QC_PEID:
  22.     ind = QC_PEID.index(value)
  23.     if value in lastEdited_PEID:
  24.         QC.update_cell(ind, 14, 'Bingo!')  # it makes 2 HTTP requests each time
  25.    
  26. # A utility method
  27. def col_cells(worksheet, col):
  28.     """Returns a range of cells in a `worksheet`'s column `col`."""
  29.     start_cell = self.get_addr_int(1, col)
  30.     end_cell = self.get_addr_int(worksheet.row_count, col)
  31.  
  32.     return worksheet.range('%s:%s' % (start_cell, end_cell))
  33.  
  34. QC_PEID = QC.col_values(4)
  35. lastEdited_PEID = set(lastEdited.col_cells(1))  # make the 'in' lookup a bit faster
  36. column_14_cells = col_cells(QC, 14)
  37.  
  38. has_updates = False
  39. # iterate by rows and check if value from each row appears in the second document
  40. for i, value in enumerate(QC_PEID):
  41.     if value in lastEdited_PEID:
  42.         has_updates = True
  43.         column_14_cells[i].value = 'Bingo!'
  44.  
  45. if has_updates:
  46.     QC.update_cells(column_14_cells)
  47.    
  48. import gspread
  49. import os
  50. import datetime
  51. import time
  52.  
  53. from gspread_dataframe import get_as_dataframe
  54.  
  55.  
  56. from oauth2client.service_account import ServiceAccountCredentials
  57.  
  58. #to find next available row
  59. def next_available_row(sheet):
  60.     str_list = list(filter(None, sheet.col_values(1)))  # fastest
  61.     return str(len(str_list)+1)
  62.  
  63. # use creds to create a client to interact with the Google Drive API
  64. def update_spreadsheet(data_list):
  65.  
  66.     scope = ['https://spreadsheets.google.com/feeds']
  67.     basedir = os.path.abspath(os.path.dirname(__file__))
  68.     DATA_JSON = basedir+'/'+'client_secret.json'
  69.     creds = ServiceAccountCredentials.from_json_keyfile_name(DATA_JSON, scope)
  70.  
  71.     client = gspread.authorize(creds)
  72.  
  73.     # Find a workbook by name and open the first sheet
  74.     # Make sure you use the right name here.
  75.     sheet = client.open("mqttsheet").sheet1
  76.  
  77.     next_row = next_available_row(sheet)
  78. #    
  79. #    
  80. #    
  81. #    
  82.     count=int(next_row)
  83.     count-=1
  84.  
  85.  
  86.     #insert on the next available row
  87.     date_today=datetime.date.today()
  88.     time_today=time.strftime("%H:%M:%S")
  89.  
  90.     sheet.update_acell("A{}".format(next_row), count)
  91.     sheet.update_acell("B{}".format(next_row), date_today)
  92.     sheet.update_acell("C{}".format(next_row), time_today)
  93.     sheet.update_acell("D{}".format(next_row), data_list[0])
  94.     sheet.update_acell("E{}".format(next_row), data_list[1])
  95.     sheet.update_acell("F{}".format(next_row), data_list[2])
  96.     sheet.update_acell("G{}".format(next_row), data_list[3])
  97.     sheet.update_acell("H{}".format(next_row), data_list[4])
  98.     sheet.update_acell("I{}".format(next_row), data_list[5])
  99.     sheet.update_acell("J{}".format(next_row), data_list[6])
  100.     sheet.update_acell("K{}".format(next_row), data_list[7])
  101.     sheet.update_acell("L{}".format(next_row), data_list[8])
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top