Ejemplo n.º 1
0
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)
Ejemplo n.º 2
0
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)
Ejemplo n.º 3
0
	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")
Ejemplo n.º 4
0
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
Ejemplo n.º 5
0
    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)
Ejemplo n.º 6
0
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
Ejemplo n.º 8
0
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
Ejemplo n.º 9
0
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)
Ejemplo n.º 10
0
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)
Ejemplo n.º 11
0
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)
Ejemplo n.º 12
0
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
Ejemplo n.º 13
0
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)
Ejemplo n.º 14
0
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
Ejemplo n.º 15
0
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)
Ejemplo n.º 17
0
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('-------')
Ejemplo n.º 18
0
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
Ejemplo n.º 19
0
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
Ejemplo n.º 20
0
 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
Ejemplo n.º 21
0
 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))
Ejemplo n.º 23
0
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
Ejemplo n.º 24
0
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
Ejemplo n.º 25
0
 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
Ejemplo n.º 26
0
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
Ejemplo n.º 27
0
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
Ejemplo n.º 29
0
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
Ejemplo n.º 30
0
    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
Ejemplo n.º 31
0
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
Ejemplo n.º 32
0
    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
Ejemplo n.º 33
0
def auth_gss_client(path, scopes):
    credentials = ServiceAccountCredentials.from_json_keyfile_name(
        path, scopes)
    return gspread.authorize(credentials)
Ejemplo n.º 34
0
        # 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
Ejemplo n.º 35
0
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)
Ejemplo n.º 36
0
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
Ejemplo n.º 37
0
 def __init__(self, credentials):
     self.conn = gspread.authorize(credentials)
     self.personnel = self.getdb()
     self.schedule = self.getschedule()
     self.sanitize = re.compile(r'[^\d]+')
Ejemplo n.º 38
0
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+'))
Ejemplo n.º 39
0
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)
Ejemplo n.º 40
0
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)
Ejemplo n.º 41
0
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)
Ejemplo n.º 42
0
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)
Ejemplo n.º 43
0
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
Ejemplo n.º 45
0
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")
Ejemplo n.º 46
0
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"
Ejemplo n.º 49
0
# -*- 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列
Ejemplo n.º 50
0
def context(credentials):
    return gspread.authorize(credentials)
Ejemplo n.º 51
0
 def _authorize_credentials(self):
     return gspread.authorize(self.CREDENTIALS)
Ejemplo n.º 52
0
 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")
Ejemplo n.º 53
0
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(",")
Ejemplo n.º 54
0
 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)
Ejemplo n.º 55
0
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
Ejemplo n.º 56
0
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)
Ejemplo n.º 57
0
    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__())
Ejemplo n.º 58
0
    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))