def initializeGoogleServiceAcc(self): with open(self.kwargs['secresFilePath'], 'r') as f: fileContents = f.read() self.secrets = json.load(fileContents) creds = GS.auth.ServiceAccountCredentials.from_service_account_info( self["secrets"], scopes=GS.auth.DEFAULT_SCOPES) self.googleSA = GS.Client(auth=creds)
def __init__( self, secret_key_path, sheet_title, create=False, share_with=None, ): """ Create a new Google Sheet connector. This connector is a wrapper around a spreadsheet. :param os.Pathlike secret_key_path: The path to the secret key file. :param str sheet_title: The title for the spreadsheet. It must be shared with the service account, unless a new one is to be created. :param bool create: Indicate if the spreadsheet should be created if it does not exist. If this value is True, ``share_with`` MUST be provided. :param str share_with: The email address of an account where newly created sheets will be shared by default. """ credentials = Credentials.from_service_account_file(secret_key_path) scoped_credentials = credentials.with_scopes(self.SCOPES) self.client = gspread.Client(scoped_credentials) self.client.session = AuthorizedSession(scoped_credentials) self.share_with = share_with self.sheet = self._open(sheet_title, create=create)
def get_spread(): """ Get's the contents of the Campaign Generator Spreadsheet Outputs a list of candidates not seen by the program before""" # If modifying these scopes, delete the file token.pickle. SCOPES = [ 'https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive' ] creds = None # The file token.pickle stores the user's access and refresh tokens, and is # created automatically when the authorization flow completes for the first # time. if os.path.exists('token.pickle'): with open('token.pickle', 'rb') as token: creds = pickle.load(token) # If there are no (valid) credentials available, let the user log in. if not creds or not creds.valid: if creds and creds.expired and creds.refresh_token: creds.refresh(Request()) else: flow = InstalledAppFlow.from_client_secrets_file( 'credentials.json', SCOPES) creds = flow.run_local_server(port=0) # Save the credentials for the next run with open('token.pickle', 'wb') as token: pickle.dump(creds, token) # Initialize gspread client with credentials then open spreadsheet gc = gspread.Client(creds) sheet = gc.open("Campaign Generator Spreadsheet") worksheet = sheet.sheet1 # Get spreadsheet values values = sheet.sheet1.get_all_values() values.remove(values[0]) # Remove headers # Find unprocessed rows unprocessed = [] for row in values: if row[10].lower() == "yes": continue unprocessed.append(row) index = values.index(row) worksheet.update(f"K{index+2}", "Yes") candidate_list = [] for row in unprocessed: candidate_list.append({ "timestamp": row[0], "email": row[1], "name": row[2], "position": row[3], "district": row[4], "party": row[5], "issues": [row[6], row[7], row[8]], "image": row[9] }) return candidate_list
def __init__(self, credentials): # oauth2clientはdeprecationなので使わないように # https://github.com/burnash/gspread/issues/472#issuecomment-317631725 self.client = gspread.Client(auth=credentials) self.client.session = AuthorizedSession(credentials) self._current_sheet = None self.name_prefix = 'sample-'
def google_sshet(): """ Função simples para retornar um objeto capaz de manipular as planilhas do Google Sheets. """ session = google_api_auth() ggle_cred = gspread.Client(None, session) return ggle_cred
def update_stats_sheet(exported_data): # use creds to create a client to interact with the Google Drive API headers = HTTPSession(headers={'Connection':'Keep-Alive'}) scope = ['https://spreadsheets.google.com/feeds'] creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope) client = gspread.Client(creds, headers) # Find a workbook by name and open the first sheet # Make sure you use the right name here. client.login() sheet = client.open_by_key('1OspJrWAgzBj6Pm6TPio8O6ba6c9VQMTt3EjlAjl805Q').sheet1 last_row = 0 for num in range(5, 100): temp_cell = sheet.acell('A' + str(num)) if(temp_cell.value == ''): last_row = num break new_row = stats_collector.gather_data(exported_data) '''We generate an entire row of data at once, but the connection times out when trying to add an entire row to the google sheet. In order to upload the row, we have to write each element of the array to it's corresponding cell in the sheet.''' for item in range(0, len(new_row)): client.login() sheet.update_cell(last_row, item+1, nwe_row[item])
def setUp(self): super(BetamaxGspreadTest, self).setUp() self.session.headers.update({'accept-encoding': 'identity'}) self.gc = gspread.Client(self.auth_credentials, session=self.session) self.gc.login() self.assertTrue(isinstance(self.gc, gspread.client.Client))
def get_spreadsheet(sheet_name): if not SHEETS.get(sheet_name): # oAuth authentication. Json file created using explanation at: http://gspread.readthedocs.org/en/latest/oauth2.html # Updated call since v2.0: See https://github.com/google/oauth2client/releases/tag/v2.0.0 # Sheet should be shared with: 859748496829-pm6qtlliimaqt35o8nqcti0h77doigla@developer.gserviceaccount.com scopes = [ 'https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive' ] # Latest version from: https://stackoverflow.com/questions/51618127/credentials-object-has-no-attribute-access-token-when-using-google-auth-wi credentials = Credentials.from_service_account_file( 'sources/oauth_key.json') scoped_credentials = credentials.with_scopes(scopes) gc = gspread.Client(auth=scoped_credentials) gc.session = AuthorizedSession(scoped_credentials) try: sheet = gc.open(sheet_name) except gspread.exceptions.SpreadsheetNotFound: log.log_error('googlesheet.py', 'get_spreasheeet()', 'Could not find ' + sheet_name) return None except gspread.exceptions.APIError: log.log_error('googlesheet.py', 'get_spreasheeet()', 'Could not open ' + sheet_name) return None SHEETS[sheet_name] = sheet return SHEETS[sheet_name]
def get_google_sheet(spreadsheet_url, section): """ Retrieve sheet data using OAuth credentials and Google Python API. """ SCOPES = [ 'https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive' ] SERVICE_ACCT_FILE = '../client_secret.json' credentials = service_account.Credentials.from_service_account_file( SERVICE_ACCT_FILE, scopes=SCOPES) client = gspread.Client(auth=credentials) client.session = AuthorizedSession(credentials) sheet = client.open_by_url(spreadsheet_url) # for spreadsheet by URL # sheet1 = client.open(spreadsheet_id).sheet1 # for spreadsheet by NAME # convert sheet to dataframe section_sheet = sheet.worksheet(section) values = section_sheet.get_all_values() section_sheet_df = pd.DataFrame(values[2:]) # rename columns col_names = section_sheet_df[0:1].values[0] section_sheet_df = section_sheet_df[1:] section_sheet_df.columns = col_names return section_sheet_df
def __init__(self, credential_file, workbook_key, sheet_name, identifier_key, identifier=None, static_values=None, config_sheet_name=None): """Creates an initial entry. Args: credential_file: path to json API key. workbook_key: google sheet key (in URL). sheet_name: name of sheet to be edited. identifier_key: Column name to store unique entry identifier. identifier: unique entry id. if provided, an entry is created immediately. static_values (dict): columns and values that don't change. These values will be written every time a new row is appended, no matter in which sheet we are logging. config_sheet_name: If provided, config parameters will be stored in this worksheet, otherwise they go into the default worksheet `sheet_name`. """ self.credential_file = credential_file self.workbook_key = workbook_key self.sheet_name = sheet_name self.identifier_key = identifier_key self.identifier = identifier self.config_sheet_name = config_sheet_name self.start_time = time.strftime('%Y/%m/%d %H:%M:%S') self.hostname = socket.getfqdn() self.static_values = static_values if static_values is not None else dict( ) self.credential_key = json.load(self.credential_file) try: credentials = service_account.Credentials.from_service_account_info( self.credential_key, scopes=['https://www.googleapis.com/auth/spreadsheets']) self.client = gspread.Client(auth=credentials) self.client.session = AuthorizedSession(credentials) except: # pylint: disable=bare-except print('Could not authenticate with Drive API.') traceback.print_exc() self.static_values['Hostname'] = self.hostname self.static_values['Start Time'] = self.start_time if self.identifier is not None: self.static_values[self.identifier_key] = self.identifier # If the entry identifier is already provided, create an entry. self.ready = True if self.identifier is not None: try: self.update(self.static_values, self.sheet_name) except: # pylint: disable=bare-except self.ready = False traceback.print_exc() return
def __init__(self, credentials_dict, scopes, spreadsheet_name, tab_name): self.creds = ServiceAccountCredentials.from_json_keyfile_dict( credentials_dict, scopes) gc = gspread.Client(self.creds) self.ss = gc.open(spreadsheet_name) self.worksheet = self.ss.worksheet(tab_name)
def _get_gspread_client(user, refresh=False): if refresh: user.refresh_auth_token() token = user.get('access_token') auth = GSCredentials(access_token=token) session = CachedHTTPSession() client = gspread.Client(auth=auth, http_session=session) client.login() return client
def gspread_client(gc=None): if gc: return gc creds = credentials("sheets") gc = gspread.Client(auth=creds) gc.session = AuthorizedSession(creds) return gc
def refresh_drive_api(): logger.info("Keeping alive Drive API") scope = ['https://spreadsheets.google.com/feeds'] credentials = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope) gc = gspread.authorize(credentials) wks = gc.open("HAL 2.0").worksheet("Autocaptura") headers = gspread.httpsession.HTTPSession(headers={'Connection': 'Keep-Alive'}) gc = gspread.Client(auth=credentials, http_session=headers) gc.login() threading.Timer(120, refresh_drive_api).start()
def get_spreadsheet(self): while True: try: gc = gspread.Client(auth=self.scopedCreds) gc.session = AuthorizedSession(self.scopedCreds) sheet = gc.open(self.sheetName).sheet1 return sheet except: print("Authentication error, trying again") pass
def success(name, work): #Extract from Google Sheet scope = ['https://spreadsheets.google.com/feeds'] #attach credential file from developer google (API) credentials = service_account.Credentials.from_service_account_file( 'file(API from gsheet).json', scopes=scope) gc = gspread.Client(auth=credentials) gc.session = AuthorizedSession(credentials) #input gsheet ID #keysheet = request.args.get('keysheet') sheet = gc.open_by_key(name) #select number of sheet #worksheetid = request.args.get(0) worksheet = sheet.get_worksheet(work) list_of_lists = worksheet.get_all_values() #Transform names = sheet.title names = names.replace('xlsx', '').replace('xls', '').replace('csv', '').replace( '.', '').replace('-', '_').replace(' ', '_').lower() suffixs = worksheet.title suffixs = suffixs.replace(' ', '_').replace('-', '_').lower() df = pd.DataFrame() df = df.append(list_of_lists) df.columns = df.iloc[0] mantap = df.reindex(df.index.drop(0)) #Load to Table logger = logging.getLogger() logger.setLevel(logging.INFO) try: conn = create_engine('postgresql://username:hostname:5432/password') except: logger.error( "ERROR: Unexpected error: Could not connect to PostgreSQL instance." ) sys.exit() logger.info("SUCCESS: Connection to RDS PostgreSQL instance succeeded") df.to_sql(name + '_' + suffix, conn, if_exists='replace', index=None) return render_template('flask.html', tables=[mantap.head().to_html()], sheet=names, worksheet=suffixs, hasil=names + '_' + suffixs)
def __init__(self, credential_file, workbook_key, sheet_names, model_identifier, static_values=None): """Creates an initial entry. Args: credential_file: path to json API key. workbook_key: google sheet key (in URL). sheet_names: name of sheets to be edited. model_identifier: unique model id to find previous entries. static_values (dict): columns and values that don't change. """ self.credential_file = credential_file self.workbook_key = workbook_key self.sheet_names = sheet_names self.model_identifier = model_identifier self.sheet_name = sheet_names[0] self.start_time = time.strftime('%Y/%m/%d %H:%M:%S') self.hostname = socket.getfqdn() self.static_values = dict() self.credential_key = json.load(self.credential_file) try: credentials = service_account.Credentials.from_service_account_info( self.credential_key, scopes=['https://www.googleapis.com/auth/spreadsheets']) self.client = gspread.Client(auth=credentials) self.client.session = AuthorizedSession(credentials) except: # pylint: disable=bare-except print('Could not authenticate with Drive API.') traceback.print_exc() # Create the first entry. if static_values: for key, val in static_values.items(): self.static_values[key] = val self.static_values['Model ID'] = model_identifier self.static_values['Hostname'] = self.hostname self.static_values['Start Time'] = self.start_time # Write experiment information to create row for future logging try: self.ready = True for sheet_name in self.sheet_names: self.update_or_append_row(self.static_values, sheet_name) except: # pylint: disable=bare-except self.ready = False traceback.print_exc() return
def gConnect(): global gDocLogin, gDocPSW, wakeTime, sleepTime, duration, connectionAttempts # Login with your Google account gc = gspread.Client(auth=(gDocLogin, gDocPSW)) while True: try: # Login & Connect to the spreadsheet gc.login() sht = gc.open_by_key(gDocURL) worksheet = sht.get_worksheet(0) # Get the values from the cells duration = int(worksheet.acell('B5').value) wakeHour = int(worksheet.acell('B2').value) wakeMin = int(worksheet.acell('C2').value) sleepHour = int(worksheet.acell('B3').value) sleepMin = int(worksheet.acell('C3').value) # Combine the data to construct the wake & sleep times / + timedelta(minutes=duration) wakeTime = datetime.datetime(year=2014, month=11, day=15, hour=wakeHour, minute=wakeMin, second=0, microsecond=0) sleepTime = datetime.datetime(year=2014, month=11, day=15, hour=sleepHour, minute=sleepMin, second=0, microsecond=0) # Reset connection attempts counter connectionAttempts = 0 break except: print "Could not connect to GDocs. Retrying..." # Update the connection attempts variable connectionAttempts = connectionAttempts + 1 # If too many failures, reboot if connectionAttempts > 10: os.system("sudo reboot") # Reset in 10s sleep(10)
def _get_spreadsheet_service(self): scope = ["https://spreadsheets.google.com/feeds"] key = json_loads(b64decode(self.configuration["jsonKeyFile"])) creds = ServiceAccountCredentials.from_json_keyfile_dict(key, scope) timeout_session = Session() timeout_session.requests_session = TimeoutSession() spreadsheetservice = gspread.Client(auth=creds, session=timeout_session) spreadsheetservice.login() return spreadsheetservice
def gspread_client(credentials): """ Returns a gspread client object. Google has deprecated Oauth2, but the gspread library still uses the creds from that system, so this function bypasses the regular approach and creates and authorizes the client here instead. Code copied from answer here: https://github.com/burnash/gspread/issues/472 """ gc = gspread.Client(auth=credentials) gc.session = AuthorizedSession(credentials) return gc
def get_client(args): scopes = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive'] if args.credentials.endswith('.json'): credentials = Credentials.from_service_account_file(args.credentials, scopes=scopes) else: print('credentials files must be in json format', file=sys.stderr) sys.exit() # https://github.com/burnash/gspread/blob/0f22a5d9f9adea7db72c94273d9f69a5a7711398/gspread/client.py#L27 # https://stackoverflow.com/a/59699007/6674256 client = gspread.Client(auth=credentials) client.session = AuthorizedSession(credentials) return client
def WriteSpreadSheet(temperature, humidity): try: gc = gspread.Client(auth=(email, password)) gc.login() # Open a worksheet from your spreadsheet using the filename sht = gc.open(spreadsheet) # Get first sheet worksheet = sht.get_worksheet(0) # Create and insert values values = [datetime.datetime.now(), temperature, humidity, bFridgeOn] worksheet.append_row(values) except Exception: print("Failed to connect to Google Spreadsheet")
def append_google_sheet(profile_dict, msg): scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive'] session = create_assertion_session(scope) client = gspread.Client(None, session) sheet = client.open(environ['GOOGLE_SHEET_NAME']).sheet1 # content = sheet.get_all_values() # print(content) now = datetime.now() row = [str(now), profile_dict['display_name'], profile_dict['picture_url'], profile_dict['status_message'], profile_dict['user_id'], msg] sheet.append_row(values=row, value_input_option='USER_ENTERED')
def update_spreadsheet(self, df=pd.DataFrame()): if df.empty: return while True: try: gc = gspread.Client(auth=self.scopedCreds) gc.session = AuthorizedSession(self.scopedCreds) sheet = gc.open(self.sheetName).sheet1 sheet.update([df.columns.values.tolist()] + df.values.tolist()) break except: print("Authentication error, trying again") pass
def gspread_client(): """ Sets BigQuery client. """ lib.bq_token_file_valid() credentials = service_account.Credentials.from_service_account_file( lib.bq_token_file_path(), scopes=[ 'https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive' ]) gc = gspread.Client(auth=credentials) gc.session = AuthorizedSession(credentials) return gc
def gspread_setup(self): """ Load gspread credentials from a file and return the object """ credentials=self._credentials auth=json.load(open(credentials)) scope = ['https://spreadsheets.google.com/feeds'] credentials = SignedJwtAssertionCredentials(auth['client_email'], auth['private_key'], scope) gc = gspread.Client(auth=credentials) gc.login() return gc
def setup_db(): global wb _service_account_info = json.loads(os.environ["GOOGLE_AUTH"]) _spreadsheetId = os.environ["WB_ID"] _scope = [ 'https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive' ] _credentials = service_account.Credentials.from_service_account_info( _service_account_info) _scoped_credentials = _credentials.with_scopes(_scope) _gc = gspread.Client(auth=_scoped_credentials) _gc.session = AuthorizedSession(_scoped_credentials) wb = _gc.open_by_key(_spreadsheetId)
def create_client(url): print '[+] Loading...' scope = [ 'https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive' ] creds = ServiceAccountCredentials.from_json_keyfile_name( 'cfa-exceptions-a4f72a8677f2.json', scope) client = gspread.authorize(creds) #headers = gspread.httpsession.HTTPSession(headers={'Connection': 'Keep-Alive'}) client = gspread.Client(auth=creds) client.login() sheet = client.open_by_url(url) return sheet
def __init__(self, config, data_collector_cbs): self.data_collector_cbs = data_collector_cbs self.cfg = config self.credentials = service_account.Credentials.from_service_account_file( self.cfg['credentials']) self.scoped_credentials = self.credentials.with_scopes( self.cfg['scope']) # Connect to sheet self.gc = gspread.Client(auth=self.scoped_credentials) self.gc.session = AuthorizedSession(self.scoped_credentials) self.sh = self.gc.open_by_key(self.cfg['key']) self.ws = self.sh.worksheet(self.cfg['sheet'])
def _get_worksheet_id(file_id, credentials=None, worksheet_title=None): """ Retrieve Google Sheet worksheet id from worksheet title :param file_id: drive file id :param credentials: google-auth credentials object :param worksheet_title: (str) title of spreadsheet, defaults to returning id of first spreadsheet if not specified :return: spreadsheet id """ gc = gspread.Client(auth=credentials) gc.session = AuthorizedSession(credentials) sheet = gc.open_by_key(file_id) if worksheet_title: worksheet = sheet.worksheet(worksheet_title) else: worksheet = sheet.get_worksheet(0) return worksheet.id