#acces the documents on Drive
QC = gc.open_by_key("FIRST KEY").sheet1
lastEdited = gc.open_by_key("SECOND KEY").sheet1

#get values from columns and convert to lists
QC_PEID = QC.col_values(4)
lastEdited_PEID = lastEdited.col_values(1)

#iterate by rows and check if value from each row appears in the second document
for value in QC_PEID:
    ind = QC_PEID.index(value)
    if value in lastEdited_PEID:
        QC.update_cell(ind, 14, 'Bingo!')

for value in QC_PEID:
        ind = QC_PEID.index(value)
        if value in lastEdited_PEID:
            QC.update_cell(ind, 14, 'Bingo!')
            **lastEdited_PEID.remove('value')**

for value in QC_PEID:
    ind = QC_PEID.index(value)
    if value in lastEdited_PEID:
        QC.update_cell(ind, 14, 'Bingo!')  # it makes 2 HTTP requests each time

# A utility method
def col_cells(worksheet, col):
    """Returns a range of cells in a `worksheet`'s column `col`."""
    start_cell = self.get_addr_int(1, col)
    end_cell = self.get_addr_int(worksheet.row_count, col)

    return worksheet.range('%s:%s' % (start_cell, end_cell))

QC_PEID = QC.col_values(4)
lastEdited_PEID = set(lastEdited.col_cells(1))  # make the 'in' lookup a bit faster
column_14_cells = col_cells(QC, 14)

has_updates = False
# iterate by rows and check if value from each row appears in the second document
for i, value in enumerate(QC_PEID):
    if value in lastEdited_PEID:
        has_updates = True
        column_14_cells[i].value = 'Bingo!'

if has_updates:
    QC.update_cells(column_14_cells)

import gspread
import os
import datetime
import time

from gspread_dataframe import get_as_dataframe


from oauth2client.service_account import ServiceAccountCredentials

#to find next available row
def next_available_row(sheet):
    str_list = list(filter(None, sheet.col_values(1)))  # fastest
    return str(len(str_list)+1)

# use creds to create a client to interact with the Google Drive API
def update_spreadsheet(data_list):

    scope = ['https://spreadsheets.google.com/feeds']
    basedir = os.path.abspath(os.path.dirname(__file__))
    DATA_JSON = basedir+'/'+'client_secret.json'
    creds = ServiceAccountCredentials.from_json_keyfile_name(DATA_JSON, scope)

    client = gspread.authorize(creds)

    # Find a workbook by name and open the first sheet
    # Make sure you use the right name here.
    sheet = client.open("mqttsheet").sheet1

    next_row = next_available_row(sheet)
#
#
#
#
    count=int(next_row)
    count-=1


    #insert on the next available row
    date_today=datetime.date.today()
    time_today=time.strftime("%H:%M:%S")

    sheet.update_acell("A{}".format(next_row), count)
    sheet.update_acell("B{}".format(next_row), date_today)
    sheet.update_acell("C{}".format(next_row), time_today)
    sheet.update_acell("D{}".format(next_row), data_list[0])
    sheet.update_acell("E{}".format(next_row), data_list[1])
    sheet.update_acell("F{}".format(next_row), data_list[2])
    sheet.update_acell("G{}".format(next_row), data_list[3])
    sheet.update_acell("H{}".format(next_row), data_list[4])
    sheet.update_acell("I{}".format(next_row), data_list[5])
    sheet.update_acell("J{}".format(next_row), data_list[6])
    sheet.update_acell("K{}".format(next_row), data_list[7])
    sheet.update_acell("L{}".format(next_row), data_list[8])