def read_invoices(): """ Reads Google Spreadsheet with next actions Returns a list of dictionaries, where a dictionary is a record read from the spreadsheet Created for test purposes, not intended to be used in production """ try: gc = gspread.login(gs_config["email"], gs_config["password"]) sh = gc.open_by_key("1G5ispFcCHw3ejVitV91xmdLFXhKOjn2E8JQ8fSmMCg0") ws = sh.get_worksheet(0) rows = ws.get_all_values() cols = [col.replace(" ", "").strip().lower() for col in rows.pop(0)] data = [{k: v for k, v in zip(cols, row)} for row in rows] action_rows = [row for row in data if len(row["customeremail"].strip()) and len(row["invoicecounter"].strip())] print len(action_rows) # if col in ultradox_cols except httplib.CannotSendRequest: print "HTTP Error while reading invoice data from Google spreadsheet" raise except httplib.BadStatusLine: print "HTTP Error while reading invoice data from Google spreadsheet" raise return [action_rows[1]]
def initDataFromGDocs(): print 'Fetching data from Google Docs...' global FIRST_NAME global LAST_NAME global DATE global STREET global CITY global STATE global ZIP global PHONE global EMAIL # Login with your Google account # I know I should NEVER do this like this. But this is a hackathon... gc = gspread.login(DOCS_EMAIL, DOCS_PASSWORD) # Open a worksheet from spreadsheet with one shot wks = gc.open("GovSafeData").sheet1 # Populate FIRST_NAME = wks.acell('A2').value LAST_NAME = wks.acell('B2').value DATE = wks.acell('C2').value STREET = wks.acell('D2').value CITY = wks.acell('E2').value STATE = wks.acell('F2').value ZIP = wks.acell('G2').value PHONE = wks.acell('H2').value EMAIL = wks.acell('I2').value
def cxn(): global gc global sh global out gc = gspread.login(os.environ['gmail_add'],os.environ['gmail_pw']) sh = gc.open('nepal test') out = sh.worksheet('Aggregated')
def test_contact_key_creator(self): self.logger.info('Starting test_contact_key_creator') #setup the doc on gdrive first file_name = 'ContactKeyTest1' ss_key = gdoc_util.upload_file_to_gdrive('contact_key_test1.tsv', file_name) driver = self.driver gdoc_util.login_gdrive(driver) driver.get('%s%s' % (self.base_url, '?ss=' + ss_key)) gc = gspread.login(settings.DEFAULT_GDRIVE_EMAIL, settings.DEFAULT_GDRIVE_PW) my_worksheet = gc.open_by_key(ss_key).sheet1 e2_val = my_worksheet.acell('E2') self.logger.info('e2_val: %s' % e2_val) #reset the cell my_worksheet.update_acell('E2', '') e2_val = my_worksheet.acell('E2') self.logger.info('e2_val reset to: %s' %e2_val) #now run the command #switch to input form frame driver.switch_to.frame(0) WebDriverWait(driver, 10).until( EC.presence_of_element_located((By.LINK_TEXT, "Hiplead")) ).click() id_worksheet_name_INPUT = WebDriverWait(driver, 10).until( EC.presence_of_element_located((By.ID, "id_worksheet_name")) ) id_worksheet_name_INPUT.clear() id_worksheet_name_INPUT.send_keys(file_name) Select(driver.find_element_by_id("id_scrapers")).select_by_value('contactKeyCreator') driver.find_element_by_id("id_email").send_keys(settings.DEFAULT_GDRIVE_EMAIL) driver.find_element_by_id("id_password").send_keys(settings.DEFAULT_GDRIVE_PW) #ok, now submit the form id_worksheet_name_INPUT.submit() #then wait for task to complete #this success alert only becomes visible when task is actually finished. success_div = driver.find_element_by_class_name('time_remaining') try: WebDriverWait(driver, 10).until( EC.visibility_of(success_div) ) except StaleElementReferenceException as e: #TODO The javascript DOM manipulation that results in StaleElementReferenceException needs to be resolved. success_div = driver.find_element_by_class_name('time_remaining') WebDriverWait(driver, 10).until( EC.visibility_of(success_div) ) #now validate cell value, since we know task has completed. e2_val = my_worksheet.acell('E2') self.logger.info('e2_val after test: %s' %e2_val) self.assertEquals('john_franklin_smith_somedomain.net', e2_val.value) self.logger.info( 'Finished test_contact_key_creator')
def authenticate(self, email, password): # Warning: ClientLogin is deprecated since April 20, 2015. # Should migrate to OAuth2 authentication. # These should be placed in the environment variables. auth = gspread.login(email, password) return auth
def update_spreadsheet(key, worksheet, data, chunk_size=200): gsclient = gspread.login(app_engine_username, app_engine_password) wr = gsclient.open_by_key(key).worksheet(worksheet) print "updating %s:" % (wr.title) row_generator = iter(data) def get_chunk(): for i, line in enumerate(row_generator): yield line if i >= chunk_size: break total_rows = 0 current_row = 1 while True: rows = list(get_chunk()) if not rows: break ncols = len(rows[0]) total_rows += len(rows) wr.resize(total_rows, ncols) addr = "%s:%s" % (wr.get_addr_int( current_row, 1), wr.get_addr_int(total_rows, ncols)) cells = wr.range(addr) for cell in cells: cell.value = rows[cell.row - current_row][cell.col - 1] print "updating %s cells (%s)..." % (len(cells), addr) wr.update_cells(cells) current_row += len(rows)
def getWorksheet(): if (os.system("ping -c 4 192.168.1.1") == 0): try: logging.warning ("getWorksheet: Try login") gc = gspread.login(email, password) except: logging.error ("getWorksheet: Unable to login") return 0 else: try: logging.warning("getWorksheet: Try open spreadsheet") spreadSheet = gc.open(spreadsheetName) except: logging.error("getWorksheet: Unable to open spread sheet: %s" % spreadsheetName) return 0 else: logging.warning("getWorksheet: Open spredsheet succesfully") try: workSheet = spreadSheet.get_worksheet(8) except: logging.error("getWorksheet: Unable to get worksheet") return 0 else: return workSheet else: logging.warning("getWorksheet: No network connection") return 0
def write_to_gdocs(temp, ext_temp, humidity): """write our data to a google spreadsheet""" # Account details for google docs email = '*****@*****.**' password = '******' spreadsheet = 'pitemp' # Login with your Google account try: gc = gspread.login(email, password) except: print "Unable to log in. Check your email address/password" sys.exit() # Open a worksheet from your spreadsheet using the filename try: worksheet = gc.open(spreadsheet).sheet1 except: print "Unable to open the spreadsheet. \ Check your filename: %s" % spreadsheet sys.exit() try: values = [datetime.datetime.now(), temp, ext_temp, humidity] worksheet.append_row(values) #success! print "Wrote a row to %s at %s" % (spreadsheet, datetime.datetime.now()) except: print "Unable to append data. Check your connection?"
def create_google(): ''' create a google spreadsheet if doesn't exists copied from http://diorsman.me/2013/11/05/create-new-google-spreadsheet-in-python/ ''' import gspread import gdata.docs.client import getpass # if using in batch these 3 needs to be given not interactively email = raw_input("Google drive account: ") password = getpass.getpass("Google drive password: "******"Spreadsheet name: ") try: gc = gspread.login(email, password) except BadAuthentication: print "Username or password are wrong" sys.exit() except NotVerified: print "Account not verified, user need to acces Google account directly first" sys.exit() except ServiceUnavailable: print "Service unavailable, try again later" sys.exit() #Open Spreadsheet, if doesn't exist then create a new one try: spr = gc.open(gname) print "Spreadsheet exists, it will be overwritten: ", gname except: source = 'CMIP5 Spreadsheet' gd_client = gdata.docs.client.DocsClient() gd_client.ClientLogin(email, password, source) document = gdata.docs.data.Resource(type='spreadsheet', title=gname) document = gd_client.CreateResource(document) print 'Created Spreadsheet: '+ gname spr = gc.open(gname) return spr,gc,gname
def abrir_planilha_cursos(): prints("abrir_planilha_cursos") teste_de_conexao(True) global cliente, planilha_cursos, banco_de_dados, menu, msg msg.put(["Abrindo", "Planilhas cursos"]) try: cliente = gspread.login(Sis_M, Sis_S) except: msg.put(["Erro", "Erro ao fazer login"]) menu = INICIO tecla = ler_teclas() else: try: planilha_cursos = cliente.open_by_key(KEY_CURSOS).get_worksheet(0) except: print("Erro ao abrir planilha dos cursos. Key = ", KEY_CURSOS) msg.put(["Erro", "Erro ao abrir planilha dos cursos"]) menu = INICIO tecla = ler_teclas() try: banco_de_dados = cliente.open_by_key(KEY_BD).get_worksheet(0) except: print("Erro ao abrir banco de dados. Key = ", KEY_BD) msg.put(["Erro", "Erro ao abrir banco de dados"]) menu = INICIO tecla = ler_teclas() msg.put(["Verificando", "Cursos"]) verifica_cursos()
def _getGspreadClient(self): try: return gspread.login(self.email, self.password) except gspread.exceptions.AuthenticationError as err: #needed as gspread lib doesnt properly handle errors yet raise WrapperError("Incorrect authentication")
def download(self, key, datarange, init_date = None, map_meaningful = None): # print('key', key) gc = gspread.login(self.user, self.password) if isinstance(init_date, date): pass elif isinstance(init_date, dict): init_date = datetime(tzinfo = TZI, **init_date).date() elif isinstance(init_date, str): init_date = date.strptime(init_date, "%d %b %y") # 26 nov 12 else: init_date = self._get_init_date(gc, key) def datasize(drange): start, end = drange.split(':') letters = lambda x, boo: ''.join([ z for z in x if z.isdigit()!=boo ]) start_letters = letters(start,1) start_digits = letters(start,0) end_letters = letters(end,1) end_digits = letters(end,0) col_names = list(map(str.strip,map(''.join,itertools.combinations_with_replacement(' ABCDEFGHIJKLMNOPQRSTUVWXYZ', 2))))[1:] x_size = col_names.index(end_letters) - col_names.index(start_letters) y_size = int(end_digits) - int(start_digits) return x_size + 1, y_size + 1 # +1 because bothe start and end index are part of the range # wks = gc.open("schedule").get_worksheet(1) wks = gc.open_by_key(key).get_worksheet(1) sheet_data = wks.range(datarange) # print('sheet data', sheet_data) results = self._parse_shifts(init_date, sheet_data, *datasize(datarange), map_meaningful=map_meaningful) return results
def updateSheet(): UN = str(raw_input("Enter Google UserName: "******"Enter Google Password: "******"Conn_spreadsheet_catmaid").sheet1 usedSkels = worksheet.col_values(1) c = catmaid.Connection('http://catmaid.hms.harvard.edu', 'thomas.lo', 'asdfjkl;', 'Validation Tracing DR5_7L') Conn_Load = loadmat("cons_to_seed.mat") loaded = np.asarray(Conn_Load['cons_to_seed']) wd = c.wiring_diagram() for edge in wd['data']['edges']: if ((edge['target'] not in usedSkels) or (edge['source'] not in usedSkels)): rows = rowMake(c, 1, [edge['source'], edge['target']], loaded) if edge['source'] not in usedSkels: n = len(worksheet.col_values(1)) rows[0][ 22] = '=IF(Z{0}="UHOH","uhoh",IF(C{0}="","Ready",IF(I{0}="","In progress",IF(K{0}="","needs Review",IF(U{0}="","In progress","Reviewed")))))'.format( n + 1) worksheet.append_row(rows[0]) if edge['target'] not in usedSkels: n = len(worksheet.col_values(1)) rows[1][ 22] = '=IF(Z{0}="UHOH","uhoh",IF(C{0}="","Ready",IF(I{0}="","In progress",IF(K{0}="","needs Review",IF(U{0}="","In progress","Reviewed")))))'.format( n + 1) worksheet.append_row(rows[1])
def optout_users(): """ Retrieve opt-out users from Google Sheets, load them to an opt-out table, and log the batch when done """ # Login with your Google account gc = gspread.login('*****@*****.**','password') spreadsheet = gc.open(datasheets["OptOuts"]) worksheet = spreadsheet.worksheet("OptOuts") # Retrieve all signups as a list of lists optouts = worksheet.get_all_values() # Trim off all the Google Spreadsheet reporting header records optouts = optouts[15:] optout_count = len(optouts) optouts_clean = [] for optout in optouts: optouts_clean.append(optout[0:5]) if (optout_count > 0): # Set up database connection db_connection = pyodbc.connect(**thinmint_db) db_cursor = db_connection.cursor() # Load all new signups to database db_cursor.executemany("execute thinmint.dbo.OptOutUser ?,?,?,?,?", optouts_clean) db_cursor.commit() db_connection.close() log_me(datasheets["OptOuts"], 'dbo.OptOuts', 'Success', str(optout_count))
def read_spreadsheet(google_url): gc = gspread.login(sys.argv[1], sys.argv[2]) google_spread = gc.open("Books to Read") # print 'after google_spread' google_worksheet = google_spread.get_worksheet("To Read") col_titles = google_worksheet.col_values(1) # Does gspread do 1 or 0? print col_titles
def getCSV(self): """ Returns ------- filename: str """ import getpass import gspread user = raw_input("Insert Google username:"******"Insert password:"******"SpreadSheet filename on Drive:") sheet = raw_input("Sheet name (first sheet is default):") cl = gspread.login(user, password) sh = cl.open(name) if not (sheet.strip()): ws = sh.sheet1 sheet = "1" else: ws = sh.worksheet(sheet) filename = name + '-worksheet_' + sheet + '.csv' with open(filename, 'wb') as f: writer = UnicodeWriter(f) writer.writerows(ws.get_all_values()) return filename
def start(): print 'Convert CSV to JSON' user = raw_input("Gmail user: "******"Gmail Password: "******"Spreadsheet url: ") sh = gc.open_by_url(docurl) #apertura del documento / Open Doc. worksheet = sh.worksheet( "Single Neurons") #Seleccion de la hoja / Sheet selection. print 'Taking values...' #Toma los valores de las celdas / Take value of celds. colb = worksheet.col_values(2) cold = worksheet.col_values(4) colf = worksheet.col_values(6) #Segundo paso, datos convertidos / Second Step, converted data. print 'Converting values to JSON...' celd = 2 celegans = open('celegans.json', 'w') head = "{\n" "\"name\":\"NeuroNetwork\",\n\"children\": [\n" celegans.write(head) for i in range(301): chld = "{\n\"name\":\"" + colb[ celd] + "\",\n\"children\": [\n{\"name\": \"" + str( cold[celd]) + "\", \"size\": 3000},\n{\"name\": \"" + str( colf[celd]) + "\", \"size\": 3000}\n]\n},\n" celd += 1 celegans.write(chld) celegans.write("]\n}") celegans.close() print "\nWORKS!, celegans.json was generated!\n"
def exportToGoogleSpreadSheet(self, username, password, sheetname): # Login with your Google account gc = gspread.login(username, password) # Open a worksheet from spreadsheet with one shot sh = gc.open(sheetname) wks = sh.worksheet(self.comboBox_Grouping.currentText()) for i, vhead in enumerate(self.tablemodel.v_header): wks.update_cell(i+2, 1, vhead) for j, hhead in enumerate(self.tablemodel.h_header): wks.update_cell(1, j+2, hhead) d = self.tablemodel.index(i, j).data(Qt.DisplayRole).toPyObject() if d: cell_data = r'=IMAGE("%s", 4, %s, %s)' % (quote(self._card_database.findByName(d)[0].imageURL), CARD_HEIGHT, CARD_WIDTH) else: cell_data = r'' wks.update_cell(i+2, j+2, cell_data) # Fetch a cell range #cell_list = wks.range('A1:B7') #print cell_list print("DONE!")
def _fetch_responses(self, update): self._clear() self._print('\t'+self._uname+'\n') if update: if self._password: passwd = self._password else: passwd = getpass.getpass() try: self._print('Retrieving spreadsheet from Google Drive...') gc = gspread.login(self._uname, passwd) responses = gc.open_by_key(self._docKey) sheet = responses.get_worksheet(0) copy = sheet.get_all_values() with open('reflections.csv', 'w') as f: csv.writer(f).writerows(copy) except: self._print('Failed! Loading saved csv...') with open('reflections.csv', 'r') as f: copy = list(csv.reader(f)) else: self._print('Working off saved csv...') with open('reflections.csv', 'r') as f: copy = list(csv.reader(f)) return copy
def parse_data(raw): if len(raw) != 24: raise DadosErradosDoPortao # vê se pegou a string certa do lugar certo parts = raw.strip().split(',') if len(parts) != 4: raise DadosErradosDoPortao # # pega partes da string pw_pos = parts[0][1:] code = parts[1] date = datetime.datetime.strptime(parts[2], '%d/%m/%y') time = datetime.datetime.strptime(parts[3], '%H:%M') hora_lida = date.replace(minute=time.minute, hour=time.hour) # pegando dados da planilha quarto = nome = '' try: gc = gspread.login(settings.googlelogin, settings.googlepassword) wks = gc.open('senhas').sheet1 cellpos = wks.find(pw_pos) nome = wks.cell(cellpos.row, 4).value quarto = wks.cell(cellpos.row, 3).value except: print 'erro ao buscar dados da planilha: %s\nraw: %s' % (query, raw) # salva return { 'nome': nome, 'quarto': quarto, 'hora_lida': hora_lida.isoformat(), 'created_at': datetime.datetime.now().isoformat(), 'raw': raw, 'event': event[code] }
def get_financial_data_and_upload(spreadsheet_name): print pyCol.COL("Type your google ID (e.g. [email protected]) then press ", "header") + pyCol.COL('<Enter>: ', 'green') email = raw_input() print pwd = getpass.getpass( pyCol.COL('Type password then press ', 'header') + pyCol.COL('<Enter>: ', 'green')) # command line prompt without echo print gc = gspread.login(str(email), pwd) print pyCol.COL('Type Company Symbol (e.g: ORCL:NYQ) then press ', 'header') + pyCol.COL('<Enter>: ', 'green') company_name = raw_input() # company_name = 'ORCL:NYQ' print pyCol.COL('Data for company ' + company_name + ' will be downloaded', 'blue') print financial_category_list = ["BalanceSheet", "CashFlow", "IncomeStatement"] for financial_category in financial_category_list: print pyCol.COL('Updating the ' + financial_category + ' category', 'blue') d_result_ordered = get_financial_data(financial_category, company_name) upload_financial_data( financial_category, spreadsheet_name, gc, d_result_ordered)
def main(): google_account = gspread.login(os.environ['gmail'], os.environ['password']) alumni_spreadsheet_url = os.environ['alumni_url'] student_spreadsheet_url = os.environ['student_url'] alumni = get_alumni(google_account, alumni_spreadsheet_url) students = get_students(google_account, student_spreadsheet_url) matches = match_up(students, alumni)
def getWorksheet(key, wsNumOrTitle): gc = gspread.login(USERNAME, PASSWORD) sh = gc.open_by_key(key) if type(wsNumOrTitle) == int: return sh.get_worksheet(wsNumOrTitle) else: return sh.worksheet(wsNumOrTitle)
def gSpread(self): """ These are used for login information with Google Spread sheet, create a "password.txt" file and type your password in there for authentication to Google. Be sure to remember to add "password.txt" to your ".gitignore" file if you are using Git/GitHub so that you don't post your password to the entire world. """ email = '*****@*****.**' password = linestring = open('password.txt', 'r').read() spreadsheet = 'Computer Science Lab Temperature' # Login with your Google account try: gc = gspread.login(email, password) print "try gSpread" except: print "Unable to log in. Check your email address/password" sys.exit() # Open a worksheet from your spreadsheet using the filename try: worksheet = gc.open(spreadsheet).sheet1 # Alternatively, open a spreadsheet using the spreadsheet's key # worksheet = gc.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE') except: print "Unable to open the spreadsheet. Check your filename: %s" % spreadsheet sys.exit()
def resign_users(): """ Retrieve resigned users from Google Sheets, load them to an resigns table, and log the batch when done """ # Login with your Google account gc = gspread.login('*****@*****.**','password') spreadsheet = gc.open(datasheets["Resigns"]) worksheet = spreadsheet.sheet1 # Retrieve all signups as a list of lists resigns = worksheet.get_all_values() # Remove the Header record if (resigns[0][0] == "Submitted On"): resigns.pop(0) resigns_count = len(resigns) if (resigns_count > 0): for record in resigns: resigns[2] = resigns[2][0:min(len(resigns[2]),250)] # Set up database connection db_connection = pyodbc.connect(**thinmint_db) db_cursor = db_connection.cursor() # Load all new signups to database db_cursor.executemany("execute thinmint.dbo.ResignUser ?,?,?", resigns) db_cursor.commit() db_connection.close() log_me(datasheets["Resigns"], 'dbo.Resigns', 'Success', str(resigns_count))
def index(self): gc = gspread.login('*****@*****.**', self.password) spreadsheet = gc.open_by_key( '14Yb7BQ5PtczaX9z7b2YIig_xFZgI7Ox8kPA_PNRhXB4') ldap = 'thomasl' worksheet = [ x for x in spreadsheet.worksheet('Individuals').get_all_values() if x[0] == ldap ] if len(worksheet) == 0: return None else: group_name = worksheet[0][1] token_count = worksheet[0][2] spreadsheet_data = [ ListMethods.strip(y) for y in ListMethods.split( spreadsheet.worksheet(group_name).get_all_values()) ] return { 'bucketData': { 'user': ldap, 'tokenCount': token_count, 'buckets': [[[TokensMethods.parse(z) for z in y] for y in x] for x in spreadsheet_data] } }
def update_monthly_games_played(): gc = gspread.login('*****@*****.**', 'muatkienjwxfpnxn') player_cell_locations = {'thewarmth00':'B51', 'rob_chainsaw':'B53', 'mashley93':'B55', 'peachy36west':'B57', 'm_sibs':'B59', 'rc_van':'B61', 'soviet_canuck':'B63', 'undertheblanket':'B65', 'vsmewen':'B67', 'hokagesama1':'B69', 'lnferno31':'H86'} sheet = gc.open_by_key('0Ak-m4uT6aXL1dExuUHdLV0x2aTNFSGNRMTV2WWdLX2c').get_worksheet(9) session = Session() our_players = session.query(Player).all() for player in our_players: cell_location = player_cell_locations[player.username.lower()] games_played = len(session.query(GamePlayed).filter_by(player_id=player.id).all()) value = sheet.acell(cell_location).value sheet.update_acell(cell_location, str(games_played)) if sheet.acell(cell_location).value != value: pass else: pass
def setUp(self): creds_filename = "tests.config" try: config_filename = os.path.join( os.path.dirname(__file__), creds_filename) config = ConfigParser.ConfigParser() config.readfp(open(config_filename)) self.config = config auth_type = config.get('Google Account', 'auth_type') if auth_type == 'OAuth': # key_ring = {} key_ring['grant_type'] = 'refresh_token' key_ring['refresh_token'] = config.get('Google Account', 'refresh_token') key_ring['client_secret'] = config.get('Google Account', 'client_secret') key_ring['client_id'] = config.get('Google Account', 'client_id') # access_token = config.get('Google Account', 'access_token') # self.gc = gspread.authorize(access_token, key_ring) else: # email = config.get('Google Account', 'email') password = config.get('Google Account', 'password') self.gc = gspread.login(email, password) self.assertTrue(isinstance(self.gc, gspread.Client)) except IOError: msg = "Can't find %s for reading google account credentials. " \ "You can create it from %s.example in tests/ directory." raise Exception(msg % (creds_filename, creds_filename))
def listLearningzzz(): if not os.path.isfile("config.ini"): return "No config file set. FATAL" config = ConfigParser.RawConfigParser() config.read("config.ini") username = config.get("user_details","username") password = config.get("user_details","password") spreadsheetKey = config.get("spreadsheet_details","key") worksheetNum = config.getint("spreadsheet_details","worksheet") gc = gspread.login(username,password) sheet = gc.open_by_key(spreadsheetKey).get_worksheet(worksheetNum) topicsList = sheet.col_values(2)[1:] linksList = sheet.col_values(3)[1:] topicsLinksList = [] for topic, link in zip(topicsList,linksList): if link is None: link = "#" obj = {} obj["topic"] = topic obj["link"] = link topicsLinksList.append(obj) return render_template('learningzzz.html',topicsLinksList=topicsLinksList)
def read_worksheet(): global app gc = gspread.login(app.config['GOOGLE_EMAIL'], app.config['GOOGLE_PASSWORD']) sht1 = gc.open_by_key(app.config['GOOGLE_DRIVE_SHEET_ID']) worksheet = sht1.get_worksheet(0) return worksheet
def write_items(): nowstr = datetime.now().strftime('%Y/%m/%d %H:%M:%S') print 'writing to google spreadsheet' gapp = gspread.login('*****@*****.**', GPASS) doc = gapp.open('Ruten Diesel Tracker') sht = doc.add_worksheet(nowstr, 1, len(TITLES)) title_cells = sht.range('A1:%s1' % chr(ord('A') + len(TITLES) - 1)) for i in range(len(TITLES)): title_cells[i].value = TITLES[i] sht.update_cells(title_cells) for i in ITEMS: print 'writing: %s' % i row = [ i['style'], i['wash'], i['wsize'], i['lsize'], i['available'], i['backwidth'], i['frontwidth'], i['bottomheight'], i['bottomwidth'], i['biglegwidth'], i['footwidth'], i['outerlength'], i['innerlegth'], i['url'], ] sht.append_row(row)
def updateSheet(): UN = str(raw_input("Enter Google UserName: "******"Enter Google Password: "******"Conn_spreadsheet_catmaid").sheet1 usedSkels = worksheet.col_values(1) c = catmaid.Connection('http://catmaid.hms.harvard.edu', 'thomas.lo', 'asdfjkl;', 'Validation Tracing DR5_7L') Conn_Load = loadmat("cons_to_seed.mat") loaded = np.asarray(Conn_Load['cons_to_seed']) wd = c.wiring_diagram() for edge in wd['data']['edges']: if ((edge['target'] not in usedSkels) or (edge['source'] not in usedSkels)): rows = rowMake(c, 1, [edge['source'], edge['target']], loaded) if edge['source'] not in usedSkels: n = len(worksheet.col_values(1)) rows[0][22]='=IF(Z{0}="UHOH","uhoh",IF(C{0}="","Ready",IF(I{0}="","In progress",IF(K{0}="","needs Review",IF(U{0}="","In progress","Reviewed")))))'.format(n+1) worksheet.append_row(rows[0]) if edge['target'] not in usedSkels: n = len(worksheet.col_values(1)) rows[1][22]='=IF(Z{0}="UHOH","uhoh",IF(C{0}="","Ready",IF(I{0}="","In progress",IF(K{0}="","needs Review",IF(U{0}="","In progress","Reviewed")))))'.format(n+1) worksheet.append_row(rows[1])
def cxn(): global gc global sh global out gc = gspread.login(os.environ['gmail_add'],os.environ['gmail_pw']) sh = gc.open('150505 Priority VDC list with Population') out = sh.worksheet('Aggregated')
def move_new_signups(): """ Retrieve new Signups from Google Sheets, load them to a registration database, and log the batch when done """ # Login with your Google account gc = gspread.login('*****@*****.**','password') spreadsheet = gc.open(datasheets["Signups"]) worksheet = spreadsheet.sheet1 # Retrieve all signups as a list of lists new_signups = worksheet.get_all_values() # Remove the Header record if (new_signups[0][0] == "Submitted On"): new_signups.pop(0) signup_count = len(new_signups) for record in new_signups: record.append("website") if (signup_count > 0): # Set up database connection db_connection = pyodbc.connect(**thinmint_db) db_cursor = db_connection.cursor() # Load all new signups to database db_cursor.executemany("execute thinmint.dbo.AddNewUser ?,?,?,?", new_signups) db_cursor.commit() db_connection.close() log_me(datasheets["Signups"], 'dbo.Users', 'Success', str(signup_count))
def read_invoices(): """ Reads Google Spreadsheet with next actions Returns a list of dictionaries, where a dictionary is a record read from the spreadsheet Created for test purposes, not intended to be used in production """ try: gc = gspread.login(gs_config['email'], gs_config['password']) sh = gc.open_by_key('1G5ispFcCHw3ejVitV91xmdLFXhKOjn2E8JQ8fSmMCg0') ws = sh.get_worksheet(0) rows = ws.get_all_values() cols = [col.replace(' ', '').strip().lower() for col in rows.pop(0)] data = [{k: v for k, v in zip(cols, row)} for row in rows] action_rows = [ row for row in data if len(row['customeremail'].strip()) and len(row['invoicecounter'].strip()) ] print len(action_rows) #if col in ultradox_cols except httplib.CannotSendRequest: print "HTTP Error while reading invoice data from Google spreadsheet" raise except httplib.BadStatusLine: print "HTTP Error while reading invoice data from Google spreadsheet" raise return [action_rows[1]]
def update_spreadsheet(key, worksheet, data, chunk_size=200): gsclient = gspread.login(app_engine_username, app_engine_password) wr = gsclient.open_by_key(key).worksheet(worksheet) print "updating %s:" % (wr.title) row_generator = iter(data) def get_chunk(): for i, line in enumerate(row_generator): yield line if i>=chunk_size: break total_rows = 0 current_row = 1 while True: rows = list(get_chunk()) if not rows: break ncols = len(rows[0]) total_rows += len(rows) wr.resize(total_rows, ncols) addr = "%s:%s" % (wr.get_addr_int(current_row, 1), wr.get_addr_int(total_rows, ncols)) cells = wr.range(addr) for cell in cells: cell.value = rows[cell.row-current_row][cell.col-1] print "updating %s cells (%s)..." % (len(cells), addr) wr.update_cells(cells) current_row += len(rows)
def __init__(self, user_id, passwd, spreadsheet_name): """ Args: user_id: Gmail user id passwd: Gmail password spreadsheet_name: Name of the spreadsheet containing all the subs info """ try: gc = gspread.login(user_id, passwd) sp = gc.open(spreadsheet_name) ws = sp.sheet1 row_num = 2 while True: curr_row = ws.row_values(row_num) if not curr_row: break self.append(TeacherData(curr_row)) row_num += 1 except gspread.exceptions.AuthenticationError: print 'Incorrect username or password' sys.exit(1) except gspread.exceptions.SpreadsheetNotFound: print '\'%s\' spreadsheet not found' % spreadsheet_name sys.exit(1)
def cxn(): gc = gspread.login(os.environ['gmail_add'], os.environ['gmail_pw']) sh = gc.open('150505 Priority VDC list with Population') c = sh.worksheet('eo_sandbox') #get names of all worksheets titles = [n.title for n in t.worksheets()]
def purchases(): def incompletePurchaseRequest(): notification = Notify(notification_type = 'error', message = 'Incomplete form') return render_template('purchase.html', form=form, active_page='purchase', notify = notification) form = PurchaseForm(request.form) if request.method == 'POST' and form.validate(): gc = gspread.login('*****@*****.**','BajaBaja') work_sheet = gc.open('Purchases').sheet1 i = 1 col_list = work_sheet.col_values(1) for value in col_list: if value == None: break i += 1 work_sheet.update_acell('A%s'%(i), form.data['name']) work_sheet.update_acell('B%s'%(i), form.data['item']) work_sheet.update_acell('C%s'%(i), form.data['cost']) work_sheet.update_acell('D%s'%(i), form.data['vendor']) work_sheet.update_acell('E%s'%(i), form.data['link']) work_sheet.update_acell('F%s'%(i), form.data['date']) work_sheet.update_acell('G%s'%(i), form.data['quantity']) send_email(); return redirect(url_for('index')) return render_template('purchase.html', form=form, active_page='purchase')
def start(): print 'Convert CSV to JSON' user = raw_input("Gmail user: "******"Gmail Password: "******"Spreadsheet url: ") sh = gc.open_by_url(docurl)#apertura del documento / Open Doc. worksheet = sh.worksheet("Single Neurons")#Seleccion de la hoja / Sheet selection. print 'Taking values...' #Toma los valores de las celdas / Take value of celds. colb = worksheet.col_values(2) cold = worksheet.col_values(4) colf = worksheet.col_values(6) #Segundo paso, datos convertidos / Second Step, converted data. print 'Converting values to JSON...' celd = 2 celegans = open('celegans.json','w') head = "{\n""\"name\":\"NeuroNetwork\",\n\"children\": [\n" celegans.write(head) for i in range(301): chld = "{\n\"name\":\""+colb[celd]+"\",\n\"children\": [\n{\"name\": \""+str(cold[celd])+"\", \"size\": 3000},\n{\"name\": \""+str(colf[celd])+"\", \"size\": 3000}\n]\n},\n" celd += 1 celegans.write(chld) celegans.write("]\n}") celegans.close() print "\nWORKS!, celegans.json was generated!\n"
def get(self): gc = gspread.login(email, password) sht1 = gc.open_by_key(spreadsheet_key) worksheet = sht1.get_worksheet(0) matriz = [] num_cols = worksheet.col_count num_fils = worksheet.row_count #Metemos un bucle que recorra toda la hoja y vamos almacenando los datos en una matriz (array de array) i = 1 j = 1 while i < num_fils: matriz.append([]) while j <= num_cols: #Recorremos el documento de forma inversa para mostrar en el blog primero los eventos mas actuales (insertados los ultimos en la hoja de calculo val = worksheet.cell(num_fils - i + 1, j).value matriz[i - 1].append(val) j = j + 1 i = i + 1 j = 1 # Ahora devolvemos la matriz al html, donde con js la cogeremos y la iremos formateando para mostrar los eventos en el blog template_values = { 'matriz': matriz, 'num_fils': num_fils, 'num_cols': num_cols } template = JINJA_ENVIRONMENT.get_template( 'templates/MAIN_PAGE_HTML_BOOT.html') self.response.write(template.render(template_values))
def upload_grades(): try: import gspread except ImportError: print "Install gpsread library (on github @ https://github.com/burnash/gspread)" print "Note - library is super easy to install!" return user = get_credentials() gc = gspread.login(user[0],user[1]) spreadsheet = gc.open(spreadsheet_name) worksheet = spreadsheet.get_worksheet(0) grades = [] for name, email in students: if os.path.exists(email[0]): lines = [line.strip() for line in open(email[0]+"/grade.txt",'r')] for line in lines: if line.find("Grade:") == 0: grade = line.lstrip("Grade:") words = grade.rsplit('/'); grades.append(words[0]) else: print "ERROR! Student: "+email[0]+" Not Found!\n" print "grades not uploaded..." return col = worksheet.find(column_name) i = 2 for g in grades: worksheet.update_cell(i,col.col, g) i += 1 print "Grades have been uploaded to spreadsheet: "+spreadsheet_name return
def get(self): gc = gspread.login(email, password) sht1 = gc.open_by_key(spreadsheet_key) worksheet = sht1.get_worksheet(0) val = worksheet.cell(4, 1).value val1 = worksheet.row_count self.response.write(val1)
def insert_into_google_docs(username, password, docname, data): """ Update the db. """ # connect gc = gspread.login(username + '@gmail.com', password) ws = gc.open(docname).sheet1 # flattened list keys = ['name', 'email', 'last-presented', 'gone-until', 'going-on'] db = [keys] Nkeys = len(keys) Nrows = len(data[keys[0]]) for i in range(Nrows): db.append([data[k][i] for k in keys]) db = np.array(db).ravel() # list of cells end_letter = string.ascii_uppercase[Nkeys - 1] end_number = str(Nrows + 1) cell_list = ws.range(''.join(['A1:', end_letter, end_number])) # fill in cells for i, c in enumerate(cell_list): c.value = db[i] # resize db ws.resize(rows=Nrows + 1, cols=Nkeys) # update cells ws.update_cells(cell_list)
def fetch_from_spreadsheet(eid): gc = gspread.login(SPREADSHEET_ACC, SPREADSHEET_PW) wks = gc.open_by_key(SPREADSHEET_ID).sheet1 row_no = wks.find(eid).row value = wks.row_values(row_no) json = dict(zip(LOG_KEY, value)) return json
def write_items(): nowstr = datetime.now().strftime('%Y/%m/%d %H:%M:%S') print 'writing to google spreadsheet' gapp = gspread.login('*****@*****.**', GPASS) doc = gapp.open('Ruten Diesel Tracker') sht = doc.add_worksheet(nowstr, 1, len(TITLES)) title_cells = sht.range('A1:%s1' % chr(ord('A') + len(TITLES) - 1 )) for i in range(len(TITLES)): title_cells[i].value = TITLES[i] sht.update_cells(title_cells) for i in ITEMS: print 'writing: %s' % i row = [ i['style'], i['wash'], i['wsize'], i['lsize'], i['available'], i['backwidth'], i['frontwidth'], i['bottomheight'], i['bottomwidth'], i['biglegwidth'], i['footwidth'], i['outerlength'], i['innerlegth'], i['url'], ] sht.append_row(row)
def update_spreadsheet(values, settings): gc = gspread.login(settings['google_user']['username'], settings['google_user']['password']) # sempre atualiza a primeira folha da planilha sheet = gc.open_by_key(settings['spreadsheet_key']).get_worksheet(0) # pega as celulas da primeira linha # cujos campos a serem gravados estão vazios line = get_empty_line(sheet, values.keys()) # nenhuma linha disponivel (nunca deve acontecer rs) if not line: raise Exception('Nenhuma linha vazia encontrada') # itera sobre uma copia da lista line pq ela sera alterada # durante o loop for cell in line[:]: # obtem a letra da coluna da celula a ser atualizada row = chr(cell.col + 64) if values.get(row): cell.value = values[row] else: line.remove(cell) # faz o request de update sheet.update_cells(line)
def __init__(self, email, password, title, sheet="sheet1"): """ Connect to the database and open it. """ import gspread self.gs = gspread.login(email, password) self.ws = self.gs.open(title).worksheet(sheet)
def login(self): try: self.gclient = gspread.login(self.user, self.password) except Exception as e: print("Error:") print(e) self.gclient = None return self.gclient
def google_login(): #attempt to log in to your google account try: gc = gspread.login(os.environ["G_USER"], os.environ["G_PASS"]) print('log in success!!') return gc except: print('log in fail') sys.exit()
def login_open_sheet(email, password, spreadsheet): """Connect to Google Docs spreadsheet and return the first worksheet.""" try: gc = gspread.login(email, password) worksheet = gc.open(spreadsheet).sheet1 return worksheet except: print 'Unable to login and get spreadsheet. Check email, password, spreadsheet name.' sys.exit(1)
def geturl(msgid, sheet): gc = gspread.login(creds.googleuser, creds.googlepass) spreadsheet = gc.open(sheet) worksheet = spreadsheet.sheet1 url = "" for x in range(1, len(worksheet.col_values(1))): if worksheet.col_values(1)[x] == msgid: url = worksheet.col_values(2)[x] return url
def gdocs_login(): spreadsheet_key = '1A9uLVV21BEZBhuSBhDMUUnoP_ICAzz2YAkVKCGWZHQs' worksheet_key = 'od6' gd_client = gdata.spreadsheet.service.SpreadsheetsService(spreadsheet_key, worksheet_key) gd_client.email = '*****@*****.**' gd_client.password = "******" gd_client.ProgrammaticLogin() gc = gspread.login('*****@*****.**', "alkezuowfwtexxer") return gd_client, gc
def getallmembers(sheet): gc = gspread.login(creds.googleuser, creds.googlepass) spreadsheet = gc.open(sheet) worksheet = spreadsheet.sheet1 numbers = worksheet.range('A2:A%s' % len(worksheet.col_values(1))) members = [] for number in numbers: members.append(number.value) return members