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()