Python gspread.authorize() Examples

def _init_gsheet_client():
        with GoogleSheet._client_lock():
            def _():
                if config.GOOGLE_SERVICE_ACCOUNT is not None:
                    credentials = ServiceAccountCredentials.from_json_keyfile_dict(
                        json.loads(config.GOOGLE_SERVICE_ACCOUNT),
                        scopes=SCOPES)
                else:
                    credentials = ServiceAccountCredentials.from_json_keyfile_name(
                        "avrae-google.json",
                        scopes=SCOPES)
                return gspread.authorize(credentials)

            try:
                GoogleSheet.g_client = await asyncio.get_event_loop().run_in_executor(None, _)
            except:
                GoogleSheet._client_initializing = False
                raise
        GoogleSheet._token_expiry = datetime.datetime.now() + datetime.timedelta(
            seconds=ServiceAccountCredentials.MAX_TOKEN_LIFETIME_SECS)
        log.info("Logged in to google") 

def handle(self, *args, **options):
        """Fetch Mess Menus."""

        # Use credentials to create a client to interact with the Google Drive API
        print("Authorizing - ", end="", flush=True)
        scope = ['https://spreadsheets.google.com/feeds',
                 'https://www.googleapis.com/auth/drive']

        creds = ServiceAccountCredentials.from_json_keyfile_name('google_client_secret.json', scope)
        client = gspread.authorize(creds)
        print("OK")

        # Iterate over all hostels
        for hostel in Hostel.objects.all():
            print("Updating " + hostel.name + " - ", end="", flush=True)
            if hostel.mess_gsheet:
                try:
                    fetch_hostel(client, hostel)
                    print("OK")
                except Exception:  # pylint: disable=W0703
                    print("FAIL")
            else:
                print("SKIP") 

def upload_test_results(results, name, sheet, credentials):
    """Upload the test results to the the google sheet"""

    if not sheet:
        return
    if not results:
        print('ERROR: No results specified')
        return

    credentials = ServiceAccountCredentials.from_json_keyfile_name(credentials, SCOPES)
    gcloud = gspread.authorize(credentials)

    spreadsheet = gcloud.open_by_url(sheet)

    try:
        worksheet = spreadsheet.worksheet(results["suite"])
    except gspread.exceptions.WorksheetNotFound:
        print(" * ERROR: Worksheet {} not found".format(results["suite"]))
        return

    filename = "{}_{}_{}.json".format(name, results.get('suite'), results.get('timestamp'))

    gsheets_import(results, worksheet, filename) 

def receive(self):
        CLIENT_EMAIL = self.param('receiving', 'client_email')
        PRIVATE_KEY = self.param('receiving', 'private_key')
        GOOGLE_SPREAD = self.param('receiving', 'google_sheet')

        scope = ['https://spreadsheets.google.com/feeds']
        credentials = SignedJwtAssertionCredentials(CLIENT_EMAIL, PRIVATE_KEY, scope)
        gc = gspread.authorize(credentials)
        sheet = gc.open(GOOGLE_SPREAD).sheet1

        READ_COL = self.param('receiving', 'column')

        cells = []
        row = 1

        while sheet.acell(READ_COL+str(row)).value:
            cells.append(sheet.acell(READ_COL+str(row)).value)
            row += 1

        return cells 

def send(self, data):
        CLIENT_EMAIL = self.param('sending', 'client_email')
        PRIVATE_KEY = self.param('sending', 'private_key')
        GOOGLE_SPREAD = self.param('sending', 'google_sheet')

        scope = ['https://spreadsheets.google.com/feeds']
        credentials = SignedJwtAssertionCredentials(CLIENT_EMAIL, PRIVATE_KEY, scope)
        gc = gspread.authorize(credentials)
        sheet = gc.open(GOOGLE_SPREAD).sheet1

        WRITE_COL = self.param('sending', 'column')
        row = 1
        while sheet.acell(WRITE_COL+str(row)).value:
            row += 1
        cell = WRITE_COL + str(row)

        sheet.update_acell(cell, data)
        return 

def setUp(self):

        configPath = os.path.join(basePath, 'config', 'google-config.json')
        try:
            with open(configPath, 'rb') as f:
                s = json.loads(f.read())
        except:
            raise SkipTest("Could not access Google configuration file.")

        self.params = s['google']

        scope = ['https://spreadsheets.google.com/feeds']
        credentials = SignedJwtAssertionCredentials(self.params['client_email'], self.params['private_key'], scope)       
        gc = gspread.authorize(credentials)
        
        self.client = gc.open(self.params['google_sheet']).sheet1
        self.randText = ''.join([random.choice(string.letters) for i in range(10)])

        self.channel = googleSpread.GoogleSpread()
        self.channel.params['sending'] = self.params
        self.channel.params['receiving'] = self.params 

def login_open_sheet(oauth_key_file, spreadsheet):
	"""Connect to Google Docs spreadsheet and return the first worksheet."""
	try:
		json_key = json.load(open(oauth_key_file))
		credentials = SignedJwtAssertionCredentials(json_key['client_email'], 
													json_key['private_key'], 
													['https://spreadsheets.google.com/feeds'])
		gc = gspread.authorize(credentials)
		worksheet = gc.open(spreadsheet).sheet1
		return worksheet
	except Exception as ex:
		print 'Unable to login and get spreadsheet.  Check OAuth credentials, spreadsheet name, and make sure spreadsheet is shared to the client_email address in the OAuth .json file!'
		print 'Google sheet login failed with error:', ex
		sys.exit(1) 

def get_spreadsheet(credentials, doc_id, tab_id):
    """
    Inputs params:
    * credentials
    * doc_id
    * tab_id
    Returns a gspread's worksheet object.
    """
    credentials = get_credentials(credentials)
    scope = [
        'https://www.googleapis.com/auth/spreadsheets'
    ]
    gspread_client = gspread.authorize(ServiceAccountCredentials.from_json_keyfile_dict(credentials, scope))

    try:
        return gspread_client.open_by_key(doc_id).worksheet(tab_id)
    except gspread.exceptions.SpreadsheetNotFound as e:
        raise Exception("Trying to open non-existent or inaccessible spreadsheet document.")
    except gspread.exceptions.WorksheetNotFound as e:
        raise Exception("Trying to open non-existent sheet. Verify that the sheet name exists (%s)." % tab_id)
    except gspread.exceptions.APIError as e:
        if hasattr(e, 'response'):
            error_json = e.response.json()
            print(error_json)
            error_status = error_json.get("error", {}).get("status")
            email = credentials.get("client_email", "(email missing)")
            if error_status == 'PERMISSION_DENIED':
                error_message = error_json.get("error", {}).get("message", "")
                raise Exception("Access was denied with the following error: %s. Have you enabled the Sheets API? Have you shared the spreadsheet with %s?" % (error_message, email))
            if error_status == 'NOT_FOUND':
                raise Exception("Trying to open non-existent spreadsheet document. Verify the document id exists (%s)." % doc_id)
        raise Exception("The Google API returned an error: %s" % e) 

def login_open_sheet(oauth_key_file, spreadsheet):
	"""Connect to Google Docs spreadsheet and return the first worksheet."""
	try:
		json_key = json.load(open(oauth_key_file))
		credentials = SignedJwtAssertionCredentials(json_key['client_email'],
													json_key['private_key'],
													['https://spreadsheets.google.com/feeds'])
		gc = gspread.authorize(credentials)
		worksheet = gc.open(spreadsheet).sheet1
		return worksheet
	except Exception as ex:
		print 'Unable to login and get spreadsheet.  Check OAuth credentials, spreadsheet name, and make sure spreadsheet is shared to the client_email address in the OAuth .json file!'
		print 'Google sheet login failed with error:', ex
		sys.exit(1) 

def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--json", required=True, nargs="+", help="json test results filename(s) to import")
    parser.add_argument("--sheet", required=True, help="spreadsheet url")
    parser.add_argument("--credentials", default="credentials.json", help="credentials filename")
    parser.add_argument("--start_col", default="1", type=int, help="reserve some columns for manually entered details")
    parser.add_argument("--insert", action="store_true", help="insert new results at the top rather than the bottom")
    args = parser.parse_args()

    credentials = ServiceAccountCredentials.from_json_keyfile_name(args.credentials, SCOPES)
    gcloud = gspread.authorize(credentials)

    spreadsheet = gcloud.open_by_url(args.sheet)

    worksheets = spreadsheet.worksheets()

    for json_file_name in args.json:
        with open(json_file_name) as json_file:
            test_results = json.load(json_file)

        try:
            worksheet = next(x for x in worksheets if x.title == test_results["suite"])
        except StopIteration:
            print(" * ERROR: Worksheet {} not found".format(test_results["suite"]))
            # could add_worksheet?
            sys.exit(1)

        gsheets_import(test_results, worksheet, json_file_name, args.start_col, args.insert) 

def connect_to_sheets():
    """ Returns a Google Sheets client and the creds used by that client.

    If intentionally disabling Google Sheets functionality, `None` will be
    returned as both the client and client credentials. This occurs even if the
    proper credential file is present.

    If missing credentials while in DEBUG mode, a "soft failure" will occur:
    the logger will note the missing credentials file and will return `None`
    for both client and credentials.

    This allows us to run a webserver in a development environment that will
    never actually update Google spreadsheets. We can go through the flow of
    modifying discounts without running a Celery job to update the spreadsheet.
    """
    scope = ['https://spreadsheets.google.com/feeds']
    credfile = settings.OAUTH_JSON_CREDENTIALS
    creds_present = credfile and os.path.exists(credfile)

    # Avoid unnecessary handshake with a service we'll never use
    if settings.DISABLE_GSHEETS:
        return None, None

    if not creds_present:
        if settings.DEBUG:
            logger.error(
                "OAUTH_JSON_CREDENTIALS is missing! " "Unable to update Google Sheets."
            )
            return None, None
        raise KeyError("Specify OAUTH_JSON_CREDENTIALS to update Google Sheets")

    credentials = ServiceAccountCredentials.from_json_keyfile_name(credfile, scope)
    return gspread.authorize(credentials), credentials 

def append_to_sheet(sheet_name, vals, worksheet_index=0):
    creds = ServiceAccountCredentials.from_json_keyfile_dict(credential_info, scope)
    client = gspread.authorize(creds)
    sheet = client.open(sheet_name).get_worksheet(worksheet_index)
    sheet.append_row(vals) 

def read_sheet(sheet_name, worksheet_index=0):
    creds = ServiceAccountCredentials.from_json_keyfile_dict(credential_info, scope)
    client = gspread.authorize(creds)
    sheet = client.open(sheet_name).get_worksheet(worksheet_index)
    return sheet.get_all_records() 

def __init__(self, sheet_name, sheet_index):
        # use creds to create a client to interact with the Google Drive API
        scope = [
            'https://spreadsheets.google.com/feeds',
            'https://www.googleapis.com/auth/drive'
        ]
        google_api = os.path.expanduser(config.RUN['google_api_secret'])
        creds = ServiceAccountCredentials.from_json_keyfile_name(
            google_api, scope
        )
        client = gspread.authorize(creds)
        self.sheet = client.open(sheet_name).get_worksheet(sheet_index) 

def get_worksheet(self, key, sheet):
        date = datetime.date.today()
        days_until_saturday = 5 - date.weekday()
        next_saturday = date + datetime.timedelta(days_until_saturday)

        scope = ['https://spreadsheets.google.com/feeds']
        credentials = ServiceAccountCredentials.from_json_keyfile_name(key, scope)
        gc = gspread.authorize(credentials)
        sheet_name = 'Guildwar %s' % next_saturday.strftime('%m-%d-%Y')
        return gc.open(sheet_name) 

def request_sheet(self, sheet_name: Text) -> Optional[Spreadsheet]:
        # fetch a specific sheet
        logging.debug("Refreshing auth")
        try:
            return gspread.authorize(self.credentials).open(sheet_name)
        except Exception as e:
            logging.error(
                "Failed to create google spreadsheet connection. %s", e, exc_info=True
            )
            return None 

def get_access():
    scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
    credentials = ServiceAccountCredentials.from_json_keyfile_name('confs/google_sheets.json', scope)
    gc = gspread.authorize(credentials)
    return gc 

def create_google_sheet(sheetname):
    scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
    credentials = ServiceAccountCredentials.from_json_keyfile_name('confs/google_sheets.json', scope)
    gc = gspread.authorize(credentials)

    wks = gc.create(sheetname).sheet1

    return wks 

def get_sheet(self, ctx) -> gspread.Worksheet:
        """Return values from spreadsheet."""
        server = ctx.message.server

        credentials = ServiceAccountCredentials.from_json_keyfile_name(
            SERVICE_KEY_JSON, scopes=SCOPES)
        spreadsheetId = self.settings[server.id]["SHEET_ID"]
        gc = gspread.authorize(credentials)
        sh = gc.open_by_key(spreadsheetId)
        worksheet = sh.get_worksheet(0)

        return worksheet 

def write_to_google_sheet(row):
    # use creds to create a client to interact with the Google Drive API
    scope = ['https://spreadsheets.google.com/feeds',
             'https://www.googleapis.com/auth/drive']
    credentials = ServiceAccountCredentials.from_json_keyfile_name('./client_secret.json', scope)
    client = gspread.authorize(credentials)

    sheet = client.open("2017-2018-PL-tips").sheet1
    sheet.append_row(row) 

def setUpClass(cls):
        try:
            cls.config = read_config(CONFIG_FILENAME)
            credentials = read_credentials(CREDS_FILENAME)
            cls.gc = gspread.authorize(credentials)
        except IOError as e:
            msg = "Can't find %s for reading test configuration. "
            raise Exception(msg % e.filename) 

def test_toggl2gsuite(self):
        # have to do this year by year
        data = {
            'workspace_id': os.environ['WORKSPACE_ID'],
        }
        y = self.toggl.getDetailedReport(data)


        credentials = ServiceAccountCredentials.from_json_keyfile_name(
            os.environ['KEYFILE'],
            ['https://spreadsheets.google.com/feeds'])

        client = gspread.authorize(credentials)
        sheet = client.open_by_url(os.environ['SHEET_URL'])
        worksheet = sheet.get_worksheet(0)

        wrote_header = False
        columns_to_write = ['user', 'updated', 'start', 'end', 'client', 'project', 'description', 'is_billable',
                            'billable']
        cell_row = 0
        for row_idx, rec in enumerate(y['data']):
            if wrote_header == False:
                for col_idx, header in enumerate(columns_to_write):
                    worksheet.update_acell(Toggl2GSuiteTest.excel_style(row_idx + 1, col_idx + 1), header)
                wrote_header = True
            for col_idx, header in enumerate(columns_to_write):
                worksheet.update_acell(Toggl2GSuiteTest.excel_style(row_idx + 2, col_idx + 1), rec[header]) 

def get_credentials(cred_file):
    scope = ['https://spreadsheets.google.com/feeds']
    credentials = ServiceAccountCredentials.from_json_keyfile_name(cred_file, scope)
    return gspread.authorize(credentials) 

def __init__(self, spreadsheet_name):
        self.item_col = 1
        self.price_col = 2
        self.frequency_col = 3
        self.url_col = 4
        self.product_name_col = 5
        
        scope = ['https://spreadsheets.google.com/feeds',
                 'https://www.googleapis.com/auth/drive']

        creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json',
                                                                 scope)
        client = gspread.authorize(creds)

        self.sheet = client.open(spreadsheet_name).sheet1 

def get_repo_request_rows():
    from oauth2client.service_account import ServiceAccountCredentials

    # this file inspired by https://www.twilio.com/blog/2017/02/an-easy-way-to-read-and-write-to-a-google-spreadsheet-in-python.html

    # use creds to create a client to interact with the Google Drive API
    scopes = ['https://spreadsheets.google.com/feeds']
    json_creds = os.getenv("GOOGLE_SHEETS_CREDS_JSON")

    creds_dict = json.loads(json_creds)

    # hack to get around ugly new line escaping issues
    # this works for me, but later found links to what might be cleaner solutions:
    # use ast.literal_eval?  https://github.com/googleapis/google-api-go-client/issues/185#issuecomment-422732250
    # or maybe dumping like this might fix it? https://coreyward.svbtle.com/how-to-send-a-multiline-file-to-heroku-config

    creds_dict["private_key"] = creds_dict["private_key"].replace("\\\\n", "\n")

    # now continue
    creds = ServiceAccountCredentials.from_json_keyfile_dict(creds_dict, scopes)
    client = gspread.authorize(creds)

    # Find a workbook by url
    spreadsheet = client.open_by_url("https://docs.google.com/spreadsheets/d/1RcQuetbKVYRRf0GhGZQi38okY8gT1cPUs6l3RM94yQo/edit#gid=704459328")
    sheet = spreadsheet.sheet1

    # Extract and print all of the values
    rows = sheet.get_all_values()
    print(rows[0:1])
    return rows 

def _open_doc(url):
    scope = ['https://spreadsheets.google.com/feeds']
    credentials = ServiceAccountCredentials.from_json_keyfile_name(
        settings.GOOGLE_SERVICE_ACCOUNT_KEYFILE_PATH, scope)
    gc = gspread.authorize(credentials)
    try:
        return gc.open_by_url(url)
    except gspread.SpreadsheetNotFound:
        raise SpreadsheetNotFound 

def save_row(self, csv_type, data_type, data):
        if data_type == 'entry':
            if csv_type == 'run_logger':
                tab = 'Runs'
                last_column = 'Y'
                total = 'AA1'
            elif csv_type == 'arena_logger':
                tab = 'Arena'
                last_column = 'P'
                total = 'R1'
            elif csv_type == 'summon_logger':
                tab = 'Summon'
                last_column = 'F'
                total = 'H1'
            elif csv_type == 'raid_logger':
                tab = 'Raid'
                last_column = 'K'
                total = 'M1'
            elif csv_type == 'worldboss_logger':
                tab = 'World Boss'
                last_column = 'AA'
                total = 'AC1'
            elif csv_type == 'toa_logger':
                tab = 'ToA'
                last_column = 'O'
                total = 'Q1'
            elif csv_type == 'guild_battle_logger':
                tab = 'Guild'
                last_column = 'S'
                total = 'U1'

            names, row = data
            key_file = self.config['google_key']
            sheet_name = self.config['sheet_name']
            scope = ['https://spreadsheets.google.com/feeds']
            credentials = ServiceAccountCredentials.from_json_keyfile_name(key_file, scope)
            gc = gspread.authorize(credentials)
            wks = gc.open(sheet_name).worksheet(tab)
            line = int(wks.acell(total).value) + 2
            cl = wks.range('A%s:%s%s' % (line, last_column, line))
            for (i, name) in enumerate(names):
                if name in row:
                    cl[i].value = row[name]

            wks.update_cells(cl) 

def send_alert_to_google_sheet(google_api_creds, data, name, sheet='', index=1, search_criteria=''):
    try:
        results = []
        scope = ['https://spreadsheets.google.com/feeds',
                 'https://www.googleapis.com/auth/drive']
        credentials = ServiceAccountCredentials.from_json_keyfile_name(google_api_creds, scope)
        client = gspread.authorize(credentials)
        sheet = client.open(name).worksheet(sheet)

        limit = 100
        if config.has_option('api', 'google_write_requests_per_100_seconds_per_user'):
            limit = config.getint('api', 'google_write_requests_per_100_seconds_per_user')
        inserted = 0
        for url in data:
            symbol = data[url][0]
            alert = data[url][1]
            date = data[url][2]
            screenshots = data[url][3]
            [exchange, market] = symbol.split(':')
            screenshot = ''
            for chart in screenshots:
                if screenshot == '':
                    screenshot = screenshots[chart]
            # noinspection PyBroadException
            try:
                setup = alert.split(',')[0]
            except Exception:
                setup = name

            timeframe = ''
            match = re.search("(\\d+\\s\\w+),", alert)
            if match:
                timeframe = match.group(1)
            row = [date, setup, timeframe, url, screenshot, exchange, market]
            if TEST:
                log.info(row)
            else:
                result = ''
                if len(search_criteria) == 0:
                    result = sheet.insert_row(row, index, 'RAW')
                else:
                    for search_criterium in search_criteria:
                        if str(alert).find(str(search_criterium)) >= 0:
                            result = sheet.insert_row(row, index)
                            break
                if result:
                    results.append(result)
                    log.debug(str(result))
                inserted += 1
                if inserted == 100:
                    log.info('API limit reached. Waiting {} seconds before continuing...' + str(limit))
                    time.sleep(limit)
                    inserted = 0
        if len(results) == 1:
            log.info(str(len(results)) + ' row inserted')
        else:
            log.info(str(len(results)) + ' rows inserted')
    except Exception as e:
        log.exception(e) 

def __init__(self, spread_sheet_name, path_for_worksheet_name, row_name, credential_path = None):
        if not os.path.exists(path_for_worksheet_name):
            os.makedirs(path_for_worksheet_name)

        self.config_file = os.path.join(path_for_worksheet_name, 'sheet.config.json')
        if os.path.exists(self.config_file):
            with open(self.config_file, 'r') as fin:
                all_data = json.load(fin)
                self._name_dict = all_data['name_dict']
                self._metric_dict = all_data['metric_dict']
                if credential_path is None:
                    self.credential_path = all_data['credential_path']
                else:
                    self.credential_path = credential_path
                if "path_for_worksheet_name" in all_data:
                    loaded_path_for_worksheet_name = all_data["path_for_worksheet_name"]
                else:
                    loaded_path_for_worksheet_name = None
        else:
            loaded_path_for_worksheet_name = None
            self._name_dict = dict()
            self._metric_dict = dict()
            # assert (self.credential_path is not None)
            if credential_path is None:
                self.credential_path = '/shared/data/ll2/Torch-Scope/torch-scope-8acf12bee10f.json'
            else:
                self.credential_path = credential_path

        self._scope = ['https://spreadsheets.google.com/feeds',
                 'https://www.googleapis.com/auth/drive']

        self._credentials = ServiceAccountCredentials.from_json_keyfile_name(self.credential_path, self._scope)

        self._gc = gspread.authorize(self._credentials)

        self._sh = self._gc.open(spread_sheet_name)

        self.path_for_worksheet_name = os.path.realpath(os.path.expanduser(path_for_worksheet_name))

        if loaded_path_for_worksheet_name is None:
            self._wks = self._sh.add_worksheet(title=self.path_for_worksheet_name, rows="100", cols="26")
        else:
            self._wks = self._sh.worksheet(loaded_path_for_worksheet_name)
            if self._wks is None:
                self._wks = self._sh.add_worksheet(title=self.path_for_worksheet_name, rows="100", cols="26")
            else:
                self.path_for_worksheet_name = loaded_path_for_worksheet_name

        if row_name not in self._name_dict:
            self._name_dict[row_name] = len(self._name_dict) + 2
            self.save_config()

        self.row_index = self._name_dict[row_name]
        self._wks.update_cell(self.row_index, 1, row_name)