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 get_gdrive_client(credentials_key):
""" Get gspread client
Parameters
----------
credentials_key : str
either a path to a json file containing 'project_id' and 'read_key'
or a service_name for keyring entries containing 'project_id' and
'read_key'
Returns
-------
gspread client
"""
if credentials_key.endswith('.json'):
credentionals_json = open(credentials_key, 'r').read()
else:
credentionals_json = keyring.get_password(credentials_key,
'credentionals_json')
scope = ['https://spreadsheets.google.com/feeds']
credentials = json.loads(credentionals_json)
credentials = ServiceAccountCredentials.from_json_keyfile_dict(credentials,
scope)
gc = gspread.authorize(credentials)
return gc
def main(path=None):
scope = ['https://spreadsheets.google.com/feeds']
pk = "-----BEGIN PRIVATE KEY-----{key}-----END PRIVATE KEY-----\n".format(key=environ['PK'])
key_dict = {
"type": "service_account",
"project_id": "devposthackathontweets",
"private_key_id": "1d02a04353966c7d0f48be5dc6780c20177f96bd",
"private_key": pk,
"client_email": "*****@*****.**",
"client_id": "105326323772464143676",
"auth_uri": "https://accounts.google.com/o/oauth2/auth",
"token_uri": "https://accounts.google.com/o/oauth2/token",
"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
"client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/hackathon-manager%40devposthackathontweets.iam.gserviceaccount.com"
}
creds = ServiceAccountCredentials.from_json_keyfile_dict(key_dict, scope)
client = gspread.authorize(creds)
# Find a workbook by name and open the first sheet
# Make sure you use the right name here.
sheet = client.open("Hackathon Twitter").sheet1
# Extract and print all of the values
sheets_data = sheet.get_all_records()
if path is None:
return render_template('index.html', payload=sheets_data)
else:
c = getClient(path, sheets_data)
k = getKeywords(path, sheets_data)
d = getTweets(k)
return render_template('report.html', client=c, data=d)
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 load_review_list(package_name, last_post=0):
credential = ServiceAccountCredentials.from_json_keyfile_dict(_CREDENTIAL_DICT, scopes=_SCOPES)
http_auth = credential.authorize(Http())
service = build('androidpublisher', 'v2', http=http_auth)
token = None
result = list()
latest_modified = last_post
while True:
reviews = service.reviews().list(packageName=package_name, token=token).execute()
for r in reviews["reviews"]:
if "text" in r["comments"][0]["userComment"]:
modified = int(r["comments"][0]["userComment"]["lastModified"]["seconds"])
if last_post < modified:
result.append(Review(r))
if latest_modified < modified:
latest_modified = modified
else:
return _create_result_dict(latest_modified, result)
if "tokenPagination" in reviews:
token = reviews["tokenPagination"]["nextPageToken"]
else:
break
return _create_result_dict(latest_modified, result)
def load_sheet(worksheet, g_file='CMPD'):
"""Load sheet as array of strings (drops .xls style index)
gspread allows for .xlsx export as well, which can capture style info.
:param worksheet: provide None to return a dictionary of all sheets
:param g_file:
:return:
"""
# see http://gspread.readthedocs.org/en/latest/oauth2.html
json_key = make_gspread_json()
from oauth2client.service_account import ServiceAccountCredentials
import gspread
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_dict(json_key, scope)
gc = gspread.authorize(credentials)
xsheet = gc.open(g_file)
if isinstance(worksheet, int):
wks = xsheet.get_worksheet(worksheet)
if worksheet is None:
return {x.title: np.array(x.get_all_values()) for x in xsheet.worksheets()}
else:
wks = xsheet.worksheet(worksheet)
xs_values = wks.get_all_values()
return xs_values
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 get_oauth_credentials(self):
client_credentials = json.loads(base64.b64decode(os.environ["GCS_CREDENTIALS"]))
if client_credentials["type"] == SERVICE_ACCOUNT:
creds = ServiceAccountCredentials.from_json_keyfile_dict(client_credentials)
else:
raise ImproperlyConfigured("non-service accounts are not supported")
return self.create_scoped(creds)
def get_service_account_credentials(self):
# Bug fix for https://github.com/pandas-dev/pandas/issues/12572
# We need to know that a supported version of oauth2client is installed
# Test that either of the following is installed:
# - SignedJwtAssertionCredentials from oauth2client.client
# - ServiceAccountCredentials from oauth2client.service_account
# SignedJwtAssertionCredentials is available in oauthclient < 2.0.0
# ServiceAccountCredentials is available in oauthclient >= 2.0.0
oauth2client_v1 = True
oauth2client_v2 = True
try:
from oauth2client.client import SignedJwtAssertionCredentials
except ImportError:
oauth2client_v1 = False
try:
from oauth2client.service_account import ServiceAccountCredentials
except ImportError:
oauth2client_v2 = False
if not oauth2client_v1 and not oauth2client_v2:
raise ImportError("Missing oauth2client required for BigQuery "
"service account support")
from os.path import isfile
try:
if isfile(self.private_key):
with open(self.private_key) as f:
json_key = json.loads(f.read())
else:
# ugly hack: 'private_key' field has new lines inside,
# they break json parser, but we need to preserve them
json_key = json.loads(self.private_key.replace('\n', ' '))
json_key['private_key'] = json_key['private_key'].replace(
' ', '\n')
if compat.PY3:
json_key['private_key'] = bytes(
json_key['private_key'], 'UTF-8')
if oauth2client_v1:
return SignedJwtAssertionCredentials(
json_key['client_email'],
json_key['private_key'],
self.scope,
)
else:
return ServiceAccountCredentials.from_json_keyfile_dict(
json_key,
self.scope)
except (KeyError, ValueError, TypeError, AttributeError):
raise InvalidPrivateKeyFormat(
"Private key is missing or invalid. It should be service "
"account private key JSON (file path or string contents) "
"with at least two keys: 'client_email' and 'private_key'. "
"Can be obtained from: https://console.developers.google."
"com/permissions/serviceaccounts")
def google_get_images_json_key(project, key_json):
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
key_json, scopes=['https://www.googleapis.com/auth/compute'])
compute = build('compute', 'v1', credentials=credentials)
images = compute.images().list(project=project).execute()
items = images.get('items', [])
return [(i['name'], dateutil.parser.parse(i['creationTimestamp'])) for i in items]
def google_get_images_json_key(project, key_json):
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
key_json, scopes=['https://www.googleapis.com/auth/compute'])
compute = build('compute', 'v1', credentials=credentials)
images = compute.images().list(project=project).execute()
items = images.get('items', [])
return [(i['name'], dateutil.parser.parse(i['creationTimestamp']))
for i in items]
def get_gsuite_credentials():
keys = gsuite_creds
keys_dict = json.loads(keys)
scopes = 'https://www.googleapis.com/auth/admin.directory.user'
credentials = ServiceAccountCredentials.from_json_keyfile_dict(keyfile_dict=keys_dict, scopes=scopes)
credentials = credentials.create_delegated(api_user)
return apiclient.discovery.build('admin', 'directory_v1', credentials=credentials)
def build(self):
service_key_json = base64.decodebytes(self.encoded_service_key.encode())
service_key = json.loads(service_key_json)
creds = ServiceAccountCredentials.from_json_keyfile_dict(
service_key,
self.__class__.scopes,
)
return build(self.api, self.version, credentials=creds, cache_discovery=False)
def updateSheets(row_index):
creds = ServiceAccountCredentials.from_json_keyfile_dict(
creds_dict, scopes)
client = gspread.authorize(creds)
spreadsheet = client.open_by_url(real_spread_url)
sheet = spreadsheet.worksheet(indexSheetName)
sheet.update_acell("A1", str(row_index))
today = datetime.today().strftime('%Y-%m-%d')
sheet.update_acell("B1", today)
def get_service_object(self, api_name, api_version, scopes=None):
if self.connection.password:
credentials = AccessTokenCredentials(self.connection.password,
'Airflow/1.0')
elif self.client_secrets:
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
self.client_secrets, scopes)
return build(api_name, api_version, credentials=credentials)
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_access_token():
"""Retrieve a valid access token that can be used to authorize requests.
:return: Access token.
"""
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
json.loads(settings.GOOGLE_CREDENTIALS), settings.GOOGLE_SCOPE)
access_token_info = credentials.get_access_token()
return access_token_info.access_token
def get_sheets_service(self):
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
self.credentials_json_data, scopes=SCOPES)
http = credentials.authorize(httplib2.Http(timeout=120))
return discovery.build('sheets',
'v4',
http=http,
discoveryServiceUrl=SHEETS_DISCOVERY_URL)
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 _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)
spreadsheetservice = gspread.authorize(creds)
return spreadsheetservice
def discover_resources(**kwargs):
discovered_mysqldb = []
existing_instances = set()
existing_bps = ServiceBlueprint.objects.filter(
name__contains='Google MySQL Database')
for bp in existing_bps:
for resource in bp.resource_set.filter(lifecycle='ACTIVE'):
existing_instances.add(
(resource.gcp_sql_instance_name, resource.gcp_sql_project))
for project in GCPProject.objects.filter(imported=True):
try:
account_info = json.loads(project.service_account_key)
except:
account_info = json.loads(project.service_account_info)
environment = project.environment
resource_handler = project.handler
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
account_info)
client_email = account_info["client_email"]
set_progress(
'Connecting to Google Cloud through service account email {}'.
format(client_email))
set_progress("RH: %s" % resource_handler.name)
service_name = 'sqladmin'
version = 'v1beta4'
client = build(service_name, version, credentials=credentials)
try:
instances = client.instances().list(
project=project.id).execute().get('items', None)
if instances:
for instance in instances:
set_progress(
'Found Database named {}: checking to see if the instance already exists on CB'
.format(instance['name']))
if (instance['name'],
project.name) not in existing_instances:
discovered_mysqldb.append({
'name':
instance['name'],
'gcp_sql_instance_name':
instance['name'],
'gcp_sql_rh_id':
resource_handler.id,
'gcp_sql_project':
project.name
})
except Exception as e:
set_progress(
'Could not list sql servers for {}, error message is as follows:{}'
.format(project.name, e))
return discovered_mysqldb
def google_api_credentials(scope):
"""Returns an OAuth2 credentials object for the given scope."""
if credential_info is None:
raise Exception("google service account credentials not defined in configuration")
if scope is None:
scope = ['https://www.googleapis.com/auth/drive']
if type(scope) is not list:
scope = [scope]
return ServiceAccountCredentials.from_json_keyfile_dict(credential_info, scope)
def init_service(conf):
'''コンフィグを読み込んで、GoogleAnalyticsのサービスを返す
'''
with open(conf[SERVICE_ACCOUNT_SECRET], 'r') as f:
keydict = json.load(f)
credentials = ServiceAccountCredentials.from_json_keyfile_dict(keydict, conf[SCOPES])
http = httplib2.Http()
http = credentials.authorize(http)
return build('analytics', 'v3', http=http)
def create_credentials(additional_scopes=None, **kwargs):
scope = (additional_scopes or []) + DEFAULT_SCOPES
credential_dict = build_credential_json(**kwargs)
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
credential_dict, scope
)
gc = gspread.authorize(credentials)
return gc
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 _():
if "GOOGLE_SERVICE_ACCOUNT" in os.environ:
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
json.loads(os.environ['GOOGLE_SERVICE_ACCOUNT']),
scopes=SCOPES)
else:
credentials = ServiceAccountCredentials.from_json_keyfile_name(
"avrae-google.json", scopes=SCOPES)
return gspread.authorize(credentials)
def initialize_analyticsreporting(
): #initializes Analytics Reporting API V4 service object
KEY_FILE_LOCATION = ast.literal_eval(
Analytics.KEY_FILE_LOCATION1) #convert string to dict
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
KEY_FILE_LOCATION, Analytics.SCOPES)
analytics = build('analyticsreporting', 'v4',
credentials=credentials) #Build service object
return analytics
def post_order_info(order_id, sheets_key):
scope = [
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
json.loads(GOOGLE_CREDS), scope)
gc = gspread.authorize(credentials)
sheet = gc.open_by_key(sheets_key)
orders = Order.objects.filter(order_id=order_id)
for order in orders:
worksheet = sheet.get_worksheet(0)
next_free = len(worksheet.col_values(2)) + 1
today = datetime.datetime.today().strftime('%B %d')
worksheet.update_acell('B{}'.format(next_free), today)
worksheet.update_acell('D{}'.format(next_free), order.part_number)
if order.source == 'woocommerce':
worksheet.update_acell('E{}'.format(next_free), order.total_price)
else:
worksheet.update_acell('L{}'.format(next_free), order.total_price)
if order.customer_id.state == 'BC':
worksheet.update_acell('F{}'.format(next_free), 'BC Retail')
elif order.customer_id.country == 'US' and order.source == 'woocommerce':
worksheet.update_acell('F{}'.format(next_free), 'USA')
else:
worksheet.update_acell('F{}'.format(next_free),
order.customer_id.state)
if order.source == 'bestbuy':
worksheet.update_acell('N{}'.format(next_free), 'Bestbuy')
elif order.customer_id.country == 'US':
worksheet.update_acell('N{}'.format(next_free), 'Website-US')
else:
worksheet.update_acell('N{}'.format(next_free), 'Website')
worksheet.update_acell('O{}'.format(next_free),
order.bestbuy_commission)
if order.customer_id.country == 'USA' and order.source == 'woocommerce':
worksheet.update_acell('Q{}'.format(next_free), 'UPS')
else:
worksheet.update_acell('Q{}'.format(next_free), 'Ground-Newegg')
worksheet.update_acell('T{}'.format(next_free), order.tracking_id)
worksheet.update_acell('Y{}'.format(next_free),
order.customer_id.firstname)
worksheet.update_acell('Z{}'.format(next_free),
order.customer_id.lastname)
worksheet.update_acell('AA{}'.format(next_free),
order.customer_id.phone)
worksheet.update_acell('AB{}'.format(next_free),
order.customer_id.street)
worksheet.update_acell('AC{}'.format(next_free), order.customer_id.zip)
worksheet.update_acell('AD{}'.format(next_free),
order.customer_id.city)
worksheet.update_acell('AE{}'.format(next_free),
order.customer_id.state)
worksheet.update_acell('X{}'.format(next_free), order.order_id)
worksheet.update_acell('AF{}'.format(next_free),
order.customer_id.email)
def index():
config_file = os.path.join(os.path.dirname(__file__), 'config.json')
with open(config_file, 'r') as configFile:
config_dict = json.loads(configFile.read())
r = requests.get(config_dict['target_url'],
auth=HTTPBasicAuth(config_dict['client_id'],
config_dict['client_secret']))
parser = etree.XMLParser(recover=True)
d = pq(etree.fromstring(r.text, parser))
elements = [e.attrib['title'] for e in d("label.screen-name")]
credential_file = os.path.join(os.path.dirname(__file__),
'credential.json')
with open(credential_file, 'r') as dataFile:
credential_dict = json.loads(dataFile.read())
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
credential_dict, scopes=['https://spreadsheets.google.com/feeds'])
gs_client = gspread.authorize(credentials)
gfile = gs_client.open_by_key(config_dict['doc_key'])
worksheet = gfile.sheet1
records = worksheet.get_all_values()
results = []
for i, r in enumerate(records):
if i > 0:
working = False
in_use = False
if r[3] != '':
in_use = True
for e in elements:
if r[0] in e:
working = True
result = {
'id': r[0],
'working': working,
'in_use': in_use,
'notifications': r[3],
'location': r[2]
}
results.append(result)
if in_use is False:
logging.info(u'{} is not in use, skipped'.format(r[0]))
continue
state = States.get_by_id(r[0])
if working is False:
if state is None or state.working is True:
logging.info(
u'{}: looks bad, notification task will be sent to que'
.format(r[0]))
States(id=r[0], working=False).put()
deferred.defer(send_notification, result)
else:
logging.info(
u'{}: looks bad, same status as before'.format(r[0]))
else:
logging.info(u'{}: looks good'.format(r[0]))
States(id=r[0], working=True).put()
return render_template('index.html', results=results)
def acquire_token(keyfile_dict):
"""Retrieve an OAuth 2 access token."""
if keyfile_dict is None:
creds = GoogleCredentials.get_application_default().create_scoped(
SCOPE)
else:
creds = ServiceAccountCredentials.from_json_keyfile_dict(
keyfile_dict, SCOPE)
pylog.info('Successfully acquired OAuth 2 token.')
return creds.get_access_token()
def write_to_db(row):
creds = ServiceAccountCredentials.from_json_keyfile_dict(
keyfile_dict=keyfile_dict, scopes=scopes)
#creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)
sh = client.open_by_key('1PoD8M5_fg33gdAktthKXrsyPwHMqSMASDRIX1i_zYtk')
worksheet = sh.sheet1
worksheet.insert_row(row + [str(datetime.date.today())], 1)
sleep(1) # google api 60 write requests per 60 sec
return
def gsheets_auth():
print 'auth in progress'
with open('account.json', 'r') as data_file:
json_key = json.loads(data_file.read())
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
json_key, scope)
gc = gspread.authorize(credentials)
sh = gc.open_by_key('1MI5C__3it4KgMVK6I8fZMa0Y3QQBg5lI_dcjkTqMZS4')
return sh
def create_credentials():
scopes = ['https://www.googleapis.com/auth/admin.directory.resource.calendar',
'https://www.googleapis.com/auth/calendar']
client_secret_dict = ast.literal_eval(os.environ['MR_CLIENT_SECRET_JSON'])
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
client_secret_dict, scopes=scopes).create_delegated(os.environ['MR_DELEGATED_ACCOUNT'])
http = credentials.authorize(Http())
directory = build('admin', 'directory_v1', http=http)
calendar = build('calendar', 'v3', http=http)
return directory, calendar
def gsheets_auth():
print 'auth in progress'
with open('account.json', 'r') as data_file:
json_key = json.loads(data_file.read())
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
json_key, scope)
gc = gspread.authorize(credentials)
sh = gc.open_by_key('1SKvVzU5CJrlIANTfqfFsrQH34fRnzkHPzFCsYKPIIzw')
return sh
def get_credentials(self):
"""
Build GSuite credentials
"""
if settings.GSUITE_CREDENTIALS is None:
raise ImproperlyConfigured("Missing GSuite credentials")
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
settings.GSUITE_CREDENTIALS, scopes)
return credentials.create_delegated(settings.GSUITE_DELEGATED_ACCOUNT)
def _credentials(self):
if not self.credentials_obj:
if self.credentials_dict:
self.credentials_obj = (
ServiceAccountCredentials.from_json_keyfile_dict(
self.credentials_dict))
else:
self.credentials_obj = (
GoogleCredentials.get_application_default())
return self.credentials_obj
def __init__(self, project_id, jsonkey, appname, instanceid=0):
self.project_id = project_id
self.jsonkey = jsonkey
self.appname = appname
self.instanceid = instanceid
jsonData = json.loads(jsonkey)
self.credentials = ServiceAccountCredentials.from_json_keyfile_dict(
jsonData, SCOPES)
self.endpoint = "%s/v1/projects/%s/messages:send" % (BASE_URL,
self.project_id)
def insertLog(self, data):
scope = [
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
creds = ServiceAccountCredentials.from_json_keyfile_dict(
self.client_secret, scope)
client = gspread.authorize(creds)
self.sheet = client.open(self.logname).get_worksheet(1)
return self.sheet.insert_row(data, 2)
def _discover_service(self):
if self.service is None:
store = ServiceAccountCredentials.from_json_keyfile_dict(
keyfile_dict=self._get_keyfile_dict(), scopes=self.scope)
delegated_credentials = store.create_delegated(
'*****@*****.**')
creds = delegated_credentials.authorize(http=Http())
service = discovery.build('admin', 'directory_v1', http=creds)
self.service = service
return self.service
def __init__(self, **kwargs):
self.project_id = kwargs["project_id"]
self.jsonkey = kwargs["jsonkey"]
self.appname = kwargs["appname"]
self.instanceid = kwargs["instanceid"]
jsonData = json_decode(self.jsonkey)
self.oauth_client = ServiceAccountCredentials.from_json_keyfile_dict(
jsonData, SCOPES
)
self.endpoint = "%s/v1/projects/%s/messages:send" % (BASE_URL, self.project_id)
def gsheets_auth():
print 'auth in progress'
with open('account.json', 'r') as data_file:
json_key = json.loads(data_file.read())
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
json_key, scope)
gc = gspread.authorize(credentials)
sh = gc.open_by_key('18BAUpx6HFaUaiEa5P5j-6mjbG2XhpPIlpgliKg_jFM8')
return sh
def init_sheets(creds):
"""Function to initialize google sheets API uses credentials in an env var"""
json_creds = creds
creds_dict = json.loads(json_creds, strict=False)
creds_dict["private_key"] = creds_dict["private_key"].replace(
"\\\\n", "\n")
creds = ServiceAccountCredentials.from_json_keyfile_dict(creds_dict)
client = gspread.authorize(creds)
logger.info(f"G-sheets initialized")
return client
def write_to_db(row):
creds = ServiceAccountCredentials.from_json_keyfile_dict(
keyfile_dict=keyfile_dict, scopes=scopes)
#creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gspread.authorize(creds)
sh = client.open_by_key('1yKc23vjZ9AGoUaMP1ebo9q31zVj5HEq0vUq2_C5shBs')
worksheet = sh.sheet1
worksheet.insert_row(row + [str(datetime.date.today())], 1)
sleep(1) # google api 60 write requests per 60 sec
return
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)
def get_service(api_name, api_version, scope, json_keyfile_dict):
try:
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
json_keyfile_dict, scopes=scope)
return build(
api_name,
api_version,
http=credentials.authorize(httplib2.Http()))
except Exception as e:
raise GoogleAnalyticsReporterException(
"Cannot build service ({0})".format(e))
def send_helpline_tweets(config, test_mode=False):
# Login to the Twitter account
logger.info('Logging into Twitter account')
auth = tweepy.OAuthHandler(config['twitter']['consumer_key'],
config['twitter']['consumer_secret'])
auth.set_access_token(config['twitter']['access_token'],
config['twitter']['access_token_secret'])
twitter_api = tweepy.API(auth)
# Login to the Google Docs API
logger.info('Open Helpline Tweets Google Doc spreadsheet')
try:
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_dict(config['google_docs'], scope)
gclient = gspread.authorize(credentials)
spreadsheet = gclient.open_by_key(config['helpline_tweets_doc_key'])
sheet_number = config.get('sheet_number', 0)
worksheet = spreadsheet.get_worksheet(sheet_number)
values_list = worksheet.col_values(1)
except gspread.SpreadsheetNotFound:
logger.error('Unable to open HelpLine tweets spreadsheet')
return
except KeyError:
logger.error('Missing required key in config file')
# Get the individual tweets from the file
tweets = []
for item in values_list:
tweet = item.rstrip()
if len(tweet) > 280:
logger.error(u'Tweet is too long: {}'.format(repr(tweet)))
continue
if not tweet:
continue
tweets.append(tweet)
# Tweet the articles
logger.info(u'Sending {} new tweets'.format(len(tweets)))
wait_time = config['tweet_shift'] / len(tweets)
counter = 0
wait = True
for tweet in tweets:
if counter and wait:
logger.info(u'Waiting {} seconds before sending next tweet'.format(wait_time))
time.sleep(wait_time)
logger.info(u'Sending tweet {} of {} - Tweet contents: {}'.format(counter + 1,
len(tweets),
tweet))
if not test_mode:
wait = send_tweet(twitter_api, tweet)
else:
wait = False
counter += 1
def open_connection_to_google_spreadsheet(spreadsheet_name):
"""
Opens a Google spreadsheet
:param spreadsheet_name: The name of the spreadsheet
:return: workbook object
"""
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_dict(drive_details, scope)
gc = gspread.authorize(credentials)
return gc.open(spreadsheet_name)
def GetCredentials(self):
if OAUTH2CLIENT_V2:
return ServiceAccountCredentials.from_json_keyfile_dict(
self._json_key_dict, scopes=[DEFAULT_SCOPE], token_uri=self.token_uri)
else:
return ServiceAccountCredentials(
service_account_id=self._client_id,
service_account_email=self._service_account_email,
private_key_id=self._private_key_id,
private_key_pkcs8_text=self._private_key_pkcs8_text,
scopes=[DEFAULT_SCOPE], token_uri=self.token_uri)
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 get_service_account_access_token(self, user_info, scopes=None):
"""
Get a service account access token to access objects protected by fence
:param user_info:
:param scopes: scopes to request token, defaults to ["email", "profile"]
:return: access token for service account
"""
if scopes is None or len(scopes) == 0:
scopes = ["email", "profile"]
key_json = self.get_service_account_key_json(user_info)
credentials = ServiceAccountCredentials.from_json_keyfile_dict(json.loads(key_json), scopes=scopes)
return credentials.get_access_token().access_token
def get_gapps_client():
if not settings.GAPPS_PRIVATE_KEY or not settings.GAPPS_PRIVATE_KEY_ID:
return None
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
GAPPS_JSON_CREDENTIALS,
scopes=settings.GAPPS_ADMIN_SDK_SCOPES
)
delegated_credentials = credentials.create_delegated('*****@*****.**')
http_auth = delegated_credentials.authorize(Http())
return build('admin', 'directory_v1', http=http_auth)
def _get_credentials(self):
required_scopes = [
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive',
]
s3 = boto3.resource('s3')
obj = s3.Object('www.imartinisisposano.it', 'secret_key.json').get()
keyfile_dict = json.loads(obj['Body'].read().decode('utf-8'))
return ServiceAccountCredentials.from_json_keyfile_dict(
keyfile_dict=keyfile_dict,
scopes=required_scopes
)
def _get_bigquery_service(self):
scope = [
"https://www.googleapis.com/auth/bigquery",
"https://www.googleapis.com/auth/drive"
]
key = json.loads(b64decode(self.configuration['jsonKeyFile']))
creds = ServiceAccountCredentials.from_json_keyfile_dict(key, scope)
http = httplib2.Http(timeout=settings.BIGQUERY_HTTP_TIMEOUT)
http = creds.authorize(http)
return build("bigquery", "v2", http=http)
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 = HTTPSession()
timeout_session.requests_session = TimeoutSession()
spreadsheetservice = gspread.Client(auth=creds, http_session=timeout_session)
spreadsheetservice.login()
return spreadsheetservice
def get_googleapiclient(config, project, ns, v):
"""Helper to get an authorized googleapiclient
Args:
config (dict): provider of configuration
project (str): the project used within config
ns (str): google endpoint to use
v (str): google endpoint version to use
"""
cred = ServiceAccountCredentials.from_json_keyfile_dict(
config[project]['service_account'],
scopes=['https://www.googleapis.com/auth/drive'])
http_auth = cred.authorize(Http())
return build(ns, v, http=http_auth)
def get_access_token_from_str(ga_key_content):
# from https://ga-dev-tools.appspot.com/embed-api/server-side-authorization/
# Defines a method to get an access token from the credentials object.
# The access token is automatically refreshed if it has expired.
# The scope for the OAuth2 request.
SCOPE = 'https://www.googleapis.com/auth/analytics.readonly'
# Construct a credentials objects from the key data and OAuth2 scope.
keyDict = json.loads(ga_key_content.replace('\n', '').replace('\r', ''))
_credentials = ServiceAccountCredentials.from_json_keyfile_dict(
keyDict, SCOPE)
return _credentials.get_access_token().access_token
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 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 get_rbp_color_chooser():
json_key = json.load(open("/home/gpratt/ipython_notebook/public clip-588adbc137f3.json"))
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_dict(json_key, scope)
gc = gspread.authorize(credentials)
sht1 = gc.open_by_url("https://docs.google.com/spreadsheets/d/138x3BU5hRsMUGEooVmuLRy1HbZYYg8Z28SlTK-neVJI/edit#gid=0")
ws = sht1.worksheet("Sheet1")
list_of_lists = ws.get_all_values()
manifest = pd.DataFrame(list_of_lists[1:], columns=list_of_lists[0])
manifest = manifest.set_index("RBP")
manifest['rgb'] = manifest.rgb.apply(_convert_color_to_list)
return manifest
def _get_service(self):
if self.service_obj:
return self.service_obj
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
json.loads(self.GOOGLE_SERVICE_ACCOUNT_JSON),
scopes='https://www.googleapis.com/auth/drive',
)
http = httplib2.Http()
delegated_credentials = credentials.create_delegated(self.GOOGLE_SERVICE_ACCOUNT_USER_EMAIL)
http = delegated_credentials.authorize(http)
self.service_obj = apiclient_discovery.build('drive', 'v3', http=http)
return self.service_obj