def setup_auth(args):
"""Set up and authentication httplib.
Args:
args: ArgumentParser with additional command-line flags to pass to
the OAuth authentication flow.
Returns:
An http client library with authentication enabled.
"""
# Perform OAuth 2.0 authorization.
if args.service_account:
# Service accounts will follow the following authenication.
client_email = args.service_account
secret_file = args.service_account_secrets_file
if secret_file.endswith('json'):
credentials = ServiceAccountCredentials.from_json_keyfile_name(
secret_file, SCOPES)
elif secret_file.endswith('p12'):
credentials = ServiceAccountCredentials.from_p12_keyfile(
client_email, secret_file, SCOPES)
else:
flow = flow_from_clientsecrets(args.client_secrets_file, scope=SCOPES)
storage = oauth_file.Storage(OAUTH2_STORAGE)
credentials = storage.get()
if credentials is None or credentials.invalid:
credentials = tools.run_flow(flow, storage, args)
http = httplib2.Http()
return credentials.authorize(http)
def __init__(self, service_email=None, private_key=None, user_email=None):
"""
Handles credentials and builds the google service.
:param service_email: String
:param private_key: Path
:param user_email: String
:raise ValueError:
"""
self._service_email = service_email or settings.GOOGLE_DRIVE_STORAGE_SERVICE_EMAIL
self._key = private_key or settings.GOOGLE_DRIVE_STORAGE_KEY
kwargs = {}
if user_email or settings.GOOGLE_DRIVE_STORAGE_USER_EMAIL:
self._user_email = kwargs['sub'] = user_email or settings.GOOGLE_DRIVE_STORAGE_USER_EMAIL
credentials = ServiceAccountCredentials(
self._service_email,
self._key,
scope="https://www.googleapis.com/auth/drive",
**kwargs
)
http = httplib2.Http()
http = credentials.authorize(http)
self._drive_service = build('drive', 'v2', http=http)
def get_service_acct_creds(key_file, verbose=False):
'''Generate service account credentials using the given key file.
key_file: path to file containing private key.
'''
### backcompatability for .p12 keyfiles
if key_file.endswith('.p12') or key_file.endswith('.pem'):
from edx2bigquery_config import auth_service_acct as SERVICE_ACCT
if verbose:
print "using key file"
print "service_acct=%s, key_file=%s" % (SERVICE_ACCT, KEY_FILE)
try:
creds = ServiceAccountCredentials.from_p12_keyfile(
SERVICE_ACCT,
key_file,
scopes=BIGQUERY_SCOPE)
except Exception as err: # fallback to old google SignedJwtAssertionCredentials call
with open (key_file, 'rb') as f:
key = f.read();
creds = SignedJwtAssertionCredentials(
SERVICE_ACCT,
key,
BIGQUERY_SCOPE)
return creds
###
creds = ServiceAccountCredentials.from_json_keyfile_name(
key_file,
BIGQUERY_SCOPE)
return creds
def __init__(self, config):
self.api_key = config["apiKey"]
self.auth_domain = config["authDomain"]
self.database_url = config["databaseURL"]
self.storage_bucket = config["storageBucket"]
self.credentials = None
self.requests = requests.Session()
if config.get("serviceAccount"):
scopes = [
'https://www.googleapis.com/auth/firebase.database',
'https://www.googleapis.com/auth/userinfo.email',
"https://www.googleapis.com/auth/cloud-platform"
]
service_account_type = type(config["serviceAccount"])
if service_account_type is str:
self.credentials = ServiceAccountCredentials.from_json_keyfile_name(config["serviceAccount"], scopes)
if service_account_type is dict:
self.credentials = ServiceAccountCredentials.from_json_keyfile_dict(config["serviceAccount"], scopes)
if is_appengine_sandbox():
# Fix error in standard GAE environment
# is releated to https://github.com/kennethreitz/requests/issues/3187
# ProtocolError('Connection aborted.', error(13, 'Permission denied'))
adapter = appengine.AppEngineAdapter(max_retries=3)
else:
adapter = requests.adapters.HTTPAdapter(max_retries=3)
for scheme in ('http://', 'https://'):
self.requests.mount(scheme, adapter)
def get_credentials():
if os.path.isfile(CLIENT_SECRET_FILE):
credentials = ServiceAccountCredentials.from_json_keyfile_name(CLIENT_SECRET_FILE, scopes=API_SCOPES)
else:
keyfile_data = json.loads(os.environ.get('KEYFILE'))
credentials = ServiceAccountCredentials.from_json_keyfile_dict(keyfile_dict=keyfile_data, scopes=API_SCOPES)
return credentials
def _from_p12_keyfile_helper(self, private_key_password=None, scopes='',
token_uri=None, revoke_uri=None):
service_account_email = '*****@*****.**'
filename = data_filename('privatekey.p12')
with open(filename, 'rb') as file_obj:
key_contents = file_obj.read()
creds_from_filename = ServiceAccountCredentials.from_p12_keyfile(
service_account_email, filename,
private_key_password=private_key_password,
scopes=scopes, token_uri=token_uri, revoke_uri=revoke_uri)
creds_from_file_contents = (
ServiceAccountCredentials.from_p12_keyfile_buffer(
service_account_email, BytesIO(key_contents),
private_key_password=private_key_password,
scopes=scopes, token_uri=token_uri, revoke_uri=revoke_uri))
for creds in (creds_from_filename, creds_from_file_contents):
self.assertIsInstance(creds, ServiceAccountCredentials)
self.assertIsNone(creds.client_id)
self.assertEqual(creds._service_account_email, service_account_email)
self.assertIsNone(creds._private_key_id)
self.assertIsNone(creds._private_key_pkcs8_pem)
self.assertEqual(creds._private_key_pkcs12, key_contents)
if private_key_password is not None:
self.assertEqual(creds._private_key_password, private_key_password)
self.assertEqual(creds._scopes, ' '.join(scopes))
self.assertEqual(creds.token_uri, token_uri)
self.assertEqual(creds.revoke_uri, revoke_uri)
def _build_service(self, api_name, api_version, scopes):
if self._keyfile_path is None:
credentials = GoogleCredentials.get_application_default()
service = build(api_name, api_version, credentials=credentials)
return credentials, service
else:
if self._keyfile_path.lower().endswith(".json"):
credentials = ServiceAccountCredentials.from_json_keyfile_name(
self._keyfile_path,
scopes=scopes)
elif self._keyfile_path.lower().endswith(".p12"):
if self._account_email is None:
raise Exception("Input account email.")
credentials = ServiceAccountCredentials.from_p12_keyfile(
self._account_email,
self._keyfile_path,
scopes=scopes)
else:
error_message = """
Key file format [{0}] is illegal.
Key file must be .json or .p12.
""".format(self._keyfile_path)
raise Exception(error_message)
#http = httplib2.Http()
#auth_http = credentials.authorize(http)
#service = build(api_name, api_version, http=auth_http)
service = build(api_name, api_version, credentials=credentials)
return credentials, service
def _GetOauth2ServiceAccountCredentials():
"""Retrieves OAuth2 service account credentials for a private key file."""
if not _HasOauth2ServiceAccountCreds():
return
provider_token_uri = _GetProviderTokenUri()
service_client_id = config.get('Credentials', 'gs_service_client_id', '')
private_key_filename = config.get('Credentials', 'gs_service_key_file', '')
with io.open(private_key_filename, 'rb') as private_key_file:
private_key = private_key_file.read()
keyfile_is_utf8 = False
try:
private_key = private_key.decode(UTF8)
# P12 keys won't be encoded as UTF8 bytes.
keyfile_is_utf8 = True
except UnicodeDecodeError:
pass
if keyfile_is_utf8:
try:
json_key_dict = json.loads(private_key)
except ValueError:
raise Exception('Could not parse JSON keyfile "%s" as valid JSON' %
private_key_filename)
# Key file is in JSON format.
for json_entry in ('client_id', 'client_email', 'private_key_id',
'private_key'):
if json_entry not in json_key_dict:
raise Exception('The JSON private key file at %s '
'did not contain the required entry: %s' %
(private_key_filename, json_entry))
return ServiceAccountCredentials.from_json_keyfile_dict(
json_key_dict, scopes=DEFAULT_SCOPES, token_uri=provider_token_uri)
else:
# Key file is in P12 format.
if HAS_CRYPTO:
if not service_client_id:
raise Exception('gs_service_client_id must be set if '
'gs_service_key_file is set to a .p12 key file')
key_file_pass = config.get('Credentials', 'gs_service_key_file_password',
GOOGLE_OAUTH2_DEFAULT_FILE_PASSWORD)
# We use _from_p12_keyfile_contents to avoid reading the key file
# again unnecessarily.
try:
return ServiceAccountCredentials.from_p12_keyfile_buffer(
service_client_id,
BytesIO(private_key),
private_key_password=key_file_pass,
scopes=DEFAULT_SCOPES,
token_uri=provider_token_uri)
except Exception as e:
raise Exception(
'OpenSSL unable to parse PKCS 12 key {}.'
'Please verify key integrity. Error message:\n{}'.format(
private_key_filename, str(e)))
def test_create_delegated(self):
signer = object()
sub = '*****@*****.**'
creds = ServiceAccountCredentials('*****@*****.**', signer)
self.assertNotIn('sub', creds._kwargs)
delegated_creds = creds.create_delegated(sub)
self.assertEqual(delegated_creds._kwargs['sub'], sub)
# Make sure the original is unchanged.
self.assertNotIn('sub', creds._kwargs)
def test_create_delegated_existing_sub(self):
signer = object()
sub1 = '*****@*****.**'
sub2 = '*****@*****.**'
creds = ServiceAccountCredentials('*****@*****.**', signer, sub=sub1)
self.assertEqual(creds._kwargs['sub'], sub1)
delegated_creds = creds.create_delegated(sub2)
self.assertEqual(delegated_creds._kwargs['sub'], sub2)
# Make sure the original is unchanged.
self.assertEqual(creds._kwargs['sub'], sub1)
def test_p12_type_non_bytes_to_sign(self):
from oauth2client.service_account import ServiceAccountCredentials
ACCOUNT_NAME = 'dummy_service_account_name'
PRIVATE_KEY_TEXT = b'dummy_private_key_text'
STRING_TO_SIGN = u'dummy_signature'
SIGNER = object()
CREDENTIALS = ServiceAccountCredentials(
ACCOUNT_NAME, SIGNER)
CREDENTIALS._private_key_pkcs12 = PRIVATE_KEY_TEXT
CREDENTIALS._private_key_password = '******'
self._run_with_fake_crypto(CREDENTIALS, PRIVATE_KEY_TEXT,
STRING_TO_SIGN)
def setup_credentials():
scope = ['https://spreadsheets.google.com/feeds']
if on_heroku:
keyfile_dict = setup_keyfile_dict()
credentials = ServiceAccountCredentials.from_json_keyfile_dict(keyfile_dict, scope)
else:
credentials = ServiceAccountCredentials.from_json_keyfile_name('My Project-3b0bc29d35d3.json', scope)
gc = gspread.authorize(credentials)
wks = gc.open_by_key("1GnVhFp0s28HxAEOP6v7kmfmt3yPL_TGJSV2mcn1RPMY").sheet1
return wks
def _create_drive_service(creds_file=None, creds_json=None):
SCOPES = ['https://www.googleapis.com/auth/drive.readonly']
if creds_json is not None:
creds = json.loads(creds_json)
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
creds, SCOPES)
elif creds_file is not None:
credentials = ServiceAccountCredentials.from_json_keyfile_name(
creds_file, SCOPES)
else:
raise ValueError("Either creds_file or creds_json must be defined")
http_auth = credentials.authorize(httplib2.Http())
return build('drive', 'v3', http=http_auth)
def _make_credentials(self):
private_key = datafile('privatekey.' + self.format_)
signer = crypt.Signer.from_string(private_key)
credentials = ServiceAccountCredentials(
'*****@*****.**', signer,
scopes='read+write',
sub='*****@*****.**')
if self.format_ == 'pem':
credentials._private_key_pkcs8_pem = private_key
elif self.format_ == 'p12':
credentials._private_key_pkcs12 = private_key
credentials._private_key_password = _PASSWORD_DEFAULT
else: # pragma: NO COVER
raise ValueError('Unexpected format.')
return credentials
def setup_credentials():
scope = ['https://spreadsheets.google.com/feeds']
if on_heroku:
keyfile_dict = setup_keyfile_dict()
credentials = ServiceAccountCredentials.from_json_keyfile_dict(keyfile_dict, scope)
else:
credentials = ServiceAccountCredentials.from_json_keyfile_name('My Project-3b0bc29d35d3.json', scope)
gc = gspread.authorize(credentials)
if on_heroku:
sps = gc.open_by_key("1DdmBaOlGGdgQRaaI3tQCxj3BEd8kPwaGIHVfMpIoH8I")
else:
sps = gc.open_by_key("1DdmBaOlGGdgQRaaI3tQCxj3BEd8kPwaGIHVfMpIoH8I")
return sps
def http(self, sub=None):
credentials = ServiceAccountCredentials.from_p12_keyfile(
'faf-server',
'faf-server.pem',
scopes='write_achievements write_events'
)
return credentials.authorize(Http())
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 from_service_account_json(cls, json_credentials_path, *args, **kwargs):
"""Factory to retrieve JSON credentials while creating client.
:type json_credentials_path: string
:param json_credentials_path: The path to a private key file (this file
was given to you when you created the
service account). This file must contain
a JSON object with a private key and
other credentials information (downloaded
from the Google APIs console).
:type args: tuple
:param args: Remaining positional arguments to pass to constructor.
:type kwargs: dict
:param kwargs: Remaining keyword arguments to pass to constructor.
:rtype: :class:`gcloud.pubsub.client.Client`
:returns: The client created with the retrieved JSON credentials.
:raises: :class:`TypeError` if there is a conflict with the kwargs
and the credentials created by the factory.
"""
if "credentials" in kwargs:
raise TypeError("credentials must not be in keyword arguments")
credentials = ServiceAccountCredentials.from_json_keyfile_name(json_credentials_path)
kwargs["credentials"] = credentials
return cls(*args, **kwargs)
def __init__(self, creds_file):
credentials = ServiceAccountCredentials.from_json_keyfile_name(
creds_file, SCOPES)
self.gc = gspread.authorize(credentials)
self.storage_service = discovery.build('storage', 'v1',
credentials=credentials)
def from_service_account_p12(cls, client_email, private_key_path, *args, **kwargs):
"""Factory to retrieve P12 credentials while creating client.
.. note::
Unless you have an explicit reason to use a PKCS12 key for your
service account, we recommend using a JSON key.
:type client_email: string
:param client_email: The e-mail attached to the service account.
:type private_key_path: string
:param private_key_path: The path to a private key file (this file was
given to you when you created the service
account). This file must be in P12 format.
:type args: tuple
:param args: Remaining positional arguments to pass to constructor.
:type kwargs: dict
:param kwargs: Remaining keyword arguments to pass to constructor.
:rtype: :class:`gcloud.client.Client`
:returns: The client created with the retrieved P12 credentials.
:raises: :class:`TypeError` if there is a conflict with the kwargs
and the credentials created by the factory.
"""
if "credentials" in kwargs:
raise TypeError("credentials must not be in keyword arguments")
credentials = ServiceAccountCredentials.from_p12_keyfile(client_email, private_key_path)
kwargs["credentials"] = credentials
return cls(*args, **kwargs)
def get_worksheet():
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name(app.config['JSON_KEYFILE_NAME'],scope);
gc = gspread.authorize(credentials)
sheet = gc.open_by_key(app.config['GOOGLE_SHEET_KEY'])
worksheet = sheet.get_worksheet(0)
return worksheet
def test__to_json_override(self):
signer = object()
creds = ServiceAccountCredentials('*****@*****.**',
signer)
self.assertEqual(creds._signer, signer)
# Serialize over-ridden data (unrelated to ``creds``).
to_serialize = {'unrelated': 'data'}
serialized_str = creds._to_json([], to_serialize.copy())
serialized_data = json.loads(serialized_str)
expected_serialized = {
'_class': 'ServiceAccountCredentials',
'_module': 'oauth2client.service_account',
'token_expiry': None,
}
expected_serialized.update(to_serialize)
self.assertEqual(serialized_data, expected_serialized)
def get_for_service_account_p12(client_email, private_key_path, scope=None):
"""Gets the credentials for a service account with PKCS12 / p12 key.
.. note::
This method is not used by default, instead :func:`get_credentials`
is used. This method is intended to be used when the environment is
known explicitly and detecting the environment implicitly would be
superfluous.
:type client_email: string
:param client_email: The e-mail attached to the service account.
:type private_key_path: string
:param private_key_path: The path to a private key file (this file was
given to you when you created the service
account). This file must be in P12 format.
:type scope: string or tuple of string
:param scope: The scope against which to authenticate. (Different services
require different scopes, check the documentation for which
scope is required for the different levels of access to any
particular API.)
:rtype: :class:`oauth2client.service_account.ServiceAccountCredentials`
:returns: A new ``ServiceAccountCredentials`` instance with the
needed service account settings.
"""
return ServiceAccountCredentials.from_p12_keyfile(
client_email, private_key_path, scopes=scope)
def __init__(self, account_json):
scopes = ['https://www.googleapis.com/auth/ndev.clouddns.readwrite']
credentials = ServiceAccountCredentials.from_json_keyfile_name(account_json, scopes)
self.dns = discovery.build('dns', 'v1', credentials=credentials, cache_discovery=False)
with open(account_json) as account:
self.project_id = json.load(account)['project_id']
def get_service(api_name, api_version, scopes, key_file_location, service_account_email):
"""Get a service that communicates to a Google API.
Args:
api_name: The name of the api to connect to.
api_version: The api version to connect to.
scope: A list auth scopes to authorize for the application.
key_file_location: The path to a valid service account p12 key file.
service_account_email: The service account email address.
Returns:
A service that is connected to the specified API.
"""
credentials = ServiceAccountCredentials.from_p12_keyfile(
service_account_email,
key_file_location,
scopes=scopes
)
http = credentials.authorize(httplib2.Http())
# Build the service object.
service = build(api_name, api_version, http=http)
return service
def write_sheet(keyword, rows):
credentials = ServiceAccountCredentials.from_json_keyfile_name(G_SERCRET, G_SCOPES)
service = discovery.build('sheets', 'v4', credentials=credentials)
spreadsheet_id = G_SHEET_ID
rangeName = keyword + '!A1:A'
result = service.spreadsheets().values().get(spreadsheetId=spreadsheet_id, range=rangeName).execute()
values = result.get('values', [])
curRowsLen =len(values)
if not values:
print('sheet has no data')
batch_update_values_request_body = {
"valueInputOption": "USER_ENTERED",
"data": [
{
"range": keyword + "!A" + str(curRowsLen+1) + ":E" + str(curRowsLen + len(rows)),
"majorDimension": "ROWS",
"values": rows
}
]
}
#pprint(batch_update_values_request_body)
request = service.spreadsheets().values().batchUpdate(spreadsheetId=spreadsheet_id,body=batch_update_values_request_body)
response = request.execute()
# TODO: Change code below to process the `response` dict:
pprint(response)
return response
def get_sheet_data_from_url(sheet_url):
"get the data for the sheet URL as CSV"
#TODO: copy code name version? maybe just toss the name version
import gspread
from oauth2client.service_account import ServiceAccountCredentials
# see 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
scope = ['https://spreadsheets.google.com/feeds']
# follow the instructions in the blog above carefully to get this.
creds = ServiceAccountCredentials.from_json_keyfile_name('/home/matz/DCSFetchMarks-3cf40810a20f.json', scope)
client = gspread.authorize(creds)
# Find the workbook by URL and open the first sheet
try:
work_book = client.open_by_url(sheet_url)
assert work_book
sheet = work_book.sheet1
assert sheet
csv_file_data = sheet.export(format='csv')
assert csv_file_data
return csv_file_data
except:
import traceback,sys
print("failed to open sheet", sheet_url)
traceback.print_exc(file=sys.stdout)
exit(2)
def lambda_handler(event, context):
global config
# Load the configuration (s3_bucket, s3_key, sheet_id)
with open('config.json') as data_file:
config = json.load(data_file)
# Connect to Google Sheets and open the sheet
# Ensure the sheet is shared with the service
# account email address ([email protected])
scopes = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scopes=scopes)
gc = gspread.authorize(credentials)
sheet = gc.open_by_key(config['sheet_id']).worksheet(config['worksheet_name'])
# Get the values
gval = sheet.range("A1:B" + str(sheet.row_count))
data = {}
# Get every key => value for A => B (If A is not blank)
for i in range(sheet.row_count):
if i % 2 == 0 and gval[i].value != '':
data[gval[i].value] = str(gval[i + 1].value)
# Encode into JSON
jsonstr = json.dumps(data)
# Print or upload to S3
if debug:
print jsonstr
else:
return upload_to_s3(jsonstr)
return
def __init__(self, credential_path, spreadsheet_name):
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name(credential_path, scope)
self.gc = gspread.authorize(credentials)
logging.info('Sheet service client authorized, credential path: %s' % credential_path)
self.spreadsheet = self.gc.open(spreadsheet_name)
pass
def go(startdate, enddate):
credentials = ServiceAccountCredentials.from_json_keyfile_name('daily_goals.json', ['https://spreadsheets.google.com/feeds'])
gc = gspread.authorize(credentials)
wks = gc.open("Daily Goals").worksheet("Sheet1")
rows = wks.get_all_values()
startdate = datetime.datetime.strptime(startdate, '%Y-%m-%d')
enddate = datetime.datetime.strptime(enddate, '%Y-%m-%d')
results = {}
results['rows'] = []
def str2num(val):
if not val:
return 0
else:
return float(val)
for row in rows:
date_str, wateroz, vitamins, scripture_study, exercised, pullups, divebombpushups, calories, sevenminuteworkout, weight, sat_fat_grams, sol_fiber_grams, hours_slept, servings_fruit_veg = row
try:
dateobj = datetime.datetime.strptime(date_str.split(' ')[0], '%Y-%m-%d')
except ValueError:
continue
dateobj = dateobj - datetime.timedelta(days=1)
if (startdate <= dateobj <= enddate) and (dateobj.weekday() != 6):
results['rows'].append({'date_str': dateobj.date().strftime('%Y-%m-%d'),
'physical_activity_description': 'walking',
'activity_minutes': exercised,
'water_5_or_more_cups': (str2num(wateroz)/8) >= 5,
'fruit_veg_4_or_more_servings': str2num(servings_fruit_veg) >= 4,
'sleep_7_or_more_hours': str2num(hours_slept) >= 7})
print(json.dumps(results))
scopes = ['https://www.googleapis.com/auth/analytics.readonly']
json_data = {
"type": "service_account",
"project_id": "ga-reporting-",
"private_key_id": "",
"private_key": "",
"client_email": "",
"client_id": "",
"auth_uri": "https://accounts.google.com/o/oauth2/auth",
"token_uri": "https://accounts.google.com/o/oauth2/token",
"auth_provider_x509_cert_url": "",
"client_x509_cert_url": ""
}
credentials = ServiceAccountCredentials._from_parsed_json_keyfile(
keyfile_dict=json_data, scopes=scopes)
class SampledDataError(Exception):
pass
date = (datetime.datetime.now() -
datetime.timedelta(days=1)).strftime('20%y-%m-%d')
date_ranges = [(date, date)]
def main(argv, metrics, dimensions, sort, profileId, profileName, writer,
logger):
service, flags = sample_tools.init(
argv,
import datetime
import requests, gspread, string
from oauth2client.service_account import ServiceAccountCredentials
import readKeysLib
# CE SCRIPT RECOPIE LES TARGETS EXPORTEES PAR YATA AU FORMAT .json DANS la spreadsheet TornStats (sheet Targets)
# Ce fichier targets.json doit être placé dans le dossier ..../torn/files !
# APIKeys and sheetKeys are saved in files in an external repertory see the module readKeysLib
APIKey_dict, sheetKey_dict = readKeysLib.getDicts()
repertory = sheetKey_dict['rep']
# Get authorization for gspread and open worksheet
scope = ['https://spreadsheets.google.com/feeds']
json_keyfile = repertory + sheetKey_dict['jsonKey']
credentials = ServiceAccountCredentials.from_json_keyfile_name(
json_keyfile, scope)
gc = gspread.authorize(credentials)
sheetKey = sheetKey_dict['TornStats']
print('-> opening sheet')
ws = gc.open_by_key(sheetKey).worksheet('Targets')
print('-> sheet opened')
# ws.update_cell(10,1,currentDateGspread)
# ws.update_cell(11,1,currentDateGspread+0.5)
# previousDateGspreadStr = ws.cell(12,1).value
# Lecture du fichier
targets_dict = readKeysLib.getYATAtargets()
print(f'-> {len(targets_dict)} targets found')
print(targets_dict)
#print(f'total_ws : {total_ws:.2f}, model: {total_ws_model:.2f}, delta: {delta:.2f} %')
search(textbox, 'Отметки:', 'Отметки:')
search(textbox, 'Отсутствующие:', 'Отсутствующие:')
search(textbox, 'Другое:', 'Другое:')
search(textbox, 'Передан', 'Передан')
search(textbox, 'через:', 'через:')
search(textbox, 'в:', 'в:')
search(textbox, 'Кабинет:', 'Кабинет:')
table = Table(root, headings=('Класс', 'Кабинет'), rows=out)
table.pack(expand=tk.YES, fill=tk.BOTH)
scope = [
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
credentials = ServiceAccountCredentials.from_json_keyfile_name(
'C:/key.json', scope)
gc = gspread.authorize(credentials)
wks = gc.open_by_key('1PmA0BSTfaHZIBoDC7G2kmed5nEheBIo4NNdBp1LJ410').sheet1
#add_one()
#get_all()
root = tk.Tk()
root.title("Где журнал? - Сервер")
textbox = Text(root)
logb = Button(root, text="Перемещение журнала")
textbox.pack()
logb.pack()
logb.bind('<Button-1>', get_all)
textbox.tag_config('False', background='red')
textbox.tag_config('True', background='blue')
textbox.tag_config('Имя:', background='yellow')
class TaiwanBotSheet:
scope = [
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
# creds = ServiceAccountCredentials.from_json_keyfile_name('creds.json', scope)
service_account_info_dict = json.loads(CONFIG.GOOGLE_SERVICE_ACCOUNT,
strict=False)
creds = ServiceAccountCredentials.from_json_keyfile_dict(
service_account_info_dict, scope)
client = gspread.authorize(creds)
context = SpreadsheetContext.GENERAL
def __init__(self, context=SpreadsheetContext.GENERAL):
_logger.info('Initiating TaiwanBotSheet')
self.context = context
def get_questions_answers(self, context=None):
if context is None:
context = self.context
spreadsheet = self.client.open(SPREADSHEET_FAQ_FILE)
sheet = spreadsheet.worksheet(CONTEXTS[context]["sheet"])
# Each question can potentially be multiple lines
question_multiples = list(
map(str.splitlines, list(map(str.strip,
sheet.col_values(1)[1:]))))
non_duplicated_answers = list(map(str.strip, sheet.col_values(2)[1:]))
# The end result is duplicate answers for each row in the spreadsheet
# that has multiple questions
questions = []
answers = []
for index in range(len(question_multiples)):
question_multiple = question_multiples[index]
answer = non_duplicated_answers[index]
for question in question_multiple:
questions.append(question)
answers.append(answer)
return [questions, answers]
def log_answers(self, user_question, similar_question, answer, score,
state):
sheet = self.client.open(SPREADSHEET_LOG_FILE).worksheet(
CONTEXTS[self.context]["sheet"])
next_row = len(sheet.get_all_values()) + 1
sheet.update('A' + str(next_row),
datetime.now().strftime("%d/%m/%Y %H:%M:%S"))
sheet.update('B' + str(next_row), user_question)
sheet.update('C' + str(next_row), similar_question)
sheet.update('D' + str(next_row), answer)
sheet.update('E' + str(next_row), score)
sheet.update('F' + str(next_row), state)
def get_context(self):
return CONTEXTS[self.context]
def set_context(self, context):
if context in CONTEXTS:
self.context = context
else:
_logger.error(
"This context type does not exist. Setting the GENERAL context instead..."
)
self.context = SpreadsheetContext.GENERAL
#------------------
import mongo_classifier # this step is necessary to update the classifier data
load_file = 'trained_classifier.sav'
loaded_classifier = pickle.load(open(load_file, "rb"))
level = 1 # 1: H/nH 2: H=2/ N=1/ F=3
eval_sheet = "Check evaluation_test"
head_ind = 8
scope = [
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
creds = ServiceAccountCredentials.from_json_keyfile_name(
'C:/Keys/mongoDB_secret.json', scope)
client = gspread.authorize(creds)
sheet = client.open("Checks list").sheet1
sheet_g = Spread(eval_sheet)
sheet_g.open_sheet("Sheet1", create=False)
sheet_g.sheet.resize(rows=head_ind)
headers = sheet.row_values(head_ind)
all_values = sheet.get_all_values()
check_SQL = pd.DataFrame(all_values, columns=headers)
all_checks = list(check_SQL.loc[range(head_ind, len(check_SQL)),
'description'].unique())
#====== validation loop
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd
from time import sleep
# 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'
]
creds = ServiceAccountCredentials.from_json_keyfile_name(
'Skills-Comparison.json', scope)
client = gspread.authorize(creds)
# Find a workbook by name and open the first sheet
sheet = client.open("Skills-Comparison-Spreadsheet").sheet1
statNames = sheet.col_values(1)[1:]
names = sheet.row_values(1)[1:]
stats = []
# Gets stats from each row
i = 2
while i <= 55:
stats.append(sheet.row_values(i)[1:])
# Sleeps to stop google from s******g itself over to many queries per second
sleep(.15)
i += 1
# Make sure you use the right name here.
sheet = client.open("Skills-Comparison-Spreadsheet").sheet1
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from pprint import pprint
scope = [
"https://spreadsheets.google.com/feeds",
'https://www.googleapis.com/auth/spreadsheets',
"https://www.googleapis.com/auth/drive.file",
"https://www.googleapis.com/auth/drive"
]
creds = ServiceAccountCredentials.from_json_keyfile_name(
"credentials.json", scope)
client = gspread.authorize(creds)
sheet = client.open("TrackRev").sheet1 # Open the spreadhseet
sheet.clear() # Clear existing data
# Helper function for writing data based on header flag or not
def writeData(theData, headerFlag):
if (headerFlag == 0):
sheet.append_row(theData)
else:
sheet.append_row(['.'] + theData)
def __init__(self):
self.key = '/Users/enrique/Desktop/key.json'
self.scopes = 'https://www.googleapis.com/auth/androidpublisher'
self.credentials = credentials = ServiceAccountCredentials.from_json_keyfile_name(self.key, scopes=self.scopes)
# import locale
from werkzeug.middleware.proxy_fix import ProxyFix
from linebot import (
LineBotApi, WebhookHandler
)
from linebot.exceptions import (
InvalidSignatureError,
LineBotApiError)
app = Flask(__name__)
app.wsgi_app = ProxyFix(app.wsgi_app, x_for=1, x_host=1, x_proto=1)
# spreadsheet
scope = ['https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('dumbbot-8fad35afb0c6.json', scope)
client = gspread.authorize(creds)
mainsheet = client.open('SGVN')
sheet = client.open('SGVN').sheet1
tmpordersheet = mainsheet.worksheet('tmporder')
tmpuserinfo = mainsheet.worksheet(('tmpuserinfo'))
ordersheet = mainsheet.worksheet('orders')
userinfosheet = mainsheet.worksheet('userinfo')
reportReceiver = mainsheet.worksheet('reportReceiver')
# urllib3 -
http = urllib3.PoolManager()
# token
# line_bot_api = LineBotApi('bOiXla2lbcGsYnZkXnhxOAkyAzuGTSDrGVZGF/hrMjlws0+DhIoFq8i9f3xjR8DHmR6KqVpU/UW+SR8yAKDyt/PEecZg5jU9AjAIPQBvYpZRrQPrzWVQCmd10l8q4E0q17mtskg/bljPsPxPFSUj9QdB04t89/1O/w1cDnyilFU=')
line_bot_api = LineBotApi('cX51Ve+hutrgp3yj8vU0+HzTgfDT3v5vJm8Z8ZswRLI09+tqBp3KzUA+wXyOiR3GovF0UEd5yip6Jfjw5gdUPv4jYWIjsvJNxifxwuM/S9LhVSbZcZCW7lREgwXT3/Zt9KNENifbpWQ8qCKRW+txiAdB04t89/1O/w1cDnyilFU=')
import gspread
from oauth2client.service_account import ServiceAccountCredentials
# Configurando a Integração
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('Control/static/credential/entregatecnica.json', scope)
gc = gspread.authorize(credentials)
wks = gc.open_by_key('1yIwqAc7yEedIvqYRaFrjv8p4IcJb4OVez4xOPOubqxg')
worksheet = wks.get_worksheet(0)
# Pega todos os dados da tabela:
def Todos():
list_of_lists = worksheet.get_all_values()
return list_of_lists
from django.shortcuts import render
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from django.contrib.auth.models import User
from ast import literal_eval
from .models import Dues
import os
# Create your views here.
scope = [
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
credentials = ServiceAccountCredentials.from_json_keyfile_name(
os.environ.get('CLIENT_SECRET'), scope)
# os.environ['CLIENT_SECRET']
gc = gspread.authorize(credentials)
# wks = gc.open((os.environ.get('WKS_NAME'))).sheet1
wks = gc.open((os.environ.get('WKS_NAME'))).get_worksheet(0)
sheet1 = wks.get_all_records()
# wks.find(user_email) find a string
# values_list = worksheet.col_values(1) all values from column 1
EmailColumn = wks.col_values(1)
def index(request):
email = request.user.email
announcement = Dues.objects.all()
def auth_gss_client(path, scopes):
credentials = ServiceAccountCredentials.from_json_keyfile_name(
path, scopes)
return gspread.authorize(credentials)
import gspread
from oauth2client.service_account import ServiceAccountCredentials
# Open a worksheet from spreadsheet with one shot
#(temp) wks = gc.open("Where is the money Lebowski?").sheet1
#(temp) wks.update_acell('B2', "it's down there somewhere, let me take another look.")
# Fetch a cell range
#(temp) cell_list = wks.range('A1:B7')
# 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']
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)
gc = gspread.authorize(creds)
# Find a workbook by name and open the first sheet
# Make sure you use the right name here.
#(temp) sheet = client.open("Survey_Project_Responses").sheet1
sh = gc.create('This is just a test')
# But that new spreadsheet will be visible only to your script's account.
# To be able to access newly created spreadsheet you *must* share it
# with your email. Which brings us to sharing a spreadsheet
sh.share('*****@*****.**', perm_type='user', role='writer')
port = skt.bind_to_random_port("tcp://*")
input="tcp://%s:%d" % (fqdn, port)
ctrl = os.fdopen(3, 'w')
ctrl.write("INIT\n")
ctrl.write("INPUT:input:%s\n" % input)
ctrl.write("RUNNING\n")
ctrl.flush()
############################################################################
# Creds
scopes = ['https://www.googleapis.com/auth/bigquery']
credentials = ServiceAccountCredentials.from_json_keyfile_name(private,
scopes=scopes)
if project == None:
project = json.loads(open(private,'r').read())['project_id']
http = Http()
http_auth = credentials.authorize(http)
service = build('bigquery', 'v2', http=http_auth)
tables = service.tables()
tabledata = service.tabledata()
jobs = service.jobs()
############################################################################
# Create table if not exists.
def update_sheet(source_link, sheet_name):
today = datetime.datetime.now()
yesterday_full_Date = (today - datetime.timedelta(1)).strftime("%d %B, %Y")
today_time_format = today.strftime("%b/%d/%Y %H:%M")
yesterday_dayno = (today - datetime.timedelta(1)).strftime("%d")
print("Today is ", today_time_format)
scrapper_end_time = (today - datetime.timedelta(1)).strftime("%b/%d/%Y 21:00")
scrapper_start_time = (today - datetime.timedelta(2)).strftime("%b/%d/%Y 21:00")
#
# print("Start Time : ", scrapper_start_time)
# print("End Time : ", scrapper_end_time)
print()
print("----- Start -----")
print()
# Test
# todays_month = 'Sep'
# todays_day = '15'
# Test
scope = ["https://spreadsheets.google.com/feeds", 'https://www.googleapis.com/auth/spreadsheets',
"https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("CodeforcesAutoTracker-b2030a7afa6c.json", scope);
client = gspread.authorize(creds)
sheet = client.open("Codeforces Auto Tracker - Akif Islam").worksheet(sheet_name)
data = sheet.get_all_records()
# pprint(data)
date_column = sheet.col_values(1)
no_of_total_submission_column = sheet.col_values(2)
no_of_total_accepted_column = sheet.col_values(3)
source = requests.get(source_link).text
soup = BeautifulSoup(source, "lxml").find('table', class_="status-frame-datatable")
submission_time = []
# 1. Collecting all dates from 50 submission of First Page of Codeforces Submission
for data in soup.findAll('span', class_="format-time"):
datetime_object = datetime.datetime.strptime(data.text, "%b/%d/%Y %H:%M")
submission_time.append(datetime_object.strftime("%b/%d/%Y %H:%M"))
print(" ALL OK !")
print()
# Execution
submission_count = int(0)
total_accepted = []
accepted_count = int(0)
accpeted_nonduplicate_set = []
# Total Accepted Count from 50s :
for data in soup.findAll('span', class_="submissionVerdictWrapper"):
total_accepted.append(data.text)
# print(total_accepted)
# print("Found: ",len(total_accepted))
# print("Found : ",len(submission_time))
# print(total_accepted)
#Total Submission Count
for i in range(0,len(submission_time),1):
if submission_time[i]>scrapper_start_time and submission_time[i]<scrapper_end_time:
submission_count += 1
if(total_accepted[i]== "Accepted" or total_accepted[i].__contains__("Pretest Passed")):
str = get_problemlist(source_link)[i] + " Accepted"
accpeted_nonduplicate_set.append(str)
# Total Submission Count
accpeted_nonduplicate_set = set(accpeted_nonduplicate_set)
# print("Accepted List : ",accpeted_nonduplicate_set)
accepted_count = len(accpeted_nonduplicate_set)
print("Total Submission : ", submission_count)
print("Total Accepted : ", accepted_count)
insert_list = [yesterday_full_Date, submission_count, accepted_count]
# print(insert_list)
previous_date = sheet.cell(len(date_column), 1).value[0:2]
if previous_date != yesterday_dayno:
sheet.insert_row(insert_list, (len(date_column) + 1))
else:
print("Duplicate Date Found ! ")
print(sheet_name + " data checked successfully !")
print("----- Finished !-----")
print()
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from json import load
def getTokens():
return load(open('res/TOKENS.json', 'r'))
def slack_tokens():
return load(open('res/slackTokens.json', 'r'))
scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name(
'res/credentials.json', scope) # hidden, it's a secret
client = gspread.authorize(creds)
sheet = client.open_by_url(getTokens()["sheet_url"]).worksheet(
'Sheet1') # till here all gspread API stuff
def updateSheet(updateList):
"""
I must say this, append_row saved about 40 lines of code, and is more efficient.
Should have found about this when Sauron was made. Meh, better late than never.
"""
sheet.append_row(updateList)
# -*- coding: utf-8 -*-
from __future__ import unicode_literals
# @Author: chengaoxing
# @Date: 2017-08-14 10:06:29
# @Last Modified by: chengaoxing
# @Last Modified time: 2017-08-14 11:36:24
# Import the Earth Engine Python Packages
import ee
from oauth2client.service_account import ServiceAccountCredentials
client_email = '*****@*****.**'
scopes = ['https://www.googleapis.com/auth/sqlservice.admin']
credentials = ServiceAccountCredentials.from_p12_keyfile(
client_email, '/Users/chengaoxing/chen-pku-app-dbdcb868ef05.p12', scopes=scopes)
# import ee.mapclient
# Initialize the Earth Engine object, using the authentication credentials.
ee.Initialize()
# Print the information for an image asset.
image = ee.Image('srtm90_v4')
path = image.getDownloadURL()
# create the vizualization parameters
# viz = {'min':0.0, 'max':4000, 'palette':"000000,0000FF,FDFF92,FF2700,FF00E7"};
# # display the map
# ee.mapclient.addToMap(image,viz, "mymap")
class GSdata():
scope = ["https://spreadsheets.google.com/feeds",
'https://www.googleapis.com/auth/spreadsheets',
"https://www.googleapis.com/auth/drive.file",
"https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("GoodsManager.json", scope)
def __init__(self,product_name = 'Sheet1'):
self.ManagerName_cell = 'C4'
self.place_cell = 'C6'
self.product_name_cell = 'C8'
self.tax_indentity_cell = 'F5'
self.Datetime = datetime.now().strftime("%Y-%m-%d")
self.gc = gspread.authorize(GSdata.creds)
self.sheet = self.gc.open('GOODS2')
### loop to find desire worksheet
self.worksheet = self.sheet.worksheets()
self.product_name = product_name
if self.__Check_existing():
Feedback = 'ไม่มี Product {} อยู่ขณะนี้ ทำการสร้างตารางใหม่ ?(y/n)'.format(self.product_name)
Input = input(Feedback)
if Input == 'y':
self.create_stock()
else :
print('Good Luck')
## check if product exist or not
def __Check_existing(self):
for i in self.worksheet:
print(i.title)
if i.title == self.product_name:
self.worksheet = self.sheet.worksheet(i.title)
return False
return True
## create new worksheet
def create_stock(self,Manager_name = 'Pybott',address_name= 'bangkok',tax_iden= '10010'):
self.sheet.worksheet('Sheet1').duplicate(new_sheet_name = self.product_name)
## set current worksheet
self.worksheet = self.sheet.worksheet(self.product_name)
self.worksheet.update_acell(self.ManagerName_cell,Manager_name)
self.worksheet.update_acell(self.place_cell,address_name)
self.worksheet.update_acell(self.product_name_cell,self.product_name)
self.worksheet.update_acell(self.tax_indentity_cell,tax_iden)
## add new incoming data
def add_product_data(self,number_of_item ,product_number , method = 'รับ',note = None):
values_list = self.worksheet.col_values(2)
current_row = len(values_list) + 1
current_row_num = 'B'+str(current_row)
self.worksheet.update_acell(current_row_num,product_number)
current_row_date = 'C'+str(current_row)
self.worksheet.update_acell(current_row_date,self.Datetime)
if method == 'รับ':
current_row_receive = 'D'+str(current_row)
self.worksheet.update_acell(current_row_receive,number_of_item)
else :
current_row_dis = 'E'+str(current_row)
self.worksheet.update_acell(current_row_dis,number_of_item)
if note is not None:
current_row_note = 'G'+str(current_row)
self.worksheet.update_acell(current_row_note,note)
return 'You have updated your products amount'
# ลบสินค้า
def delete_product_data(self,product_number):
cell = self.worksheet.find(str(product_number))
current_row_num = 'B'+str(cell.row)
self.worksheet.update_acell(current_row_num,'')
current_row_date = 'C'+str(cell.row)
self.worksheet.update_acell(current_row_date,'')
current_row_receive = 'D'+str(cell.row)
self.worksheet.update_acell(current_row_receive,'')
current_row_dis = 'E'+str(cell.row)
self.worksheet.update_acell(current_row_dis,'')
current_row_note = 'G'+str(cell.row)
self.worksheet.update_acell(current_row_note,'')
return 'You have delete your products amount'
def run(job=None, logger=None, **kwargs):
"""
"""
db_name = DB_NAME
instance_name = db_name
environment = Environment.objects.get(id=ENVIRONMENT)
rh = environment.resource_handler.cast()
assert isinstance(rh, GCPHandler)
project = environment.gcp_project
region = GCP_REGION
set_progress("REGION: %s" % GCP_REGION)
try:
CustomField.objects.get_or_create(
name="gcp_sql_rh_id",
label="Google RH ID",
type="STR",
description="Used by the Google SQL blueprint",
show_as_attribute=True,
)
except IntegrityError:
# IntegrityError: (1062, "Duplicate entry 'google_rh_id' for key 'name'")
pass
try:
CustomField.objects.get_or_create(
name="gcp_sql_instance_name",
label="Google instance identifier",
type="STR",
description="Used by the Google Cloud SQL blueprint",
show_as_attribute=True,
)
except IntegrityError:
# IntegrityError: (1062, "Duplicate entry 'db_identifier' for key 'name'")
pass
try:
CustomField.objects.get_or_create(
name="gcp_sql_project",
label="Google project",
type="STR",
description="Used by the Google Cloud SQL blueprint",
show_as_attribute=True,
)
except IntegrityError:
# IntegrityError: (1062, "Duplicate entry 'db_identifier' for key 'name'")
pass
resource = kwargs.get("resource")
resource.name = "Google SQL - " + instance_name
resource.gcp_sql_instance_name = instance_name
# Store the resource handler's ID on this resource so the teardown action
# knows which credentials to use.
resource.gcp_sql_rh_id = rh.id
resource.gcp_sql_project = project
resource.save()
try:
account_info = json.loads(
rh.gcp_projects.get(id=project).service_account_info)
except Exception:
account_info = json.loads(
rh.gcp_projects.get(id=project).service_account_key)
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
account_info)
job.set_progress(
"Connecting to Google Cloud through service account email {}".format(
account_info["client_email"]))
set_progress("RH: %s" % rh)
service_name = "sqladmin"
version = "v1beta4"
client = build(service_name, version, credentials=credentials)
set_progress("Connection established")
try:
inst_data = client.instances().list(project=project).execute()
if "items" in inst_data:
instance_names = [inst["name"] for inst in inst_data["items"]]
if instance_name in instance_names:
return (
"ERROR",
'Server instance "%s" already exists' % instance_name,
"",
)
except HttpError as e:
client_username = account_info["client_email"].split("@")[0]
return (
"ERROR",
"Server instance {instance_name} could not be created ({reason}), make sure that this ResourceHandler's service account ({service_account_name}) is given the Cloud SQL Admin Permission"
.format(
instance_name=instance_name,
reason=str(e),
service_account_name=client_username,
),
e,
)
set_progress("\nCreating instance...")
body = {
"kind": "sql#instance",
"name": instance_name,
"project": project,
"region": region,
"databaseVersion": DB_VERSION,
"settings": {
"tier": "db-n1-standard-1"
},
}
result = client.instances().insert(project=project, body=body).execute()
# Wait the server instance to be created:
while True:
inst_data = client.instances().list(project=project).execute()
status = None
for inst in inst_data["items"]:
if inst["name"] == instance_name:
status = inst["state"]
break
set_progress("Status of the server instance is: %s" % status)
if status == "RUNNABLE":
break
time.sleep(2)
set_progress("\nNow attempting to create a new database...")
body = {
"kind": "sql#database",
"name": db_name,
"project": project,
"instance": instance_name,
}
result = (client.databases().insert(project=project,
instance=instance_name,
body=body).execute())
assert result["status"] == "DONE"
set_progress("Database %s is now available on instance: %s" %
(db_name, instance_name))
def load_credentials():
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name(
'files/service-account-key.json', scope)
return credentials
def load(self):
"""
Load table data from a Google Spreadsheet.
This method consider :py:attr:`.source` as a path to the
credential JSON file to access Google Sheets API.
The method automatically search the header row start from
:py:attr:`.start_row`. The condition of the header row is that
all of the columns have value (except empty columns).
:return:
Loaded table data. Return one |TableData| for each sheet in
the workbook. The table name for data will be determined by
:py:meth:`~.GoogleSheetsTableLoader.make_table_name`.
:rtype: iterator of |TableData|
:raises pytablereader.InvalidDataError:
If the header row is not found.
:raises pytablereader.OpenError:
If the spread sheet not found.
"""
import gspread
from oauth2client.service_account import ServiceAccountCredentials
self._validate_table_name()
self._validate_title()
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name(
self.source, scope)
gc = gspread.authorize(credentials)
try:
for worksheet in gc.open(self.title).worksheets():
self._worksheet = worksheet
self.__all_values = [row for row in worksheet.get_all_values()]
if self._is_empty_sheet():
continue
try:
self.__strip_empty_col()
except ValueError:
continue
value_matrix = self.__all_values[self._get_start_row_idx():]
try:
header_list = value_matrix[0]
record_list = value_matrix[1:]
except IndexError:
continue
self.inc_table_count()
yield TableData(self.make_table_name(),
header_list,
record_list,
quoting_flags=self.quoting_flags)
except gspread.exceptions.SpreadsheetNotFound:
raise OpenError("spreadsheet '{}' not found".format(self.title))
#log part
try:
logging.basicConfig(filename='/home/pi/Documents/logs/' + sys.argv[1] +
'.log',
format='%(asctime)s %(message)s',
datefmt='%d/%m/%Y %I:%M:%S %p',
level=logging.INFO)
except:
print "The name of the log file! pass it as an argument"
#debug / info / warning
logging.info('New beginning')
#gspread part
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name(
'/home/pi/techlab-tag-nfc-b3f2a2929d98.json', scope)
gc = gspread.authorize(credentials)
logging.info(gc)
sh = gc.open_by_url(
'https://docs.google.com/spreadsheets/d/1KWxCi7tny8uxo4TmzjNnVuNj5eGRVngwFD2gxIX5qfw/edit?usp=sharing'
)
worksheet = sh.worksheet("soci")
worksheet_log = sh.worksheet("log_laser")
cellTag = ""
Cr = 0
Sk = 0
#serial part
ser = serial.Serial('/dev/ttyAMA0', 115200, timeout=1)
logging.info(ser)
def initialize_analyticsreporting() -> Resource:
credentials = ServiceAccountCredentials.from_json_keyfile_name(
os.environ['KEY_FILE_LOCATION'],
scopes='https://www.googleapis.com/auth/analytics.readonly')
analytics = build('analyticsreporting', 'v4', credentials=credentials)
return analytics
import time
import gspread
import matplotlib.pyplot as plt
from oauth2client.service_account import ServiceAccountCredentials
scope = [
'https://www.googleapis.com/auth/devstorage.read_write',
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
credentials = ServiceAccountCredentials.from_json_keyfile_name(
'Google Sheet-80a12669b8a0.json', scope)
if __name__ == '__main__':
while True:
try:
auth = gspread.authorize(credentials)
google_sheet = auth.open(
"WorldCup2015_IndiaIndividualScores").sheet1
data = google_sheet.get_all_values()
matches = []
scores = []
for match in data:
bat_first = match[1].split()[1]
def __init__(self, spreadsheet, worksheet):
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
self.credentials = ServiceAccountCredentials.from_json_keyfile_name('google_client_secret.json', scope)
self.client = gspread.authorize(self.credentials)
self.spreadsheet = self.client.open(spreadsheet)
self.worksheet = self.spreadsheet.worksheet(worksheet)
bat_usd105 = "Ticker (BAT/USD)"
bat = (myapi.ticker("BAT/USD")['last'])
#----------LTC/USD-----------#
ltc_usd105 = "Ticker (LTC/USD)"
lt1 = (myapi.ticker("LTC/USD")['last'])
#----------BCH/USD-----------#
bch_usd105 = "Ticker (BCH/USD)"
bc1 = (myapi.ticker("BCH/USD")['last'])
#print ("CEX:" + " "+ G + btc_usd659, O + last_price, G + etc_usd103, O + et1, G + xlm_usd315, O + xl1, G + dash_usd886, O + da1, G + xrp_usd237, O + xr1, G + ltc_usd105, O + lt1)
##########################################################################################################>
### Start of the Google Sheets Api Script | Documentation https://gspread.readthedocs.io/en/latest/
## https://www.techwithtim.net/tutorials/google-sheets-python-api-tutorial/ | https://www.youtube.com/watch?v=cnPlKLEGR7E
scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("/home/kali/Desktop/Projects/everything_python/Cryptozoom/creds.json", scope)
client = gspread.authorize(creds)
#------------------------------------------------------------------ Google Sheets API Primer --------------------------------------------------------------------------------------------#
# How to use the sheets api will be placed here
#----------------------------------------------------------------------------------------------------------------------------------------------------------------------#
## Get a specific row, column, and the value of a specific cell examples
#----------------------------------------------------------------------------------------------------------------------------------------------------------------------#
#row23 = sheet.row_values(23)
#colA = sheet.col_values(1)
#----------------------------------------------------------------------------------------------------------------------------------------------------------------------#
## Insert the row and the list as a row at index 4 Example:
#----------------------------------------------------------------------------------------------------------------------------------------------------------------------#
#--insertRow = ["hello", 5, "red", "blue"]
#copy cells to the output_sheet
new_cell_list= output.range(new_range)
i=0
for cell in cell_list:
if (i<max_col):
new_cell_list[i].value=cell_list[i].value
i+=1
output.update_cells(new_cell_list)
except gspread.exceptions.HTTPError:
print " output.update_cells resulted in error try again"
copy_row(input,output,input_row,output_row)
# main
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name('GetBGGInfo-a1f867326e07.json', scope)
gc = gspread.authorize(credentials)
sh = gc.open("Essen 2016")
wks_input = sh.sheet1
sheetName="data_from_BGG"
try:
sh.add_worksheet(title=sheetName,rows="100", cols="25")
except gspread.exceptions.RequestError:
print sheetName, " sheet already exist"
wks_output=sh.worksheet(sheetName)
print "output worksheet title=",wks_output.title
Fields=["Name","Id","Publisher","Designers","Year","Artists", "Ranks", "Min_players",
"Max_players","Min_age","Mechanics","Plying_time","Families",
"Rating_average","Alternative_names","Expands","Price","Booth"]
col=1
for field in Fields:
'''
Ref: https://console.developers.google.com/apis/credentials?project=sheets-test-219809
+ Download credentials and store in same folder
+ Open credentials and get client_email
+ Share it with the spreadsheet file you want to access
'''
# which APIs are used
scope = [
'http://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
# keep secret
credentials = ServiceAccountCredentials.from_json_keyfile_name(
'Sheets Test-1b7944f419c8.json', scope)
# authorize
gc = gspread.authorize(credentials)
# open ss
worksheet = gc.open('test')
wks_1 = worksheet.get_worksheet(0)
wks_2 = worksheet.get_worksheet(1)
# read, write, update, append, delete
def read_data():
data = wks_2.get_all_records()
print(data)
def get_credentials(scope):
service_account_json = os.getenv(
'GOOGLE_SERVICE_ACCOUNT') or SERVICE_ACCOUNT_PATH.read_text()
service_account = json.loads(service_account_json)
return ServiceAccountCredentials.from_json_keyfile_dict(
service_account, scope)
def __init__(self):
self.scope = ["https://spreadsheets.google.com/feeds",'https://www.googleapis.com/auth/spreadsheets',"https://www.googleapis.com/auth/drive.file","https://www.googleapis.com/auth/drive"]
self.creds = ServiceAccountCredentials.from_json_keyfile_name("creds.json", self.scope)
self.client = gspread.authorize(self.creds)
self.spreadsheet = self.client.open("Acoes")
