def readingfromgoogle(serviceaccountfile, googleid, sheetname): spreadsheet = SpreadsheetApp(serviceaccountfile).open_by_id(googleid) sheet = spreadsheet.get_sheet_by_name(sheetname) #access the wanted tab data_range = sheet.get_data_range() #extract range where the data is values = data_range.get_values() #get values values = pd.DataFrame(data = values[8:], columns = values[7]) #put the data into pandas dataframe excel = False return values, excel
def conseguir_valores(self): try: spreadsheet = SpreadsheetApp(JSON_FILE).open_by_id(self.id) sheet = spreadsheet.get_sheet_by_name(self.name) data_range = sheet.get_data_range() self.registros = data_range.get_values() except: print("No conecta con la sheet")
def cargar_datos(self): try: spreadsheet = SpreadsheetApp(JSON_FILE).open_by_id(self.id) sheet = spreadsheet.get_sheet_by_name(self.nombre) data_range = sheet.get_data_range() self.registros = data_range.get_values() except: print("No conecta con la sheet") for num in range(1, len(self.registros)): deno = self.registros[num][0] id_d = self.registros[num][1] nom = self.registros[num][2] ini = self.registros[num][3] fin = self.registros[num][4] dat = dato(deno, id_d, nom, ini, fin) self.datos.append(dat)
# Add try block to handle case where SERVICE_ACCESS_FILE or SPREADSHEET_ID do not exist, or are incorrect? # Add a fileExists check for SERVICE_ACCESS_FILE ? # Add HttpError if spreadsheet does not exist ? print("Connecting to Spreadsheet..", end='.') spreadsheet = SpreadsheetApp(env_vars.get('service_access_file')).open_by_id(env_vars.get('sheet_id')) print("CONNECTED") # If necessary to iterate through spreadsheet #sheets = spreadsheet.get_sheets() #for entries in sheets:print(entries) # Deliberately hardcoding the Invoice sheet name # Invoice Sheet invoice_sheet = spreadsheet.get_sheet_by_name('Invoice') invoice_sheet_range = invoice_sheet.get_data_range() invoice_values = invoice_sheet_range.get_values() # returns a 2d matrix of lists # Faster than a slice (invoice_values[2:-1]). It's necessary to remove the 3 header columns. invoice_values.pop(0); invoice_values.pop(0); invoice_values.pop(0) # Change INVOICE DATEs format (DD/MM?YYY -> YYYY/MM/DD) to import into MySQL date column for row in invoice_values: row[1] = datetime.datetime.strptime(row[1], '%d/%m/%Y').strftime('%Y/%m/%d') # Deliberately hardcoding the Customer sheet name # Customer_Info Sheet customer_sheet = spreadsheet.get_sheet_by_name('Customer_Info') customer_sheet_range = customer_sheet.get_data_range() customer_values = customer_sheet_range.get_values()