def adminTask(task_num):
subtitle = "Task #"+task_num
gid=request.args.get('gid')
time=request.args.get('time')
if not gid:
dtime = str(datetime.now())
gc = gspread.authorize(credentials)
works = gc.open("Usability Testing")
wks = works.worksheet('Sheet2')
wks.append_row([dtime])
gid=wks.row_count
flash('No user ID detected. Results will be added to a new row.')
form = adminTaskForm()
task_num=int(task_num)
(task,method,total_tasks) = getTask(task_num)
if request.method == 'POST':
if form.validate_on_submit() and task_num<total_tasks:
task_num+=1
gc = gspread.authorize(credentials)
works = gc.open("Usability Testing")
wks = works.worksheet('Sheet2')
completed = form.completed.data
comments = form.comments.data
method = form.method.data
if method:
wks.update_cell(gid,3+(task_num-2)*4, method)
if comments:
wks.update_cell(gid,4+(task_num-2)*4, comments)
if completed:
wks.update_cell(gid,5+(task_num-2)*4, completed)
if time !="00":
wks.update_cell(gid,6+(task_num-2)*4, time)
return redirect(url_for('adminTask', task_num=task_num, gid=gid))
elif form.validate_on_submit():
task_num+=1
gc = gspread.authorize(credentials)
works = gc.open("Usability Testing")
wks = works.worksheet('Sheet2')
completed = form.completed.data
comments = form.comments.data
method = form.method.data
if method:
wks.update_cell(gid,3+(task_num-2)*4, method)
if comments:
wks.update_cell(gid,4+(task_num-2)*4, comments)
if completed:
wks.update_cell(gid,5+(task_num-2)*4, completed)
if time !="00":
wks.update_cell(gid,6+(task_num-2)*4, time)
return redirect(url_for('adminThankYou', gid=gid))
else:
return render_template('admin_task.html', form=form, task_num=task_num, task=task, gid=gid, method=method, subtitle=subtitle)
elif request.method == 'GET':
(task,method,total_tasks) = getTask(task_num)
return render_template('admin_task.html', form=form, task=task, task_num=task_num, gid=gid, method=method, subtitle=subtitle)
def exitSurvey():
subtitle = "Exit Survey"
gid=request.args.get('gid')
if not gid:
time = str(datetime.now())
gc = gspread.authorize(credentials)
wks = gc.open("Usability Testing").sheet1
wks.append_row([time])
gid=wks.row_count
flash('No user ID detected. Results will be added to a new row.')
form = agreeLikert()
if request.method == 'POST':
if form.validate_on_submit():
gc = gspread.authorize(credentials)
wks = gc.open("Usability Testing").sheet1
for idx,field in enumerate(form):
if field.name !='submit' and field.type != 'HiddenField' and field.type !='CSRFTokenField':
wks.update_cell(gid,idx+7, field.data)
return redirect(url_for('thankyou'))
else:
return render_template('survey.html', form=form, gid=gid, subtitle=subtitle)
elif request.method == 'GET':
return render_template('survey.html', form=form, gid=gid, subtitle=subtitle)
def post(self):
text = self.get_argument("Body")
sender = self.get_argument("From").lstrip("+")
number = self.get_argument("To").lstrip("+")
signature = self.request.headers.get('X-Twilio-Signature')
proto = self.request.headers.get('X-Forwarded-Proto', self.request.protocol )
url = proto + "://"+ self.request.host + self.request.path
var = self.request.arguments
for x in var:
var[x] = ''.join(var[x])
creds = get_creds(number)
validator = RequestValidator(creds[1])
if validator.validate(url, var, signature):
r = twiml.Response()
if isadmin(sender, number):
client = TwilioRestClient(creds[0], creds[1])
gc = gspread.authorize(credentials)
worksheet = gc.open(number).worksheet("members")
members = worksheet.col_values(1)
members = filter(None, members)
for member in members:
client.messages.create(body=text, to_=member, from_=number)
r.message("Mesaage sent to %s recipients" % len(members))
else:
if re.match("^start*", text.lower()):
gc = gspread.authorize(credentials)
membersheet = gc.open(number).worksheet("members")
name = text.lower().lstrip("start").lstrip().capitalize()
membersheet.append_row([sender, name])
r.message("Thankyou, you have been added to the list")
elif re.match("^stop*", text.lower()):
gc = gspread.authorize(credentials)
membersheet = gc.open(number).worksheet("members")
try:
cell = membersheet.find(sender)
membersheet.update_cell(cell.row, cell.col, '')
r.message("You have been removed")
except:
r.message("Sorry you are not subscribed on this number")
else:
if ismember(sender, number):
gc = gspread.authorize(credentials)
membersheet = gc.open(number).worksheet("replies")
membersheet.append_row([timestamp('%Y-%m-%d %H:%M:%S'), sender, text])
else:
r.message("Sorry you are not subscribed to this list")
self.content_type = 'text/xml'
self.write(str(r))
self.finish()
else:
self.clear()
self.set_status(403)
self.finish("INVALID SOURCE")
def tryToConnect():
try:
gspCred = gspread.authorize(credentials)
worksheet = gspCred.open("meteo_tbl").sheet1
return worksheet
except Exception, e:
print("[tryToConnect] Error: %s" % str(e))
print("Waiting 60s.")
sleep(60)
print("Retrying...")
gspCred = gspread.authorize(credentials)
worksheet = gspCred.open("meteo_tbl").sheet1
return worksheet
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 get_worksheet(num_week, month=None):
"""
:param num_week:
:param month:
:return:
"""
scope = ['https://spreadsheets.google.com/feeds']
credentials = SignedJwtAssertionCredentials(
settings.GOOGLE_JSON_KEY['client_email'],
bytes(settings.GOOGLE_JSON_KEY['private_key'], 'utf-8'),
scope
)
gc = gspread.authorize(credentials)
sh = gc.open_by_key(settings.DOC_KEY_GOOGLE)
if not month is None:
name_worksheet = 'result ' + month
else:
name_worksheet = 'week ' + str(num_week)
rows = "100"
cols = "30"
try:
worksheet = sh.add_worksheet(title=name_worksheet, rows=rows, cols=cols)
except Exception:
worksheet = sh.worksheet(name_worksheet)
clean_worksheet(worksheet)
return worksheet
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_metadata_wks():
json_key = json.load(open('GlobalForestWatch-77b73e29bab8.json'))
scope = ['https://spreadsheets.google.com/feeds']
credentials = SignedJwtAssertionCredentials(json_key['client_email'], json_key['private_key'], scope)
gc = gspread.authorize(credentials)
wks = gc.open_by_key('1QRHh01869cGnQLDDInVnNdx7b4OKIMfUKGhoXtyPU94').sheet1
return wks
def update_subreddit_log(spreadsheet_url, subreddit):
try:
json_key = json.load(open("gspread_connection.json"))
except (OSError, IOError):
print("Couldn't open gpspread_connection.json")
sys.exit()
scope = ["https://spreadsheets.google.com/feeds"]
print("Connecting to Google")
credentials = SignedJwtAssertionCredentials(
json_key["client_email"], bytes(json_key["private_key"], "utf-8"), scope
)
gc = gspread.authorize(credentials)
try:
sheet = gc.open_by_url(spreadsheet_url)
except:
print("Don't forget to share the spreadsheet with the following e-mail!")
print(json_key["client_email"])
sys.exit()
print("Update AutoMod stats")
try:
work_sheet = sheet.worksheet("AutoMod Last Month")
except gspread.WorksheetNotFound:
work_sheet = sheet.add_worksheet("AutoMod Last Month", 50, 52)
update_automod_last_month(work_sheet, subreddit)
print("Update Mod Matrix")
monthly_mod_matrix(sheet, subreddit)
def get_worksheet():
json_key = json.load(open(config.json_file))
scope = ["https://spreadsheets.google.com/feeds"]
credentials = SignedJwtAssertionCredentials(json_key["client_email"], json_key["private_key"], scope)
gc = gspread.authorize(credentials)
sh = gc.open(config.spreadsheet)
return sh.worksheet(config.worksheet)
def oauthLogin():
try:
os.environ['GOOGLE_KEY']
os.environ['GOOGLE_EMAIL']
os.environ['WEAVE_STATS_SHEET']
except KeyError:
print "Please ensure you have GOOGLE_KEY, GOOGLE_EMAIL and WEAVE_STATS_SHEET set"
exit(1)
pemFile = os.environ['GOOGLE_KEY']
f = file(pemFile, 'rb')
key = f.read()
f.close()
scope = ['https://spreadsheets.google.com/feeds']
credentials = SignedJwtAssertionCredentials(os.environ['GOOGLE_EMAIL'], key, scope)
try:
gc = gspread.authorize(credentials)
except AuthenticationError:
print "Authentication Error"
exit(1)
return (gc)
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 main():
participants = {}
credentials = get_credentials()
http = credentials.authorize(httplib2.Http())
service = discovery.build('drive', 'v2', http=http)
gc = gspread.authorize(credentials)
sheet = gc.open("Spring into Summer Challenge")
wks = sheet.get_worksheet(0)
c3_name_list = wks.col_values(3)
r = praw.Reddit(user_agent='Send message to loseit weight loss challenge subscribers who have not weighed in by /u/jeffles2')
username = input('Enter your reddit username: '******'Enter your password: '******'Reddit Username':
continue
try:
user = r.get_redditor(recipient)
user.link_karma
except praw.errors.InvalidUser:
print("\n", recipient)
except praw.errors.NotFound:
print("\n", recipient)
def get_building_hours():
"""
This function retrieves the opening hours for every building when available
by calling our google-spreadsheet containing this information.
:return: a list of dictionaries for each building containing its opening hours.
"""
# first authenticate with google using oAuth2
# the credentials are stored in secrets.json and are kept out of version control.
json_key = json.load(open('secrets.json'))
scope = ['https://spreadsheets.google.com/feeds']
credentials = SignedJwtAssertionCredentials(json_key['client_email'], bytes(json_key['private_key'], 'utf-8'),
scope)
gc = gspread.authorize(credentials)
# open the google spreadsheet containing opening hours
spreadsheet = gc.open_by_key('173BpkRwPyRXR6fWgKQZ53qUgBSkyUlVWCqklT7K701Y')
# get a worksheet from the spreadsheet.
ws = spreadsheet.get_worksheet(0)
# get all entries (buildings) as python dictionaries
records = ws.get_all_records()
return records
def GLog(dT, hP, rH, tC, TC1, TC2 ):
logging.basicConfig(filename="GLog.log", level=logging.INFO)
try:
json_key = json.load(open('Uploader-8ffdcddb7d70.json'))
scope = ['https://spreadsheets.google.com/feeds']
credentials = SignedJwtAssertionCredentials(json_key['client_email'],
json_key['private_key'].encode(),
scope )
#raise gspread.exceptions.HTTPError()
gc = gspread.authorize(credentials)
wks = gc.open("TestOauthSheet2").sheet1
values = [dT, 'BME280', hP, rH, tC, 'DS18B20', TC1, TC2]
wks.append_row(values)
#print( "exception not hit" )
except gspread.exceptions.HTTPError:
time.sleep(10)
print("exception gspread HTTPError caught at ", datetime.datetime.now() )
logging.info("gspread exception")
except OSError as e:
# 101 is "network is unreachable"
print("Exception OSError occurred #", e, " at ", datetime.datetime.now() )
time.sleep(10)
logging.info("OSerror exception")
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 createEvents(lat_NW, lng_NW, lat_SE, lng_SE):
filename = os.getcwd() + '/geomap/static/geomap/data/events.csv'
localtimezone = pytz.timezone('Europe/Berlin')
if User.objects.filter(username="******"):
user = User.objects.filter(username="******")[0]
else:
user = User(username="******", password="******")
user.save()
filename = os.getcwd() + '/geomap/static/geomap/data/Moodmap-6137ab05624c.json'
json_key = json.load(open(filename))
scope = ['https://spreadsheets.google.com/feeds']
credentials = SignedJwtAssertionCredentials(json_key['client_email'], json_key['private_key'].encode(), scope)
gc = gspread.authorize(credentials)
wks = gc.open_by_url("https://docs.google.com/spreadsheets/d/14TQhBxGoy-ur_3s-JANo2v8DZadK1YRbtaz5MBY_0k0/edit#gid=0").sheet1
events = wks.get_all_records()
for event in events:
newEvent = Event()
newEvent.user = user
newEvent.creation_date = timezone.now()
newEvent.eventType = event['eventType']
newEvent.eventType_subCategory = json.dumps(event["eventType_subCategory"].split("-"))
newEvent.valid_until = timezone.now() + datetime.timedelta(0, random.uniform(300.0, 600.0)*60)
newEvent.lng = random.uniform(lng_NW, lng_SE)
newEvent.lat = random.uniform(lat_SE, lat_NW)
newEvent.description = event["description"]
newEvent.save()
print(event['eventType'])
print(event["eventType_subCategory"])
print(event["description"])
print('-------')
def login():
json_key = json.load(open('tool-cred.json'))
scope = ['https://spreadsheets.google.com/feeds']
credentials = SignedJwtAssertionCredentials(json_key['client_email'], bytes(json_key['private_key'], 'utf-8'), scope)
gc = gspread.authorize(credentials)
return gc
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 __init__(self, sheet_id):
scope = ['https://spreadsheets.google.com/feeds']
credentials = ServiceAccountCredentials.from_json_keyfile_name(os.path.join(os.path.dirname(__file__),
'service_key.json'), scope)
self.gc = gspread.authorize(credentials)
sht = self.gc.open_by_key(sheet_id)
self.worksheet = sht.get_worksheet(0)
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))
def get_data_group(code, group_type):
json_key = json.load(open(''))
scope = ['https://spreadsheets.google.com/feeds']
credentials = SignedJwtAssertionCredentials(json_key['client_email'], json_key['private_key'].encode(), scope)
gc = gspread.authorize(credentials)
sh = gc.open_by_key("")
worksheet = sh.get_worksheet(1)
#header_json = worksheet.row_values(1)
all_data = worksheet.get_all_records()
nbr_rows_new = len(all_data)
update_json = False
with open("nbr_rows.txt", "r") as f_rows:
nbr_rows_old = int(f_rows.read())
if nbr_rows_old != nbr_rows_new:
update_json = True
if update_json:
os.remove("nbr_rows.txt")
f_new = open("nbr_rows.txt", "w")
f_new.write(str(nbr_rows_new))
if update_json:
with open('biidata.json', 'w') as outfile:
json.dump(all_data, outfile, ensure_ascii=False,sort_keys=True, indent=4, separators=(',', ': '))
df0 = bii_data(group_type, code)
names = ["TRUST", "RESILIENCE", "DIVERSITY", "BELIEF", "PERFECTION", "COLLABORATION", "INNOVATION ZONE", "COMFORT ZONE", "SCORE"]
values = [df0[i] for i in names]
return values
def get_data_ind(code):
json_key = json.load(open(''))
scope = ['https://spreadsheets.google.com/feeds']
credentials = SignedJwtAssertionCredentials(json_key['client_email'], json_key['private_key'].encode(), scope)
gc = gspread.authorize(credentials)
sh = gc.open_by_key("")
worksheet = sh.get_worksheet(0)
worksheet2 = sh.get_worksheet(1)
header = worksheet2.row_values(1)
# get column index for email
email_ind = header.index("MAIL") + 1
# get a list of all emails
li = worksheet2.col_values(email_ind)
## get all data for current mail
ind = (len(li) - 1) - li[::-1].index(code) + 1
values_all = worksheet2.row_values(ind) #extract values from Worksheet2
# get indices in spreadsheet for each of the categories
names = ["TRUST", "RESILIENCE", "DIVERSITY", "BELIEF", "PERFECTION", "COLLABORATION", "INNOVATION ZONE", "COMFORT ZONE", "SCORE"]
values_ind = [header.index(i) for i in names]
# get only data for the categories
values=[values_all[i] for i in values_ind]
values = map(float, values)
return values
def get_client(self):
scope = ['https://spreadsheets.google.com/feeds']
credentials = SignedJwtAssertionCredentials(
self.auth_dict['client_email'], self.auth_dict['private_key'].encode(), scope
)
client = gspread.authorize(credentials)
return client
def main():
"""Go through tilt sensor measurements and create datasets for pole
centers.
"""
credentials = get_credentials()
gc = gspread.authorize(credentials)
tilt_data = gc.open('Tilt Measurements')
# Set the start date.
start_sec = int(time.mktime(time.strptime(START_DATE, "%d-%m-%Y")))
for time_sec in xrange(start_sec, int(time.time()), SEC_PER_DAY):
ws_date = time.strftime('%Y-%m-%d', time.localtime(time_sec))
copy_previous_tilt_centers(time_sec)
try:
sheet = tilt_data.worksheet(ws_date)
print(sheet)
update_tilt_centers(ws_date, sheet)
except(gspread.exceptions.WorksheetNotFound):
print("No data for %s" % ws_date)
continue
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 提著資料表(self):
登入憑證 = ServiceAccountCredentials.from_json_keyfile_name(
self.key_file_name, self.google_sheet_scope
)
return gspread.authorize(登入憑證).open_by_url(
self.url
).sheet1
def get_google_sheet():
json_key = json.load(open('credentials.json'))
scope = ['https://spreadsheets.google.com/feeds']
credentials = SignedJwtAssertionCredentials(json_key["client_email"], json_key['private_key'], scope)
authorization = gspread.authorize(credentials)
spreadsheet = authorization.open("Reliable Sources")
return spreadsheet
def action(self):
credentials = OAuth2Credentials.from_json(shared.client_credentials)
gc = gspread.authorize(credentials)
# get worksheet
sh = gc.open(shared.update_gsheet['filename'])
worksheet = sh.get_worksheet(0)
# data
datalist = shared.update_gsheet['data']
# loop through each data for row
row = 1
for k in range(0, len(datalist)):
col_num = len(datalist[k])
col_letter = chr(ord('A') + col_num - 1)
row_range = 'A{row}:{col}{row}'.format(row=row, col=col_letter)
cell_list = worksheet.range(row_range)
# loop through data and write in cell
for i in range(0, len(cell_list)):
cell_list[i].value = datalist[k][i]
worksheet.update_cells(cell_list)
row += 1
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
def __init__(self,
cred_file,
name='GoogleLogSheet',
key=None,
sheet_num=1,
update_interval=5,
new_sheet=False,
attri=None,
email=None,
addi_attri={}):
"""Google Log allows easy logging to google sheet
Parameters
----------
cred_file : str
path to the cred file
name : str, optional
name of the sheet (the default is 'GoogleLogSheet')
key : [type], optional
key of the sheet (the default is None, which uses name to access instead, overides name if provided)
sheet_num : int, optional
page number (the default is 1, which is the first page of the spreadsheet)
update_interval : int, optional
update to the google sheet once # seconds (the default is 5, which [default_description])
new_sheet : bool, optional
[description] (the default is False, which [default_description])
attri : [type], optional
[description] (the default is None, which [default_description])
email : [type], optional
[description] (the default is None, which [default_description])
addi_attri : dict, optional
[description] (the default is {}, which [default_description])
"""
self.thread = None
self.key = key
self.sheet_name = name
self.msg_queue = Queue()
self.sheet_num = sheet_num
self._handler = GoogleLogHandler(self, attris=attri)
self.attris = self._handler.attris
self.addi_attris = addi_attri
self._should_stop = False
self.update_interval = update_interval
credentials = ServiceAccountCredentials.from_json_keyfile_name(
cred_file, _SCOPES)
self.gc = gspread.authorize(credentials)
if not new_sheet:
try:
if self.key is None:
self.spreadsheet = self.gc.open(self.sheet_name)
else:
self.key = key
self.spreadsheet = self.gc.open_by_key(self.key)
except:
print('Cannot open sheet, making one')
self.spreadsheet = self.make_sheet()
else:
self.spreadsheet = self.make_sheet()
if email is not None:
self.spreadsheet.share(email,
perm_type='user',
role='writer',
notify=True,
email_message='Make you day @ Google Log')
self.worksheet = getattr(self.spreadsheet, f'sheet{sheet_num}')
self.thread = Thread(target=self._write_message)
self.thread.daemon = True
self.thread.start() # the thread dies with this program
def auth_gss_client(path, scopes):
credentials = ServiceAccountCredentials.from_json_keyfile_name(
path, scopes)
return gspread.authorize(credentials)
# Save the credentials for the next run
with open('token.pickle', 'wb') as token:
pickle.dump(creds, token)
return creds
class MyCredentials(object):
def __init__(self, access_token=None):
self.access_token = access_token
def refresh(self, http):
self.access_token = readCredentials('credentials.json').token
mycreds = MyCredentials()
gc = gspread.authorize(mycreds)
makers_sheet = gc.open_by_url(
'https://docs.google.com/spreadsheets/d/1uWdmNRFeJNpwg0keMAodzf3xAXCL1cvz9mPFfdUcHmQ/edit#gid=1653540701'
)
#print(makers_sheet.worksheets())
caceres = makers_sheet.get_worksheet(9)
print(caceres)
stock = str(caceres.acell('B3').value)
#"{" + stock + ": { " + user + ":"}
user = caceres.acell('B4').value
capacidad = caceres.acell('C4').value
stock_actual = caceres.acell('D4').value
entregadas = capacidad = caceres.acell('E4').value
direccion = capacidad = caceres.acell('F4').value
import gspread
from oauth2client.service_account import ServiceAccountCredentials
# use creds to create a client to interact with the Google Drive API
# scope = ['https://spreadsheets.google.com/feeds']
scope = [
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
creds = ServiceAccountCredentials.from_json_keyfile_name(
'/home/himanshuvats/Downloads/client_secret.json', 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("Source Names for Bain-OYO").sheet1
sheet = client.open(
"Bain City vs Number of Properties | Internal").get_worksheet(5)
# Extract and print all of the values
# list_of_hashes = sheet.get_all_values()
cell = sheet.find('vienna')
print(cell.value, cell.row, cell.col)
sheet.update_cell(cell.row, 19, 'Test')
# print(list_of_hashes)
import sys
import os
import time
encoderurl = 'rtsp://10.60.0.49:554/main'
camurl = 'rtsp://10.60.0.52:7447/5ac91bd5ec2e84e0ab7649cb_0'
ballroom = "BallroomA"
starttimes = ["8:00", "9:15", "13:00", "14:00", "04-27-10:26"]
scope = ['https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('self-video-zach-208096653289.json', scope)
gc = gspread.authorize(credentials)
gdspreadsheet = gc.open("speakers-list")
worksheet = gdspreadsheet.worksheet(ballroom)
def start():
#worksheet_list = gdspreadsheet.worksheets()
#print worksheet_list
#currentdate = datetime.now().strftime('%m-%d')
#print timestamp
checklist()
#This will check the list to see if the current time matches a time in the list
def __init__(self, credentials):
self.conn = gspread.authorize(credentials)
self.personnel = self.getdb()
self.schedule = self.getschedule()
self.sanitize = re.compile(r'[^\d]+')
from oauth2client.service_account import ServiceAccountCredentials
import json
import gspread
cred = ServiceAccountCredentials.from_json_keyfile_name(
'key.json', ['https://spreadsheets.google.com/feeds'])
google_sheet = gspread.authorize(cred)
doc = google_sheet.open_by_key('1jvMgRCkWwnepc-Y25piFbWkYVn0gf81grfZbZuuk5Nc')
sheet = doc.worksheet('DEV')
data = []
for man_name, party, restaurant_name, address, kind, price_str, memo in sheet.get_all_values(
)[1:]:
man_name = man_name.strip()
party = party.strip()
restaurant_name = restaurant_name.strip()
address = address.strip()
kind = kind.strip()
price = int(price_str.replace(',', ''))
data.append((man_name, party, restaurant_name, address, kind, price, memo))
print(json.dumps(data))
json.dump(data, open('data/data.json', 'w+'))
def gsheet(group_name):
#
scope = [
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
creds = ServiceAccountCredentials.from_json_keyfile_name(
settings.gsheet_key_filename, scope)
conn = gspread.authorize(creds)
repos = get_github_repos()
try:
worksheet = conn.open(settings.gspreadsheet_name).worksheet(group_name)
githubs = [x.lower() for x in worksheet.col_values(19)[2:]]
time.sleep(30)
except:
raise Exception("No group {}: {}".format(group_name, repo['name']))
#
for repo in repos:
lab_id = int(repo['name'].split('-task')[1].split('-')[0])
github = repo['name'].split('-task')[1].split('-', 1)[1]
b = False
for g in githubs:
if g != '' and github.startswith(g):
github = g
b = True
if b and (lab_id == 2 or lab_id == 3):
try:
worksheet = conn.open(
settings.gspreadsheet_name).worksheet(group_name)
time.sleep(1)
except:
raise Exception("No group {}: {}".format(
group_name, repo['name']))
stud_row = githubs.index(github) + 3
if lab_id == 2:
completion_date = get_successfull_build_info(repo).get(
"completed_at")
is_empty = worksheet.cell(stud_row, 4 + 1).value.strip() == ''
time.sleep(1)
correct_task = check_task_t2(
repo,
worksheet.cell(stud_row, 1).value.strip())
time.sleep(1)
elif lab_id == 3:
completion_date = get_successfull_status_info(repo).get(
"updated_at")
is_empty = worksheet.cell(stud_row, 7 + 1).value.strip() == ''
time.sleep(1)
correct_task = check_task_a3(
repo,
worksheet.cell(stud_row, 1).value.strip())
time.sleep(1)
else:
completion_date = None
is_empty = False
correct_task = False
if completion_date and is_empty and correct_task:
worksheet.update_cell(stud_row, 4 + (lab_id - 2) * 3,
repo["full_name"])
time.sleep(1)
worksheet.update_cell(
stud_row, 4 + (lab_id - 2) * 3 + 1,
datetime.datetime.strptime(
completion_date,
'%Y-%m-%dT%H:%M:%SZ').date().isoformat())
time.sleep(1)
def testApp():
scope = [
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
# read env data
credentials_raw = os.environ.get('GOOGLE_APPLICATION_CREDENTIALS')
credentials_json = json.loads(credentials_raw)
credentials = ServiceAccountCredentials.from_json_keyfile_dict(
credentials_json, scope)
gc = gspread.authorize(credentials)
url = "https://docs.google.com/spreadsheets/d/12_iuy8fyGuZ8KreBWNmaJirSsSmD1Lf3D10UVDUJ3rc/edit#gid=0"
log = gc.open_by_url(url).sheet1.col_values(1)
crime_Dict = {
"crimes": [{
"crime": "unauthorized entry",
"count": 0
}, {
"crime": "marijuana",
"count": 0
}, {
"crime": "controlled substance",
"count": 0
}, {
"crime": "drug paraphernalia",
"count": 0
}, {
"crime": "underage consumption",
"count": 0
}, {
"crime": "poss. of alcohol",
"count": 0
}, {
"crime": "golf cart",
"count": 0
}, {
"crime": "assault",
"count": 0
}, {
"crime": "rape",
"count": 0
}, {
"crime": "breaking and entering",
"count": 0
}, {
"crime": "larceny",
"count": 0
}, {
"crime": "injury",
"count": 0
}, {
"crime": "vehicle",
"count": 0
}, {
"crime": "traffic stop",
"count": 0
}, {
"crime": "fondling",
"count": 0
}, {
"crime": "scam",
"count": 0
}, {
"crime": "trespass",
"count": 0
}, {
"crime": "suspicious",
"count": 0
}, {
"crime": "fraud",
"count": 0
}, {
"crime": "forgery",
"count": 0
}, {
"crime": "vandalism",
"count": 0
}, {
"crime": "hit and run",
"count": 0
}, {
"crime": "traffic crash",
"count": 0
}]
}
crimes = crime_Dict['crimes']
# loops through keys in crime dictionary
for crime in crimes:
# loops through entries in the crime log
for entry in log:
# if the name of the crime is mentioned in the entry
if crime['crime'] in entry.lower():
# increment by one
crime['count'] += 1
return jsonify(crime_Dict)
def get_authenticated_gspread():
"""Returns an authenticated gspread instance."""
scope = [GOOGLE_DRIVE_SPREADSHEET_SCOPE_URL]
credentials = ServiceAccountCredentials.from_json_keyfile_name(
DRIVE_KEY_FPATH, scope)
return gspread.authorize(credentials)
import gspread
import gspread.utils as r2c
import re
import sys
import easygui
from oauth2client.service_account import ServiceAccountCredentials
# use creds to create a client to interact with the Google Drive API
scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name('Inventory Management-576c00641514.json', scope)
gc = gspread.authorize(creds)
# Find a workbook by name and open the first sheet
# Make sure you use the right name here.
ss = gc.open_by_url('https://docs.google.com/spreadsheets/d/1Kqj6ybFQmvJw3DYZmj72Hz5jWpZo2tuehRbAxLVFj2w/edit#gid=1783603278')
##########################################
Response = ss.worksheet("Form Responses")
#########################################
last_row = False
index = 1
while last_row == False:
cell = 'C' + str(index)
def handle_message(event):
msg = event.message.text
#print(type(msg))
msg = msg.encode('utf-8')
if event.message.text == "最新電影":
a = movie()
line_bot_api.reply_message(event.reply_token, TextSendMessage(text=a))
if event.message.text == "你好":
line_bot_api.reply_message(event.reply_token,
TextSendMessage(text=event.message.text))
elif event.message.text == "貼圖":
print("貼圖get")
line_bot_api.reply_message(
event.reply_token, StickerSendMessage(package_id=1, sticker_id=2))
elif event.message.text == "圖片":
print("圖片get")
line_bot_api.reply_message(
event.reply_token,
ImageSendMessage(original_content_url='圖片網址',
preview_image_url='圖片網址'))
elif event.message.text == "影片":
line_bot_api.reply_message(
event.reply_token,
VideoSendMessage(original_content_url='影片網址',
preview_image_url='預覽圖片網址'))
elif event.message.text == "音訊":
line_bot_api.reply_message(
event.reply_token,
AudioSendMessage(original_content_url='音訊網址', duration=100000))
elif event.message.text == "位置":
print("位置get")
line_bot_api.reply_message(
event.reply_token,
LocationSendMessage(title='my location',
address='Tainan',
latitude=22.994821,
longitude=120.196452))
if event.message.text == "紀錄":
line_bot_api.reply_message(event.reply_token,
TextSendMessage(text="紀錄成功"))
pass
GDriveJSON = 'Json檔名.json'
GSpreadSheet = 'Google試算表名稱'
while True:
try:
scope = ['https://spreadsheets.google.com/feeds']
key = SAC.from_json_keyfile_name(GDriveJSON, scope)
gc = gspread.authorize(key)
worksheet = gc.open(GSpreadSheet).sheet1
except Exception as ex:
print('無法連線Google試算表', ex)
sys.exit(1)
textt = ""
textt += event.message.text
if textt != "":
worksheet.append_row((datetime.datetime.now(), textt))
print('新增一列資料到試算表', GSpreadSheet)
return textt
if event.message.text == "News":
a = apple_news()
g = []
h = []
n = []
for i in range(0, len(a), 3):
g.append(a[i])
h.append(a[i + 1])
n.append(a[i + 2])
m = []
x = ['title', 'link', 'link2']
m.append(g)
m.append(h)
m.append(n)
dictionary = dict(zip(x, m))
p = random.sample(range(12), 3)
Image_Carousel = TemplateSendMessage(
alt_text='目錄 template',
template=ImageCarouselTemplate(columns=[
ImageCarouselColumn(
image_url=dictionary['link2'][p[0]],
action=URITemplateAction(uri=dictionary['link'][p[0]],
label=dictionary['title'][p[0]]
[0:11])),
ImageCarouselColumn(
image_url=dictionary['link2'][p[2]],
action=URITemplateAction(uri=dictionary['link'][p[2]],
label=dictionary['title'][p[2]]
[0:11])),
ImageCarouselColumn(image_url=dictionary['link2'][p[1]],
action=URITemplateAction(
uri=dictionary['link'][p[1]],
label=dictionary['title'][p[1]][0:11]))
]))
line_bot_api.reply_message(event.reply_token, Image_Carousel)
elif event.message.text == "樣板":
print("TEST1")
buttons_template = TemplateSendMessage(
alt_text='目錄 template',
template=ButtonsTemplate(
title='Template-樣板介紹',
text='Template分為四種，也就是以下四種：',
thumbnail_image_url='圖片網址',
actions=[
MessageTemplateAction(label='Buttons Template',
text='Buttons Template'),
MessageTemplateAction(label='Confirm template',
text='Confirm template'),
MessageTemplateAction(label='Carousel template',
text='Carousel template'),
MessageTemplateAction(label='Image Carousel',
text='Image Carousel')
]))
line_bot_api.reply_message(event.reply_token, buttons_template)
elif event.message.text == "Buttons Template":
print("TEST")
buttons_template = TemplateSendMessage(
alt_text='Buttons Template',
template=ButtonsTemplate(
title='這是ButtonsTemplate',
text='ButtonsTemplate可以傳送text,uri',
thumbnail_image_url='圖片網址',
actions=[
MessageTemplateAction(label='ButtonsTemplate',
text='ButtonsTemplate'),
URITemplateAction(label='VIDEO1', uri='網址'),
PostbackTemplateAction(label='postback',
text='postback text',
data='postback1')
]))
line_bot_api.reply_message(event.reply_token, buttons_template)
elif event.message.text == "Carousel template":
print("Carousel template")
Carousel_template = TemplateSendMessage(
alt_text='目錄 template',
template=CarouselTemplate(columns=[
CarouselColumn(thumbnail_image_url='網址',
title='this is menu1',
text='description1',
actions=[
PostbackTemplateAction(
label='postback1',
text='postback text1',
data='action=buy&itemid=1'),
MessageTemplateAction(label='message1',
text='message text1'),
URITemplateAction(label='uri1', uri='網址')
]),
CarouselColumn(thumbnail_image_url='網址',
title='this is menu2',
text='description2',
actions=[
PostbackTemplateAction(
label='postback2',
text='postback text2',
data='action=buy&itemid=2'),
MessageTemplateAction(label='message2',
text='message text2'),
URITemplateAction(label='連結2', uri='網址')
])
]))
line_bot_api.reply_message(event.reply_token, Carousel_template)
elif event.message.text == "Confirm template":
print("Confirm template")
Confirm_template = TemplateSendMessage(
alt_text='目錄 template',
template=ConfirmTemplate(title='這是ConfirmTemplate',
text='這就是ConfirmTemplate,用於兩種按鈕選擇',
actions=[
MessageTemplateAction(label='Y',
text='Y'),
MessageTemplateAction(label='N',
text='N')
]))
line_bot_api.reply_message(event.reply_token, Confirm_template)
elif event.message.text == "Image Carousel":
print("Image Carousel")
Image_Carousel = TemplateSendMessage(
alt_text='目錄 template',
template=ImageCarouselTemplate(columns=[
ImageCarouselColumn(image_url='網址',
action=PostbackTemplateAction(
label='postback1',
text='postback text1',
data='action=buy&itemid=1')),
ImageCarouselColumn(image_url='網址',
action=PostbackTemplateAction(
label='postback2',
text='postback text2',
data='action=buy&itemid=2'))
]))
line_bot_api.reply_message(event.reply_token, Image_Carousel)
return 'OK2'
#Authenticating accsess to google
import sys
#++++++++++++++++++++ Google Authentication ++++++++++++++++++++#
try:
#printing debug message
print("Authenticating to Google Sheets/Google...\n\n")
#setting scope (do not change)
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"]
#setting credentials and authorizing credentials
creds = ServiceAccountCredentials.from_json_keyfile_name("######.json", scope)
gc = gspread.authorize(######)
#opening worksheet and sheet1
wks = gc.open("#######").sheet1
#finishing debug message
print("Done!\n\n")
except:
sys.exit("There was a problem with Google Authentication!")
#++++++++++++++++++++ Google Authentication ++++++++++++++++++++#
#++++++++++++++++++++ Declaring Variables ++++++++++++++++++++#
scoutingGroup = 0
counter = 0
groupNumCounter = 1
def updateCRN():
department = 0
index = 1
while 1:
try:
scope = [
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
creds = ServiceAccountCredentials.from_json_keyfile_name(
'Project-f939c591cfa1.json', scope)
client = gspread.authorize(creds)
print("Initializing")
sheet3 = client.open("Department and courses").get_worksheet(2)
sheet = client.open("Department and courses").sheet1
departmentClasses = sheet.get_all_records()
#time.sleep(10)
index_temp = index
department_temp = department
for deparment in departmentClasses[department_temp:]:
#if deparment['department'] == 'SOCW':
row = []
row.append(deparment['department'])
i = 1
while deparment['course' +
str(i)] != '' and i < sheet.col_count - 1:
row.append(deparment['course' + str(i)])
i = i + 1
print(row)
rows = getCRN(row)
for row in rows:
time.sleep(2) #Limiting to 98.333 requests per second
print(index_temp)
if sheet3.cell(index_temp, 1).value == '':
print('writing row')
#print row
#time.sleep(1.5)
sheet3.insert_row(row, index_temp)
index_temp = index_temp + 1
else:
print('Not writing row')
index_temp = index_temp + 1
department = department + 1
index = index_temp
break
except Exception as e:
print(e)
print("Sleeping for 100 seconds")
time.sleep(100)
print('Error on line {}'.format(sys.exc_info()[-1].tb_lineno),
type(e).__name__, e)
scope = [
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
creds = ServiceAccountCredentials.from_json_keyfile_name(
'Project-f939c591cfa1.json', scope)
client = gspread.authorize(creds)
print("Re Authorizing - section update")
def auth_with_gsheets():
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
client = gs.authorize(creds)
return client
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()
def authorize(self, keyfile): self.keyfile = keyfile scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive'] credentials = ServiceAccountCredentials.from_json_keyfile_name(self.keyfile, scope) self.google = gspread.authorize(credentials) print "Google APIs Authorized"
# -*- coding: utf-8 -*-
"""
Created on Sat Jan 11 14:58:24 2020
@author: YANGS
"""
import gspread
from oauth2client.service_account import ServiceAccountCredentials
auth_json_path = '1.json' ##頻政黨按
gss_scopes = ['https://spreadsheets.google.com/feeds']
#連線
credentials = ServiceAccountCredentials.from_json_keyfile_name(auth_json_path,gss_scopes)
gss_client = gspread.authorize(credentials)
#開啟 Google Sheet 資料表
spreadsheet_key = '12312312312' google表單的key
#建立工作表1
sheet = gss_client.open_by_key(spreadsheet_key).sheet1
#自定義工作表名稱
#sheet = gss_client.open_by_key(spreadsheet_key).worksheet('測試1')
sheet.update_acell('D2', 'ABC') #D2加入ABC
sheet.update_cell(2, 4, 'ABC') #D2加入ABC(第2列第4行即D2)
values = ['A','B','C','D']
sheet.insert_row(values, 1) #插入values到第1列
def context(credentials):
return gspread.authorize(credentials)
def _authorize_credentials(self):
return gspread.authorize(self.CREDENTIALS)
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")
import gspread
from google.oauth2.service_account import Credentials
from pprint import pprint
SCOPE = [
"https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/drive.file",
"https://www.googleapis.com/auth/drive"
]
CREDS = Credentials.from_service_account_file('creds.json')
SCOPED_CREDS = CREDS.with_scopes(SCOPE)
GSPREAD_CLIENT = gspread.authorize(SCOPED_CREDS)
SHEET = GSPREAD_CLIENT.open('love_sandwiches')
def get_sales_data():
"""
Get sales figures input from the user.
Run a while loop to collect a valid string of data from the user
via the terminal, which must be a string of 6 numbers separated
by commas. The loop will repeatedly request data, until it is valid.
"""
while True:
print("Please enter sales data from the last market.")
print("Data should be six numbers, separated by commas.")
print("Example: 10,20,30,40,50,60\n")
data_str = input("Enter your data here: ")
sales_data = data_str.split(",")
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)
def job_function():
print("Hello World")
print(datetime.datetime.now())
# use creds to create a client to interact with the Google Drive API
scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name(
'bitcoin railroad-8cc9cd351748.json', 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("bitcoin railroad registration").sheet1
# In[21]:
list_of_lists = sheet.get_all_values()
df = pd.DataFrame(list_of_lists[1:])
df = df[[2, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13]]
df.columns = [
'email', 'coin1', 'coin2', 'coin3', 'coin4', 'coin5', 'url1', 'url2',
'url3', 'url4', 'url5'
]
df = df[df.email != ""]
# In[8]:
## FILL THIS IN:
client_id = ""
client_secret = ""
username = ""
password = ""
user_agent = ""
reddit = praw.Reddit(client_id=client_id,
client_secret=client_secret,
password=password,
username=username,
user_agent=user_agent)
keywords = [
"release", "listing", "partnership", "campaign", "conference", "rumor",
"china", "korea", "japan", "government"
]
# In[115]:
# In[120]:
now_hour = datetime.datetime.now().hour
now_minute = datetime.datetime.now().minute
# In[ ]:
# FOR NEW SUBSCRIBERS
# for idx, row in df.iterrows():
# email = df.loc[idx][0]
# if email not in email_list:
# print(email)
# tokens = []
# for i in (0,1,2,3,4):
# tokens.append(df.loc[idx][i+1])
# price_texts = []
# for token in tokens:
# print(tokens)
# print(token)
# price_time = minute_price_historical(token, 'BTC', 1, 1).iloc[-1]['timestamp']
# close = minute_price_historical(token, 'BTC', 1, 1).iloc[-1]['close']
# time_text = price_time.strftime("%H:%M") + " EST"
# high30 = daily_price_historical(token, 'BTC', 1, 1)[-31:-1]['high'].max()
# low30 = daily_price_historical(token, 'BTC', 1, 1)[-31:-1]['low'].min()
# price_text = "<h2>$" + str(token) + ": " + str(close) + " sats (" + str(time_text) + ") </h2> <h3>30-day high (low): " + str(high30) + " (" + str(low30) + ") sats</h3><br>"
# print(price_text)
# price_texts.append(price_text)
# prices = ''.join(price_texts)
# print(prices)
# reddit_urls = []
# reddit_urls.append(df.loc[idx][6][25:])
# reddit_urls.append(df.loc[idx][7][25:])
# reddit_urls.append(df.loc[idx][8][25:])
# reddit_urls.append(df.loc[idx][9][25:])
# reddit_urls.append(df.loc[idx][10][25:])
# news_all = []
# #print(df.loc[idx][idx2+1])
# for keyword in keywords:
# print("Searching for " + keyword + "...")
# news = collectnews(reddit, reddit_urls, keyword = keyword, period = "day")
# #for item in news:
# #item[0] = df.loc[idx][idx2+1]
# #print(df.loc[idx+1])
# print(news)
# news_all = news_all + news
# print("All News: ", news_all)
# no_news_toggle = False
# if news_all == [] or len(news_all) < 5:
# no_news_toggle = True
# print("No news found. Searching in bitcoin and ethereum...")
# reddit_urls = []
# reddit_urls.append("bitcoin")
# reddit_urls.append("ethereum")
# for keyword in keywords:
# print("Searching for " + keyword + "...")
# news = collectnews(reddit, reddit_urls, keyword = keyword, period = "hour")
# #for item in news:
# #item[0] = df.loc[idx][idx2+1]
# #print(df.loc[idx+1])
# print(news)
# news_all = news_all + news
# #news_all = dict((x[3], x) for x in news_all).values()
# news_all = sorted(news_all)
# #select coin/reddit for first instance to set as title
# search = news_all[0][0]
# for news in news_all[1:]:
# if news[0] == search:
# news[0] = ""
# else:
# search = news[0]
# news_all_lines = []
# for news in news_all:
# extracted = tldextract.extract(news[3])
# domain = "{}.{}".format(extracted.domain, extracted.suffix)
# news_all_lines.append("""<br><h2><div align="center">""" + str(news[0]).upper() + "</div></h2> [" + domain.capitalize() + """] <a href=" """ + str(news[3]) + """ ">""" + str(news[1]) + "</a>")
# news_all_final = ' '.join([news_line for news_line in news_all_lines])
# # In[171]:
# if no_news_toggle == True:
# no_news = "<br><h3> Check back tomorrow for more news on your favorite coins. In the meantime, check out these news in bitcoin/ethereum below! </h3><br>"
# else:
# no_news = ""
# FILL THIS IN:
# user = "" #Email to send from
# pwd = "" #Password of email to send from
# recipient = "" # Receiving email
# body = retrieve_html()
# msg = Template(body).safe_substitute(comment = news_all_final, price = prices, no_news = no_news)
# # now = datetime.datetime.now()
# # if now.hour > 12:
# # subject = str(now.month) + "/" + str(now.day) + " Reddit News " + str(now.hour - 12) + "pm"
# # else:
# # subject = str(now.month) + "/" + str(now.day) + " Reddit News " + str(now.hour) + "am"
# now = datetime.datetime.now()
# nowhour = str(now.month)
# d0 = datetime.date(2017, 10, 25)
# d1 = datetime.date.today()
# delta = d1 - d0
# subject = "Bitcoin Railroad: Train #" + str(delta.days)
# if msg:
# send_email(user, pwd, recipient, subject, msg)
# part = MIMEText(body, 'html')
# print("end")
# email_list.append(email)
# In[92]:
if (now_hour == 7 and now_minute == 0):
for idx, row in df.iterrows():
try:
#Retrieve token symbols
tokens = []
for i in (0, 1, 2, 3, 4):
tokens.append(df.loc[idx][i + 1])
price_texts = []
for token in tokens:
print(tokens)
print(token)
price_time = minute_price_historical(
token, 'BTC', 1, 1).iloc[-1]['timestamp']
close = minute_price_historical(token, 'BTC', 1,
1).iloc[-1]['close']
time_text = price_time.strftime("%H:%M") + " EST"
high30 = daily_price_historical(token, 'BTC', 1,
1)[-31:-1]['high'].max()
low30 = daily_price_historical(token, 'BTC', 1,
1)[-31:-1]['low'].min()
price_text = "<h2>$" + str(token) + ": " + str(
close) + " sats (" + str(
time_text
) + ") </h2> <h3>30-day high (low): " + str(
high30) + " (" + str(low30) + ") sats</h3><br>"
print(price_text)
price_texts.append(price_text)
prices = ''.join(price_texts)
print(prices)
reddit_urls = []
reddit_urls.append(df.loc[idx][6][25:])
reddit_urls.append(df.loc[idx][7][25:])
reddit_urls.append(df.loc[idx][8][25:])
reddit_urls.append(df.loc[idx][9][25:])
reddit_urls.append(df.loc[idx][10][25:])
news_all = []
#print(df.loc[idx][idx2+1])
for keyword in keywords:
print("Searching for " + keyword + "...")
news = collectnews(reddit,
reddit_urls,
keyword=keyword,
period="day")
#for item in news:
#item[0] = df.loc[idx][idx2+1]
#print(df.loc[idx+1])
print(news)
news_all = news_all + news
print("All News: ", news_all)
no_news_toggle = False
if news_all == [] or len(news_all) < 5:
no_news_toggle = True
print(
"No news found. Searching in bitcoin and ethereum...")
reddit_urls = []
reddit_urls.append("bitcoin")
reddit_urls.append("ethereum")
for keyword in keywords:
print("Searching for " + keyword + "...")
news = collectnews(reddit,
reddit_urls,
keyword=keyword,
period="hour")
#for item in news:
#item[0] = df.loc[idx][idx2+1]
#print(df.loc[idx+1])
print(news)
news_all = news_all + news
#news_all = dict((x[3], x) for x in news_all).values()
news_all = sorted(news_all)
#select coin/reddit for first instance to set as title
search = news_all[0][0]
for news in news_all[1:]:
if news[0] == search:
news[0] = ""
else:
search = news[0]
news_all_lines = []
for news in news_all:
extracted = tldextract.extract(news[3])
domain = "{}.{}".format(extracted.domain, extracted.suffix)
news_all_lines.append("""<br><h2><div align="center">""" +
str(news[0]).upper() +
"</div></h2> [" +
domain.capitalize() +
"""] <a href=" """ + str(news[3]) +
""" ">""" + str(news[1]) + "</a>")
news_all_final = ' '.join(
[news_line for news_line in news_all_lines])
# In[171]:
if no_news_toggle == True:
no_news = "<br><h3> Check back tomorrow for more news on your favorite coins. In the meantime, check out these news in bitcoin/ethereum below! </h3><br>"
else:
no_news = ""
# FILL THIS IN:
user = "" #Email to send from
pwd = "" #Password of email to send from
recipient = "" # Receiving email
body = retrieve_html()
msg = Template(body).safe_substitute(comment=news_all_final,
price=prices,
no_news=no_news)
# now = datetime.datetime.now()
# if now.hour > 12:
# subject = str(now.month) + "/" + str(now.day) + " Reddit News " + str(now.hour - 12) + "pm"
# else:
# subject = str(now.month) + "/" + str(now.day) + " Reddit News " + str(now.hour) + "am"
now = datetime.datetime.now()
nowhour = str(now.month)
d0 = datetime.date(2017, 10, 25)
d1 = datetime.date.today()
delta = d1 - d0
subject = "Bitcoin Railroad: Train #" + str(delta.days)
if msg:
send_email(user, pwd, recipient, subject, msg)
part = MIMEText(body, 'html')
print("end")
except Exception, e:
print(e)
pass
def submit():
scope = [
'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(
"client_cred.json", scope)
client = gspread.authorize(creds)
sheet = client.open("Tech Test").sheet1 # get sheet 1 data
sheet_data = sheet.get_all_records()
k = []
f_data = []
for data in sheet_data:
if db.session.query(DataTableModel).filter(
DataTableModel.first_name == data["first_name"]).count() == 0:
first_name = data["first_name"]
last_name = data["last_name"]
email = data["email"]
job_title = data["job_title"]
job_title_full = data["job_title_full"]
city = data["city"]
company = data["company"]
country = data["country"]
linkedin = data["linkedin"]
company_website = data["company_website"]
company_industry = data["company_industry"]
company_founded = (data["company_founded"])
company_size = data["company_size"]
company_linkedin = data["company_linkedin"]
company_headquaters = data["company_headquarters"]
email_reliability_status = data["email_reliability_status"]
receiving_email_server = data["receiving_email_server"]
kind = data["kind"]
tag = data["tag"]
month = data["month"]
# print(month)
commit_data = DataTableModel(
first_name, last_name, email, job_title_full, job_title, city,
country, linkedin, company, company_website, company_industry,
company_founded, company_size, company_linkedin,
company_headquaters, email_reliability_status,
receiving_email_server, kind, tag, month)
db.session.add(commit_data)
db.session.commit()
post_data = db.session.query(DataTableModel).all()
for data in post_data:
f_data.append({
"id": data.id,
"first_name": data.first_name,
"last_name": data.last_name,
"email": data.email,
"job_title": data.job_title,
"job_title_full": data.job_title_full,
"city": data.city,
"country": data.country,
"linkedin": data.linkedin,
"company": data.company,
"company_website": data.company_website,
"company_industry": data.company_industry,
"company_founded": data.company_founded,
"company_size": data.company_size,
"company_linkedin": data.company_linkedin,
"company_headquaters": data.company_headquaters,
"email_reliability_status": data.email_reliability_status,
"receiving email server": data.receiving_email_server,
"kind": data.kind,
"tag": data.tag,
"month": data.month
})
# print(f_data)
return jsonify(f_data)
def main(self):
# Set Google template and folder ID
template_id = Globals.template_id
folder_id = Globals.report_folder_id
drive = build('drive', 'v3', credentials=self.get_creds(SCOPES_DRIVE))
gs = gspread.authorize(self.get_creds(SCOPES_SHEETS))
sheet_id = None
log_gdrive = os.path.join(self.report_path, 'logs', '_gdrive_log.txt')
if os.path.exists(log_gdrive):
with open(log_gdrive) as file:
sheet_id = file.readline()
else:
sheet_id = gs.copy(template_id, title=self.name, copy_permissions=True).id
file_metadata = {
'name': self.name,
'mimeType': 'application/vnd.google-apps.folder',
'parents': template_id
}
file = drive.files().get(fileId=sheet_id, fields='parents').execute()
previous_parents = ",".join(file.get('parents'))
# Move the file to the new folder
file = drive.files().update(fileId=sheet_id,
addParents=folder_id,
removeParents=previous_parents,
fields='id, parents').execute()
with open(log_gdrive, mode='a+', encoding='utf8') as file:
file.write(sheet_id)
# CREATE FROM TEMPLATE
sheet = gs.open_by_key(sheet_id)
worksheet = None
worksheets = sheet.worksheets()
sheet_range = ''
if self.report_type == 'dash':
try:
ws_index = 0
for ws in worksheets:
if ws.title == 'DASHBOARD':
sheet.del_worksheet(ws)
ts = gs.open('REPORT TEMPLATE').get_worksheet(ws_index).copy_to(spreadsheet_id=sheet_id)
worksheets = sheet.worksheets()
mysheet = worksheets.__getitem__(worksheets.__len__() - 1)
mysheet.update_title('DASHBOARD')
mysheet.update_index(ws_index)
worksheet = sheet.get_worksheet(ws_index)
sheet_range = 'DASHBOARD!A4:Z1000'
except Exception as e:
print(e.__str__())
if self.report_type == 'axe_c_summary':
ws_index = 2
for ws in worksheets:
if ws.title == 'AXE':
sheet.del_worksheet(ws)
ts = gs.open('REPORT TEMPLATE').get_worksheet(ws_index).copy_to(spreadsheet_id=sheet_id)
worksheets = sheet.worksheets()
mysheet = worksheets.__getitem__(worksheets.__len__() - 1)
mysheet.update_title('AXE')
mysheet.update_index(ws_index)
worksheet = sheet.get_worksheet(ws_index)
sheet_range = 'AXE!A10:Z1000'
if self.report_type == 'lighthouse':
ws_index = 3
for ws in worksheets:
if ws.title == 'LIGHTHOUSE':
sheet.del_worksheet(ws)
ts = gs.open('REPORT TEMPLATE').get_worksheet(ws_index).copy_to(spreadsheet_id=sheet_id)
worksheets = sheet.worksheets()
mysheet = worksheets.__getitem__(worksheets.__len__() - 1)
mysheet.update_title('LIGHTHOUSE')
mysheet.update_index(ws_index)
worksheet = sheet.get_worksheet(ws_index)
sheet_range = 'LIGHTHOUSE!A10:Z1000'
if self.report_type == 'pdf_internal':
ws_index = 4
for ws in worksheets:
if ws.title == 'PDF INTERNAL':
sheet.del_worksheet(ws)
ts = gs.open('REPORT TEMPLATE').get_worksheet(ws_index).copy_to(spreadsheet_id=sheet_id)
worksheets = sheet.worksheets()
mysheet = worksheets.__getitem__(worksheets.__len__() - 1)
mysheet.update_title('PDF INTERNAL')
mysheet.update_index(ws_index)
worksheet = sheet.get_worksheet(ws_index)
sheet_range = worksheet.title
if self.report_type == 'pdf_external':
ws_index = 5
for ws in worksheets:
if ws.title == 'PDF EXTERNAL':
sheet.del_worksheet(ws)
ts = gs.open('REPORT TEMPLATE').get_worksheet(ws_index).copy_to(spreadsheet_id=sheet_id)
worksheets = sheet.worksheets()
mysheet = worksheets.__getitem__(worksheets.__len__() - 1)
mysheet.update_title('PDF EXTERNAL')
mysheet.update_index(ws_index)
worksheet = sheet.get_worksheet(ws_index)
sheet_range = worksheet.title
if worksheet:
value_input_option = 'USER_ENTERED'
# values = [self.data]
values = []
for row in self.data:
value = ''
if self.report_type.find('pdf') == 0:
value = [row.errors, row.title.replace('`', '').replace('"', ''),
row.description.replace('`', '').replace('"', '')]
values.append(value)
elif self.report_type == 'dash':
value = [row.metric, row.url_count, row.percentage, row.urls_total,
row.description.replace('`', '').replace('"', '')]
values.append(value)
elif self.report_type == 'axe_c':
value = [row.url, row.error_count, row.error_description.replace('`', '').replace('"', '')]
values.append(value)
else:
value = [row.error_count, row.error.replace('`', '').replace('"', ''),
row.error_description.replace('`', '').replace('"', '')]
values.append(value)
body = {'values': values}
sheets = build('sheets', 'v4', credentials=self.get_creds(SCOPES_SHEETS))
clear = sheets.spreadsheets().values().clear(
spreadsheetId=sheet.id, range=sheet_range, body={}).execute()
result = sheets.spreadsheets().values().update(
spreadsheetId=sheet.id, range=sheet_range,
valueInputOption=value_input_option, body=body).execute()
# Read CSV file contents
print('{0} cells updated.'.format(result.get('updatedCells')))
try:
filepath = os.path.join(self.report_path, 'AXE', 'Chrome', 'AXEChrome_REPORT.csv')
self.paste_csv(filepath, sheet, 'AXE DATA (CHROME)!A1')
except Exception as e:
print(e.__str__())
try:
filepath = os.path.join(self.report_path, 'AXE', 'Firefox', 'AXEFirefox_REPORT.csv')
self.paste_csv(filepath, sheet, 'AXE DATA (FIREFOX)!A1')
except Exception as e:
print(e.__str__())
try:
filepath = os.path.join(self.report_path, 'LIGHTHOUSE', 'LIGHTHOUSE_REPORT.csv')
self.paste_csv(filepath, sheet, 'LIGHTHOUSE DATA!A1')
except Exception as e:
print(e.__str__())
try:
filepath = os.path.join(self.report_path, 'PDF', 'internal_pdf_a.csv')
self.paste_csv(filepath, sheet, 'PDF INTERNAL DATA!A1')
except Exception as e:
print(e.__str__())
try:
filepath = os.path.join(self.report_path, 'PDF', 'external_pdf_a.csv')
self.paste_csv(filepath, sheet, 'PDF EXTERNAL DATA!A1')
except Exception as e:
print(e.__str__())
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))