def get_spreadsheet(spreadsheetId):
"""Return an existing spreadsheet given by the ID."""
service = authorize.get_service('sheets', 'v4')
request = service.spreadsheets().get(spreadsheetId=spreadsheetId,
ranges=[],
includeGridData=False)
response = request.execute()
return response
def create_spreadsheet(title):
"""Return a new spreadsheet with given _title_."""
service = authorize.get_service('sheets', 'v4')
body_ = {"properties": {"title": title}}
request = service.spreadsheets().create(body=body_)
response = request.execute()
return response
def get_urls(spreadsheetId):
"""Return URLs to crawl from given Google spreadsheet ID."""
range_ = 'A1:A1000'
try:
service = authorize.get_service('sheets', 'v4')
request = service.spreadsheets().values().get(
spreadsheetId=spreadsheetId,
range=range_,
majorDimension='COLUMNS',
valueRenderOption='UNFORMATTED_VALUE',
dateTimeRenderOption='FORMATTED_STRING')
response = request.execute()
logger.debug(response['values'][0])
return response['values'][0]
except Exception as ex:
logger.error(ex, exc_info=True)
return []
def create_sheet(spreadsheetId, title):
"""Return a new sheet with given _title_ at given spreadsheet ID."""
body = {
"addSheet": {
"properties": {
"title": title,
"gridProperties": {
"rowCount": 100,
"columnCount": 9,
"frozenRowCount": 1
}
}
}
}
service = authorize.get_service('sheets', 'v4')
request = service.spreadsheets().batchUpdate(spreadsheetId=spreadsheetId,
body={'requests': [body]})
response = request.execute()
return response
def get_settings(spreadsheetId=os.environ['SALLY_SETTINGS_ID']):
"""Return crawler settings from given spreadsheet ID."""
range_ = 'settings!A1:G1000'
service = authorize.get_service('sheets', 'v4')
request = service.spreadsheets().values().get(
spreadsheetId=spreadsheetId,
range=range_,
majorDimension='COLUMNS',
valueRenderOption='UNFORMATTED_VALUE',
dateTimeRenderOption='FORMATTED_STRING')
response = request.execute()
logger.debug(response)
settings = {
"allowed_domains": response['values'][0][1:],
"disallowed_domains": response['values'][1][1:],
"allowed_keywords": response['values'][2][1:],
"disallowed_keywords": response['values'][3][1:],
"networks": response['values'][4][1:],
"ecommerce": response['values'][5][1:],
"allowed_countries": response['values'][6][1:]
}
return settings
def insert_to(spreadsheetId,
sheet,
rows=[['', '', '', '', '', '', '', '']],
offset=1):
"""
Insert a new item in a google spreadsheet. Return updated spreadshet.
Arguments:
spreadsheetId - ID of target spreadsheet
row - I.E. [SCORE,URL,OFFER,META,TELEPHONE,EMAIL,ECOMMERCE,SHOPING CART,
SOCIAL NETWORKS, LOCATION, CRAWLING DATE]
offsset - row number to skip in spreadsheet before insert, like in headers
"""
service = authorize.get_service('sheets', 'v4')
rangeName = sheet + '!A' + str(offset)
value_input_option = 'RAW'
body = {'values': rows}
response = service.spreadsheets().values().update(
spreadsheetId=spreadsheetId,
range=rangeName,
valueInputOption=value_input_option,
body=body).execute()
return response
def get_score(spreadsheetId=os.environ['SALLY_SETTINGS_ID']):
"""Return score values from given Google spreadsheet ID."""
range_ = 'score!A2:B1000'
service = authorize.get_service('sheets', 'v4')
request = service.spreadsheets().values().get(
spreadsheetId=spreadsheetId,
range=range_,
majorDimension='ROWS',
valueRenderOption='UNFORMATTED_VALUE',
dateTimeRenderOption='FORMATTED_STRING')
response = request.execute()
logger.debug(response)
score = {
"email": response['values'][0][1],
"telephone": response['values'][1][1],
"ecommerce": response['values'][2][1],
"offer": response['values'][3][1],
"network": response['values'][4][1],
"secure_url": response['values'][5][1],
"cart": response['values'][6][1],
"likes": response['values'][7][1]
}
return score