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
