Beispiel #1
0
 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)
Beispiel #2
0
    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)
Beispiel #3
0
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
Beispiel #4
0
 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-'
Beispiel #5
0
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
Beispiel #6
0
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])
Beispiel #7
0
    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))
Beispiel #8
0
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
Beispiel #10
0
    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
Beispiel #11
0
    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)
Beispiel #12
0
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
Beispiel #13
0
def gspread_client(gc=None):
    if gc:
        return gc

    creds = credentials("sheets")

    gc = gspread.Client(auth=creds)
    gc.session = AuthorizedSession(creds)
    return gc
Beispiel #14
0
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()
Beispiel #15
0
 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)
Beispiel #17
0
    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
Beispiel #18
0
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
Beispiel #20
0
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
Beispiel #21
0
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
Beispiel #22
0
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")
Beispiel #23
0
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')
Beispiel #24
0
 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
Beispiel #25
0
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
Beispiel #26
0
	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
Beispiel #27
0
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)
Beispiel #28
0
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
Beispiel #29
0
    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'])
Beispiel #30
0
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