def read_invoices():
"""
Reads Google Spreadsheet with next actions
Returns a list of dictionaries, where a dictionary is a record read from the spreadsheet
Created for test purposes, not intended to be used in production
"""
try:
gc = gspread.login(gs_config["email"], gs_config["password"])
sh = gc.open_by_key("1G5ispFcCHw3ejVitV91xmdLFXhKOjn2E8JQ8fSmMCg0")
ws = sh.get_worksheet(0)
rows = ws.get_all_values()
cols = [col.replace(" ", "").strip().lower() for col in rows.pop(0)]
data = [{k: v for k, v in zip(cols, row)} for row in rows]
action_rows = [row for row in data if len(row["customeremail"].strip()) and len(row["invoicecounter"].strip())]
print len(action_rows)
# if col in ultradox_cols
except httplib.CannotSendRequest:
print "HTTP Error while reading invoice data from Google spreadsheet"
raise
except httplib.BadStatusLine:
print "HTTP Error while reading invoice data from Google spreadsheet"
raise
return [action_rows[1]]
def initDataFromGDocs():
print 'Fetching data from Google Docs...'
global FIRST_NAME
global LAST_NAME
global DATE
global STREET
global CITY
global STATE
global ZIP
global PHONE
global EMAIL
# Login with your Google account
# I know I should NEVER do this like this. But this is a hackathon...
gc = gspread.login(DOCS_EMAIL, DOCS_PASSWORD)
# Open a worksheet from spreadsheet with one shot
wks = gc.open("GovSafeData").sheet1
# Populate
FIRST_NAME = wks.acell('A2').value
LAST_NAME = wks.acell('B2').value
DATE = wks.acell('C2').value
STREET = wks.acell('D2').value
CITY = wks.acell('E2').value
STATE = wks.acell('F2').value
ZIP = wks.acell('G2').value
PHONE = wks.acell('H2').value
EMAIL = wks.acell('I2').value
def cxn():
global gc
global sh
global out
gc = gspread.login(os.environ['gmail_add'],os.environ['gmail_pw'])
sh = gc.open('nepal test')
out = sh.worksheet('Aggregated')
def test_contact_key_creator(self):
self.logger.info('Starting test_contact_key_creator')
#setup the doc on gdrive first
file_name = 'ContactKeyTest1'
ss_key = gdoc_util.upload_file_to_gdrive('contact_key_test1.tsv', file_name)
driver = self.driver
gdoc_util.login_gdrive(driver)
driver.get('%s%s' % (self.base_url, '?ss=' + ss_key))
gc = gspread.login(settings.DEFAULT_GDRIVE_EMAIL, settings.DEFAULT_GDRIVE_PW)
my_worksheet = gc.open_by_key(ss_key).sheet1
e2_val = my_worksheet.acell('E2')
self.logger.info('e2_val: %s' % e2_val)
#reset the cell
my_worksheet.update_acell('E2', '')
e2_val = my_worksheet.acell('E2')
self.logger.info('e2_val reset to: %s' %e2_val)
#now run the command
#switch to input form frame
driver.switch_to.frame(0)
WebDriverWait(driver, 10).until(
EC.presence_of_element_located((By.LINK_TEXT, "Hiplead"))
).click()
id_worksheet_name_INPUT = WebDriverWait(driver, 10).until(
EC.presence_of_element_located((By.ID, "id_worksheet_name"))
)
id_worksheet_name_INPUT.clear()
id_worksheet_name_INPUT.send_keys(file_name)
Select(driver.find_element_by_id("id_scrapers")).select_by_value('contactKeyCreator')
driver.find_element_by_id("id_email").send_keys(settings.DEFAULT_GDRIVE_EMAIL)
driver.find_element_by_id("id_password").send_keys(settings.DEFAULT_GDRIVE_PW)
#ok, now submit the form
id_worksheet_name_INPUT.submit()
#then wait for task to complete
#this success alert only becomes visible when task is actually finished.
success_div = driver.find_element_by_class_name('time_remaining')
try:
WebDriverWait(driver, 10).until(
EC.visibility_of(success_div)
)
except StaleElementReferenceException as e:
#TODO The javascript DOM manipulation that results in StaleElementReferenceException needs to be resolved.
success_div = driver.find_element_by_class_name('time_remaining')
WebDriverWait(driver, 10).until(
EC.visibility_of(success_div)
)
#now validate cell value, since we know task has completed.
e2_val = my_worksheet.acell('E2')
self.logger.info('e2_val after test: %s' %e2_val)
self.assertEquals('john_franklin_smith_somedomain.net', e2_val.value)
self.logger.info( 'Finished test_contact_key_creator')
def authenticate(self, email, password):
# Warning: ClientLogin is deprecated since April 20, 2015.
# Should migrate to OAuth2 authentication.
# These should be placed in the environment variables.
auth = gspread.login(email, password)
return auth
def update_spreadsheet(key, worksheet, data, chunk_size=200):
gsclient = gspread.login(app_engine_username, app_engine_password)
wr = gsclient.open_by_key(key).worksheet(worksheet)
print "updating %s:" % (wr.title)
row_generator = iter(data)
def get_chunk():
for i, line in enumerate(row_generator):
yield line
if i >= chunk_size:
break
total_rows = 0
current_row = 1
while True:
rows = list(get_chunk())
if not rows:
break
ncols = len(rows[0])
total_rows += len(rows)
wr.resize(total_rows, ncols)
addr = "%s:%s" % (wr.get_addr_int(
current_row, 1), wr.get_addr_int(total_rows, ncols))
cells = wr.range(addr)
for cell in cells:
cell.value = rows[cell.row - current_row][cell.col - 1]
print "updating %s cells (%s)..." % (len(cells), addr)
wr.update_cells(cells)
current_row += len(rows)
def getWorksheet():
if (os.system("ping -c 4 192.168.1.1") == 0):
try:
logging.warning ("getWorksheet: Try login")
gc = gspread.login(email, password)
except:
logging.error ("getWorksheet: Unable to login")
return 0
else:
try:
logging.warning("getWorksheet: Try open spreadsheet")
spreadSheet = gc.open(spreadsheetName)
except:
logging.error("getWorksheet: Unable to open spread sheet: %s" % spreadsheetName)
return 0
else:
logging.warning("getWorksheet: Open spredsheet succesfully")
try:
workSheet = spreadSheet.get_worksheet(8)
except:
logging.error("getWorksheet: Unable to get worksheet")
return 0
else:
return workSheet
else:
logging.warning("getWorksheet: No network connection")
return 0
def write_to_gdocs(temp, ext_temp, humidity):
"""write our data to a google spreadsheet"""
# Account details for google docs
email = '*****@*****.**'
password = '******'
spreadsheet = 'pitemp'
# Login with your Google account
try:
gc = gspread.login(email, password)
except:
print "Unable to log in. Check your email address/password"
sys.exit()
# Open a worksheet from your spreadsheet using the filename
try:
worksheet = gc.open(spreadsheet).sheet1
except:
print "Unable to open the spreadsheet. \
Check your filename: %s" % spreadsheet
sys.exit()
try:
values = [datetime.datetime.now(),
temp,
ext_temp,
humidity]
worksheet.append_row(values)
#success!
print "Wrote a row to %s at %s" % (spreadsheet,
datetime.datetime.now())
except:
print "Unable to append data. Check your connection?"
def create_google():
''' create a google spreadsheet if doesn't exists
copied from http://diorsman.me/2013/11/05/create-new-google-spreadsheet-in-python/ '''
import gspread
import gdata.docs.client
import getpass
# if using in batch these 3 needs to be given not interactively
email = raw_input("Google drive account: ")
password = getpass.getpass("Google drive password: "******"Spreadsheet name: ")
try:
gc = gspread.login(email, password)
except BadAuthentication:
print "Username or password are wrong"
sys.exit()
except NotVerified:
print "Account not verified, user need to acces Google account directly first"
sys.exit()
except ServiceUnavailable:
print "Service unavailable, try again later"
sys.exit()
#Open Spreadsheet, if doesn't exist then create a new one
try:
spr = gc.open(gname)
print "Spreadsheet exists, it will be overwritten: ", gname
except:
source = 'CMIP5 Spreadsheet'
gd_client = gdata.docs.client.DocsClient()
gd_client.ClientLogin(email, password, source)
document = gdata.docs.data.Resource(type='spreadsheet', title=gname)
document = gd_client.CreateResource(document)
print 'Created Spreadsheet: '+ gname
spr = gc.open(gname)
return spr,gc,gname
def abrir_planilha_cursos():
prints("abrir_planilha_cursos")
teste_de_conexao(True)
global cliente, planilha_cursos, banco_de_dados, menu, msg
msg.put(["Abrindo", "Planilhas cursos"])
try:
cliente = gspread.login(Sis_M, Sis_S)
except:
msg.put(["Erro", "Erro ao fazer login"])
menu = INICIO
tecla = ler_teclas()
else:
try:
planilha_cursos = cliente.open_by_key(KEY_CURSOS).get_worksheet(0)
except:
print("Erro ao abrir planilha dos cursos. Key = ", KEY_CURSOS)
msg.put(["Erro", "Erro ao abrir planilha dos cursos"])
menu = INICIO
tecla = ler_teclas()
try:
banco_de_dados = cliente.open_by_key(KEY_BD).get_worksheet(0)
except:
print("Erro ao abrir banco de dados. Key = ", KEY_BD)
msg.put(["Erro", "Erro ao abrir banco de dados"])
menu = INICIO
tecla = ler_teclas()
msg.put(["Verificando", "Cursos"])
verifica_cursos()
def _getGspreadClient(self):
try:
return gspread.login(self.email, self.password)
except gspread.exceptions.AuthenticationError as err:
#needed as gspread lib doesnt properly handle errors yet
raise WrapperError("Incorrect authentication")
def download(self, key, datarange, init_date = None, map_meaningful = None):
# print('key', key)
gc = gspread.login(self.user, self.password)
if isinstance(init_date, date):
pass
elif isinstance(init_date, dict):
init_date = datetime(tzinfo = TZI, **init_date).date()
elif isinstance(init_date, str):
init_date = date.strptime(init_date, "%d %b %y") # 26 nov 12
else:
init_date = self._get_init_date(gc, key)
def datasize(drange):
start, end = drange.split(':')
letters = lambda x, boo: ''.join([ z for z in x if z.isdigit()!=boo ])
start_letters = letters(start,1)
start_digits = letters(start,0)
end_letters = letters(end,1)
end_digits = letters(end,0)
col_names = list(map(str.strip,map(''.join,itertools.combinations_with_replacement(' ABCDEFGHIJKLMNOPQRSTUVWXYZ', 2))))[1:]
x_size = col_names.index(end_letters) - col_names.index(start_letters)
y_size = int(end_digits) - int(start_digits)
return x_size + 1, y_size + 1 # +1 because bothe start and end index are part of the range
# wks = gc.open("schedule").get_worksheet(1)
wks = gc.open_by_key(key).get_worksheet(1)
sheet_data = wks.range(datarange)
# print('sheet data', sheet_data)
results = self._parse_shifts(init_date, sheet_data, *datasize(datarange), map_meaningful=map_meaningful)
return results
def updateSheet():
UN = str(raw_input("Enter Google UserName: "******"Enter Google Password: "******"Conn_spreadsheet_catmaid").sheet1
usedSkels = worksheet.col_values(1)
c = catmaid.Connection('http://catmaid.hms.harvard.edu', 'thomas.lo',
'asdfjkl;', 'Validation Tracing DR5_7L')
Conn_Load = loadmat("cons_to_seed.mat")
loaded = np.asarray(Conn_Load['cons_to_seed'])
wd = c.wiring_diagram()
for edge in wd['data']['edges']:
if ((edge['target'] not in usedSkels)
or (edge['source'] not in usedSkels)):
rows = rowMake(c, 1, [edge['source'], edge['target']], loaded)
if edge['source'] not in usedSkels:
n = len(worksheet.col_values(1))
rows[0][
22] = '=IF(Z{0}="UHOH","uhoh",IF(C{0}="","Ready",IF(I{0}="","In progress",IF(K{0}="","needs Review",IF(U{0}="","In progress","Reviewed")))))'.format(
n + 1)
worksheet.append_row(rows[0])
if edge['target'] not in usedSkels:
n = len(worksheet.col_values(1))
rows[1][
22] = '=IF(Z{0}="UHOH","uhoh",IF(C{0}="","Ready",IF(I{0}="","In progress",IF(K{0}="","needs Review",IF(U{0}="","In progress","Reviewed")))))'.format(
n + 1)
worksheet.append_row(rows[1])
def optout_users():
""" Retrieve opt-out users from Google Sheets, load them to an opt-out table, and log the batch when done """
# Login with your Google account
gc = gspread.login('*****@*****.**','password')
spreadsheet = gc.open(datasheets["OptOuts"])
worksheet = spreadsheet.worksheet("OptOuts")
# Retrieve all signups as a list of lists
optouts = worksheet.get_all_values()
# Trim off all the Google Spreadsheet reporting header records
optouts = optouts[15:]
optout_count = len(optouts)
optouts_clean = []
for optout in optouts:
optouts_clean.append(optout[0:5])
if (optout_count > 0):
# Set up database connection
db_connection = pyodbc.connect(**thinmint_db)
db_cursor = db_connection.cursor()
# Load all new signups to database
db_cursor.executemany("execute thinmint.dbo.OptOutUser ?,?,?,?,?", optouts_clean)
db_cursor.commit()
db_connection.close()
log_me(datasheets["OptOuts"], 'dbo.OptOuts', 'Success', str(optout_count))
def read_spreadsheet(google_url):
gc = gspread.login(sys.argv[1], sys.argv[2])
google_spread = gc.open("Books to Read")
# print 'after google_spread'
google_worksheet = google_spread.get_worksheet("To Read")
col_titles = google_worksheet.col_values(1) # Does gspread do 1 or 0?
print col_titles
def getCSV(self):
"""
Returns
-------
filename: str
"""
import getpass
import gspread
user = raw_input("Insert Google username:"******"Insert password:"******"SpreadSheet filename on Drive:")
sheet = raw_input("Sheet name (first sheet is default):")
cl = gspread.login(user, password)
sh = cl.open(name)
if not (sheet.strip()):
ws = sh.sheet1
sheet = "1"
else:
ws = sh.worksheet(sheet)
filename = name + '-worksheet_' + sheet + '.csv'
with open(filename, 'wb') as f:
writer = UnicodeWriter(f)
writer.writerows(ws.get_all_values())
return filename
def start():
print 'Convert CSV to JSON'
user = raw_input("Gmail user: "******"Gmail Password: "******"Spreadsheet url: ")
sh = gc.open_by_url(docurl) #apertura del documento / Open Doc.
worksheet = sh.worksheet(
"Single Neurons") #Seleccion de la hoja / Sheet selection.
print 'Taking values...'
#Toma los valores de las celdas / Take value of celds.
colb = worksheet.col_values(2)
cold = worksheet.col_values(4)
colf = worksheet.col_values(6)
#Segundo paso, datos convertidos / Second Step, converted data.
print 'Converting values to JSON...'
celd = 2
celegans = open('celegans.json', 'w')
head = "{\n" "\"name\":\"NeuroNetwork\",\n\"children\": [\n"
celegans.write(head)
for i in range(301):
chld = "{\n\"name\":\"" + colb[
celd] + "\",\n\"children\": [\n{\"name\": \"" + str(
cold[celd]) + "\", \"size\": 3000},\n{\"name\": \"" + str(
colf[celd]) + "\", \"size\": 3000}\n]\n},\n"
celd += 1
celegans.write(chld)
celegans.write("]\n}")
celegans.close()
print "\nWORKS!, celegans.json was generated!\n"
def exportToGoogleSpreadSheet(self, username, password, sheetname):
# Login with your Google account
gc = gspread.login(username, password)
# Open a worksheet from spreadsheet with one shot
sh = gc.open(sheetname)
wks = sh.worksheet(self.comboBox_Grouping.currentText())
for i, vhead in enumerate(self.tablemodel.v_header):
wks.update_cell(i+2, 1, vhead)
for j, hhead in enumerate(self.tablemodel.h_header):
wks.update_cell(1, j+2, hhead)
d = self.tablemodel.index(i, j).data(Qt.DisplayRole).toPyObject()
if d:
cell_data = r'=IMAGE("%s", 4, %s, %s)' % (quote(self._card_database.findByName(d)[0].imageURL), CARD_HEIGHT, CARD_WIDTH)
else:
cell_data = r''
wks.update_cell(i+2, j+2, cell_data)
# Fetch a cell range
#cell_list = wks.range('A1:B7')
#print cell_list
print("DONE!")
def read_spreadsheet(google_url):
gc = gspread.login(sys.argv[1], sys.argv[2])
google_spread = gc.open("Books to Read")
# print 'after google_spread'
google_worksheet = google_spread.get_worksheet("To Read")
col_titles = google_worksheet.col_values(1) # Does gspread do 1 or 0?
print col_titles
def _fetch_responses(self, update):
self._clear()
self._print('\t'+self._uname+'\n')
if update:
if self._password:
passwd = self._password
else:
passwd = getpass.getpass()
try:
self._print('Retrieving spreadsheet from Google Drive...')
gc = gspread.login(self._uname, passwd)
responses = gc.open_by_key(self._docKey)
sheet = responses.get_worksheet(0)
copy = sheet.get_all_values()
with open('reflections.csv', 'w') as f:
csv.writer(f).writerows(copy)
except:
self._print('Failed! Loading saved csv...')
with open('reflections.csv', 'r') as f:
copy = list(csv.reader(f))
else:
self._print('Working off saved csv...')
with open('reflections.csv', 'r') as f:
copy = list(csv.reader(f))
return copy
def parse_data(raw):
if len(raw) != 24:
raise DadosErradosDoPortao # vê se pegou a string certa do lugar certo
parts = raw.strip().split(',')
if len(parts) != 4:
raise DadosErradosDoPortao
#
# pega partes da string
pw_pos = parts[0][1:]
code = parts[1]
date = datetime.datetime.strptime(parts[2], '%d/%m/%y')
time = datetime.datetime.strptime(parts[3], '%H:%M')
hora_lida = date.replace(minute=time.minute, hour=time.hour)
# pegando dados da planilha
quarto = nome = ''
try:
gc = gspread.login(settings.googlelogin, settings.googlepassword)
wks = gc.open('senhas').sheet1
cellpos = wks.find(pw_pos)
nome = wks.cell(cellpos.row, 4).value
quarto = wks.cell(cellpos.row, 3).value
except:
print 'erro ao buscar dados da planilha: %s\nraw: %s' % (query, raw)
# salva
return {
'nome': nome,
'quarto': quarto,
'hora_lida': hora_lida.isoformat(),
'created_at': datetime.datetime.now().isoformat(),
'raw': raw,
'event': event[code]
}
def get_financial_data_and_upload(spreadsheet_name):
print pyCol.COL("Type your google ID (e.g. [email protected]) then press ", "header") + pyCol.COL('<Enter>: ', 'green')
email = raw_input()
print
pwd = getpass.getpass(
pyCol.COL('Type password then press ',
'header') + pyCol.COL('<Enter>: ',
'green')) # command line prompt without echo
print
gc = gspread.login(str(email), pwd)
print pyCol.COL('Type Company Symbol (e.g: ORCL:NYQ) then press ', 'header') + pyCol.COL('<Enter>: ', 'green')
company_name = raw_input()
# company_name = 'ORCL:NYQ'
print pyCol.COL('Data for company ' + company_name + ' will be downloaded', 'blue')
print
financial_category_list = ["BalanceSheet", "CashFlow", "IncomeStatement"]
for financial_category in financial_category_list:
print pyCol.COL('Updating the ' + financial_category + ' category', 'blue')
d_result_ordered = get_financial_data(financial_category, company_name)
upload_financial_data(
financial_category,
spreadsheet_name,
gc,
d_result_ordered)
def main():
google_account = gspread.login(os.environ['gmail'], os.environ['password'])
alumni_spreadsheet_url = os.environ['alumni_url']
student_spreadsheet_url = os.environ['student_url']
alumni = get_alumni(google_account, alumni_spreadsheet_url)
students = get_students(google_account, student_spreadsheet_url)
matches = match_up(students, alumni)
def getWorksheet(key, wsNumOrTitle):
gc = gspread.login(USERNAME, PASSWORD)
sh = gc.open_by_key(key)
if type(wsNumOrTitle) == int:
return sh.get_worksheet(wsNumOrTitle)
else:
return sh.worksheet(wsNumOrTitle)
def gSpread(self):
"""
These are used for login information with Google Spread sheet, create a "password.txt" file and type your password in there for authentication
to Google. Be sure to remember to add "password.txt" to your ".gitignore" file if you are using Git/GitHub so that you don't post your password
to the entire world.
"""
email = '*****@*****.**'
password = linestring = open('password.txt', 'r').read()
spreadsheet = 'Computer Science Lab Temperature'
# Login with your Google account
try:
gc = gspread.login(email, password)
print "try gSpread"
except:
print "Unable to log in. Check your email address/password"
sys.exit()
# Open a worksheet from your spreadsheet using the filename
try:
worksheet = gc.open(spreadsheet).sheet1
# Alternatively, open a spreadsheet using the spreadsheet's key
# worksheet = gc.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE')
except:
print "Unable to open the spreadsheet. Check your filename: %s" % spreadsheet
sys.exit()
def resign_users():
""" Retrieve resigned users from Google Sheets, load them to an resigns table, and log the batch when done """
# Login with your Google account
gc = gspread.login('*****@*****.**','password')
spreadsheet = gc.open(datasheets["Resigns"])
worksheet = spreadsheet.sheet1
# Retrieve all signups as a list of lists
resigns = worksheet.get_all_values()
# Remove the Header record
if (resigns[0][0] == "Submitted On"):
resigns.pop(0)
resigns_count = len(resigns)
if (resigns_count > 0):
for record in resigns:
resigns[2] = resigns[2][0:min(len(resigns[2]),250)]
# Set up database connection
db_connection = pyodbc.connect(**thinmint_db)
db_cursor = db_connection.cursor()
# Load all new signups to database
db_cursor.executemany("execute thinmint.dbo.ResignUser ?,?,?", resigns)
db_cursor.commit()
db_connection.close()
log_me(datasheets["Resigns"], 'dbo.Resigns', 'Success', str(resigns_count))
def index(self):
gc = gspread.login('*****@*****.**', self.password)
spreadsheet = gc.open_by_key(
'14Yb7BQ5PtczaX9z7b2YIig_xFZgI7Ox8kPA_PNRhXB4')
ldap = 'thomasl'
worksheet = [
x for x in spreadsheet.worksheet('Individuals').get_all_values()
if x[0] == ldap
]
if len(worksheet) == 0:
return None
else:
group_name = worksheet[0][1]
token_count = worksheet[0][2]
spreadsheet_data = [
ListMethods.strip(y) for y in ListMethods.split(
spreadsheet.worksheet(group_name).get_all_values())
]
return {
'bucketData': {
'user':
ldap,
'tokenCount':
token_count,
'buckets': [[[TokensMethods.parse(z) for z in y] for y in x]
for x in spreadsheet_data]
}
}
def update_monthly_games_played():
gc = gspread.login('*****@*****.**', 'muatkienjwxfpnxn')
player_cell_locations = {'thewarmth00':'B51',
'rob_chainsaw':'B53',
'mashley93':'B55',
'peachy36west':'B57',
'm_sibs':'B59',
'rc_van':'B61',
'soviet_canuck':'B63',
'undertheblanket':'B65',
'vsmewen':'B67',
'hokagesama1':'B69',
'lnferno31':'H86'}
sheet = gc.open_by_key('0Ak-m4uT6aXL1dExuUHdLV0x2aTNFSGNRMTV2WWdLX2c').get_worksheet(9)
session = Session()
our_players = session.query(Player).all()
for player in our_players:
cell_location = player_cell_locations[player.username.lower()]
games_played = len(session.query(GamePlayed).filter_by(player_id=player.id).all())
value = sheet.acell(cell_location).value
sheet.update_acell(cell_location, str(games_played))
if sheet.acell(cell_location).value != value:
pass
else:
pass
def setUp(self):
creds_filename = "tests.config"
try:
config_filename = os.path.join(
os.path.dirname(__file__), creds_filename)
config = ConfigParser.ConfigParser()
config.readfp(open(config_filename))
self.config = config
auth_type = config.get('Google Account', 'auth_type')
if auth_type == 'OAuth':
#
key_ring = {}
key_ring['grant_type'] = 'refresh_token'
key_ring['refresh_token'] = config.get('Google Account', 'refresh_token')
key_ring['client_secret'] = config.get('Google Account', 'client_secret')
key_ring['client_id'] = config.get('Google Account', 'client_id')
#
access_token = config.get('Google Account', 'access_token')
#
self.gc = gspread.authorize(access_token, key_ring)
else:
#
email = config.get('Google Account', 'email')
password = config.get('Google Account', 'password')
self.gc = gspread.login(email, password)
self.assertTrue(isinstance(self.gc, gspread.Client))
except IOError:
msg = "Can't find %s for reading google account credentials. " \
"You can create it from %s.example in tests/ directory."
raise Exception(msg % (creds_filename, creds_filename))
def listLearningzzz():
if not os.path.isfile("config.ini"):
return "No config file set. FATAL"
config = ConfigParser.RawConfigParser()
config.read("config.ini")
username = config.get("user_details","username")
password = config.get("user_details","password")
spreadsheetKey = config.get("spreadsheet_details","key")
worksheetNum = config.getint("spreadsheet_details","worksheet")
gc = gspread.login(username,password)
sheet = gc.open_by_key(spreadsheetKey).get_worksheet(worksheetNum)
topicsList = sheet.col_values(2)[1:]
linksList = sheet.col_values(3)[1:]
topicsLinksList = []
for topic, link in zip(topicsList,linksList):
if link is None:
link = "#"
obj = {}
obj["topic"] = topic
obj["link"] = link
topicsLinksList.append(obj)
return render_template('learningzzz.html',topicsLinksList=topicsLinksList)
def read_worksheet(): global app gc = gspread.login(app.config['GOOGLE_EMAIL'], app.config['GOOGLE_PASSWORD']) sht1 = gc.open_by_key(app.config['GOOGLE_DRIVE_SHEET_ID']) worksheet = sht1.get_worksheet(0) return worksheet
def write_items():
nowstr = datetime.now().strftime('%Y/%m/%d %H:%M:%S')
print 'writing to google spreadsheet'
gapp = gspread.login('*****@*****.**', GPASS)
doc = gapp.open('Ruten Diesel Tracker')
sht = doc.add_worksheet(nowstr, 1, len(TITLES))
title_cells = sht.range('A1:%s1' % chr(ord('A') + len(TITLES) - 1))
for i in range(len(TITLES)):
title_cells[i].value = TITLES[i]
sht.update_cells(title_cells)
for i in ITEMS:
print 'writing: %s' % i
row = [
i['style'],
i['wash'],
i['wsize'],
i['lsize'],
i['available'],
i['backwidth'],
i['frontwidth'],
i['bottomheight'],
i['bottomwidth'],
i['biglegwidth'],
i['footwidth'],
i['outerlength'],
i['innerlegth'],
i['url'],
]
sht.append_row(row)
def updateSheet():
UN = str(raw_input("Enter Google UserName: "******"Enter Google Password: "******"Conn_spreadsheet_catmaid").sheet1
usedSkels = worksheet.col_values(1)
c = catmaid.Connection('http://catmaid.hms.harvard.edu',
'thomas.lo',
'asdfjkl;',
'Validation Tracing DR5_7L')
Conn_Load = loadmat("cons_to_seed.mat")
loaded = np.asarray(Conn_Load['cons_to_seed'])
wd = c.wiring_diagram()
for edge in wd['data']['edges']:
if ((edge['target'] not in usedSkels) or (edge['source']
not in usedSkels)):
rows = rowMake(c, 1, [edge['source'], edge['target']], loaded)
if edge['source'] not in usedSkels:
n = len(worksheet.col_values(1))
rows[0][22]='=IF(Z{0}="UHOH","uhoh",IF(C{0}="","Ready",IF(I{0}="","In progress",IF(K{0}="","needs Review",IF(U{0}="","In progress","Reviewed")))))'.format(n+1)
worksheet.append_row(rows[0])
if edge['target'] not in usedSkels:
n = len(worksheet.col_values(1))
rows[1][22]='=IF(Z{0}="UHOH","uhoh",IF(C{0}="","Ready",IF(I{0}="","In progress",IF(K{0}="","needs Review",IF(U{0}="","In progress","Reviewed")))))'.format(n+1)
worksheet.append_row(rows[1])
def cxn():
global gc
global sh
global out
gc = gspread.login(os.environ['gmail_add'],os.environ['gmail_pw'])
sh = gc.open('150505 Priority VDC list with Population')
out = sh.worksheet('Aggregated')
def move_new_signups():
""" Retrieve new Signups from Google Sheets, load them to a registration database, and log the batch when done """
# Login with your Google account
gc = gspread.login('*****@*****.**','password')
spreadsheet = gc.open(datasheets["Signups"])
worksheet = spreadsheet.sheet1
# Retrieve all signups as a list of lists
new_signups = worksheet.get_all_values()
# Remove the Header record
if (new_signups[0][0] == "Submitted On"):
new_signups.pop(0)
signup_count = len(new_signups)
for record in new_signups:
record.append("website")
if (signup_count > 0):
# Set up database connection
db_connection = pyodbc.connect(**thinmint_db)
db_cursor = db_connection.cursor()
# Load all new signups to database
db_cursor.executemany("execute thinmint.dbo.AddNewUser ?,?,?,?", new_signups)
db_cursor.commit()
db_connection.close()
log_me(datasheets["Signups"], 'dbo.Users', 'Success', str(signup_count))
def read_invoices():
"""
Reads Google Spreadsheet with next actions
Returns a list of dictionaries, where a dictionary is a record read from the spreadsheet
Created for test purposes, not intended to be used in production
"""
try:
gc = gspread.login(gs_config['email'], gs_config['password'])
sh = gc.open_by_key('1G5ispFcCHw3ejVitV91xmdLFXhKOjn2E8JQ8fSmMCg0')
ws = sh.get_worksheet(0)
rows = ws.get_all_values()
cols = [col.replace(' ', '').strip().lower() for col in rows.pop(0)]
data = [{k: v for k, v in zip(cols, row)} for row in rows]
action_rows = [
row for row in data if len(row['customeremail'].strip())
and len(row['invoicecounter'].strip())
]
print len(action_rows)
#if col in ultradox_cols
except httplib.CannotSendRequest:
print "HTTP Error while reading invoice data from Google spreadsheet"
raise
except httplib.BadStatusLine:
print "HTTP Error while reading invoice data from Google spreadsheet"
raise
return [action_rows[1]]
def update_spreadsheet(key, worksheet, data, chunk_size=200):
gsclient = gspread.login(app_engine_username, app_engine_password)
wr = gsclient.open_by_key(key).worksheet(worksheet)
print "updating %s:" % (wr.title)
row_generator = iter(data)
def get_chunk():
for i, line in enumerate(row_generator):
yield line
if i>=chunk_size:
break
total_rows = 0
current_row = 1
while True:
rows = list(get_chunk())
if not rows:
break
ncols = len(rows[0])
total_rows += len(rows)
wr.resize(total_rows, ncols)
addr = "%s:%s" % (wr.get_addr_int(current_row, 1),
wr.get_addr_int(total_rows, ncols))
cells = wr.range(addr)
for cell in cells:
cell.value = rows[cell.row-current_row][cell.col-1]
print "updating %s cells (%s)..." % (len(cells), addr)
wr.update_cells(cells)
current_row += len(rows)
def _getGspreadClient(self):
try:
return gspread.login(self.email, self.password)
except gspread.exceptions.AuthenticationError as err:
#needed as gspread lib doesnt properly handle errors yet
raise WrapperError("Incorrect authentication")
def __init__(self, user_id, passwd, spreadsheet_name):
"""
Args:
user_id: Gmail user id
passwd: Gmail password
spreadsheet_name: Name of the spreadsheet containing all the subs info
"""
try:
gc = gspread.login(user_id, passwd)
sp = gc.open(spreadsheet_name)
ws = sp.sheet1
row_num = 2
while True:
curr_row = ws.row_values(row_num)
if not curr_row:
break
self.append(TeacherData(curr_row))
row_num += 1
except gspread.exceptions.AuthenticationError:
print 'Incorrect username or password'
sys.exit(1)
except gspread.exceptions.SpreadsheetNotFound:
print '\'%s\' spreadsheet not found' % spreadsheet_name
sys.exit(1)
def cxn():
gc = gspread.login(os.environ['gmail_add'], os.environ['gmail_pw'])
sh = gc.open('150505 Priority VDC list with Population')
c = sh.worksheet('eo_sandbox')
#get names of all worksheets
titles = [n.title for n in t.worksheets()]
def purchases():
def incompletePurchaseRequest():
notification = Notify(notification_type = 'error', message = 'Incomplete form')
return render_template('purchase.html', form=form, active_page='purchase', notify = notification)
form = PurchaseForm(request.form)
if request.method == 'POST' and form.validate():
gc = gspread.login('*****@*****.**','BajaBaja')
work_sheet = gc.open('Purchases').sheet1
i = 1
col_list = work_sheet.col_values(1)
for value in col_list:
if value == None:
break
i += 1
work_sheet.update_acell('A%s'%(i), form.data['name'])
work_sheet.update_acell('B%s'%(i), form.data['item'])
work_sheet.update_acell('C%s'%(i), form.data['cost'])
work_sheet.update_acell('D%s'%(i), form.data['vendor'])
work_sheet.update_acell('E%s'%(i), form.data['link'])
work_sheet.update_acell('F%s'%(i), form.data['date'])
work_sheet.update_acell('G%s'%(i), form.data['quantity'])
send_email();
return redirect(url_for('index'))
return render_template('purchase.html', form=form, active_page='purchase')
def start():
print 'Convert CSV to JSON'
user = raw_input("Gmail user: "******"Gmail Password: "******"Spreadsheet url: ")
sh = gc.open_by_url(docurl)#apertura del documento / Open Doc.
worksheet = sh.worksheet("Single Neurons")#Seleccion de la hoja / Sheet selection.
print 'Taking values...'
#Toma los valores de las celdas / Take value of celds.
colb = worksheet.col_values(2)
cold = worksheet.col_values(4)
colf = worksheet.col_values(6)
#Segundo paso, datos convertidos / Second Step, converted data.
print 'Converting values to JSON...'
celd = 2
celegans = open('celegans.json','w')
head = "{\n""\"name\":\"NeuroNetwork\",\n\"children\": [\n"
celegans.write(head)
for i in range(301):
chld = "{\n\"name\":\""+colb[celd]+"\",\n\"children\": [\n{\"name\": \""+str(cold[celd])+"\", \"size\": 3000},\n{\"name\": \""+str(colf[celd])+"\", \"size\": 3000}\n]\n},\n"
celd += 1
celegans.write(chld)
celegans.write("]\n}")
celegans.close()
print "\nWORKS!, celegans.json was generated!\n"
def get(self):
gc = gspread.login(email, password)
sht1 = gc.open_by_key(spreadsheet_key)
worksheet = sht1.get_worksheet(0)
matriz = []
num_cols = worksheet.col_count
num_fils = worksheet.row_count
#Metemos un bucle que recorra toda la hoja y vamos almacenando los datos en una matriz (array de array)
i = 1
j = 1
while i < num_fils:
matriz.append([])
while j <= num_cols:
#Recorremos el documento de forma inversa para mostrar en el blog primero los eventos mas actuales (insertados los ultimos en la hoja de calculo
val = worksheet.cell(num_fils - i + 1, j).value
matriz[i - 1].append(val)
j = j + 1
i = i + 1
j = 1
# Ahora devolvemos la matriz al html, donde con js la cogeremos y la iremos formateando para mostrar los eventos en el blog
template_values = {
'matriz': matriz,
'num_fils': num_fils,
'num_cols': num_cols
}
template = JINJA_ENVIRONMENT.get_template(
'templates/MAIN_PAGE_HTML_BOOT.html')
self.response.write(template.render(template_values))
def upload_grades():
try: import gspread
except ImportError:
print "Install gpsread library (on github @ https://github.com/burnash/gspread)"
print "Note - library is super easy to install!"
return
user = get_credentials()
gc = gspread.login(user[0],user[1])
spreadsheet = gc.open(spreadsheet_name)
worksheet = spreadsheet.get_worksheet(0)
grades = []
for name, email in students:
if os.path.exists(email[0]):
lines = [line.strip() for line in open(email[0]+"/grade.txt",'r')]
for line in lines:
if line.find("Grade:") == 0:
grade = line.lstrip("Grade:")
words = grade.rsplit('/');
grades.append(words[0])
else:
print "ERROR! Student: "+email[0]+" Not Found!\n"
print "grades not uploaded..."
return
col = worksheet.find(column_name)
i = 2
for g in grades:
worksheet.update_cell(i,col.col, g)
i += 1
print "Grades have been uploaded to spreadsheet: "+spreadsheet_name
return
def getCSV(self):
"""
Returns
-------
filename: str
"""
import getpass
import gspread
user = raw_input("Insert Google username:"******"Insert password:"******"SpreadSheet filename on Drive:")
sheet = raw_input("Sheet name (first sheet is default):")
cl = gspread.login(user, password)
sh = cl.open(name)
if not (sheet.strip()):
ws = sh.sheet1
sheet = "1"
else:
ws = sh.worksheet(sheet)
filename = name + '-worksheet_' + sheet + '.csv'
with open(filename, 'wb') as f:
writer = UnicodeWriter(f)
writer.writerows(ws.get_all_values())
return filename
def get(self):
gc = gspread.login(email, password)
sht1 = gc.open_by_key(spreadsheet_key)
worksheet = sht1.get_worksheet(0)
val = worksheet.cell(4, 1).value
val1 = worksheet.row_count
self.response.write(val1)
def insert_into_google_docs(username, password, docname, data):
"""
Update the db.
"""
# connect
gc = gspread.login(username + '@gmail.com', password)
ws = gc.open(docname).sheet1
# flattened list
keys = ['name', 'email', 'last-presented', 'gone-until', 'going-on']
db = [keys]
Nkeys = len(keys)
Nrows = len(data[keys[0]])
for i in range(Nrows):
db.append([data[k][i] for k in keys])
db = np.array(db).ravel()
# list of cells
end_letter = string.ascii_uppercase[Nkeys - 1]
end_number = str(Nrows + 1)
cell_list = ws.range(''.join(['A1:', end_letter, end_number]))
# fill in cells
for i, c in enumerate(cell_list):
c.value = db[i]
# resize db
ws.resize(rows=Nrows + 1, cols=Nkeys)
# update cells
ws.update_cells(cell_list)
def fetch_from_spreadsheet(eid):
gc = gspread.login(SPREADSHEET_ACC, SPREADSHEET_PW)
wks = gc.open_by_key(SPREADSHEET_ID).sheet1
row_no = wks.find(eid).row
value = wks.row_values(row_no)
json = dict(zip(LOG_KEY, value))
return json
def write_items():
nowstr = datetime.now().strftime('%Y/%m/%d %H:%M:%S')
print 'writing to google spreadsheet'
gapp = gspread.login('*****@*****.**', GPASS)
doc = gapp.open('Ruten Diesel Tracker')
sht = doc.add_worksheet(nowstr, 1, len(TITLES))
title_cells = sht.range('A1:%s1' % chr(ord('A') + len(TITLES) - 1 ))
for i in range(len(TITLES)):
title_cells[i].value = TITLES[i]
sht.update_cells(title_cells)
for i in ITEMS:
print 'writing: %s' % i
row = [
i['style'],
i['wash'],
i['wsize'],
i['lsize'],
i['available'],
i['backwidth'],
i['frontwidth'],
i['bottomheight'],
i['bottomwidth'],
i['biglegwidth'],
i['footwidth'],
i['outerlength'],
i['innerlegth'],
i['url'],
]
sht.append_row(row)
def update_spreadsheet(values, settings):
gc = gspread.login(settings['google_user']['username'],
settings['google_user']['password'])
# sempre atualiza a primeira folha da planilha
sheet = gc.open_by_key(settings['spreadsheet_key']).get_worksheet(0)
# pega as celulas da primeira linha
# cujos campos a serem gravados estão vazios
line = get_empty_line(sheet, values.keys())
# nenhuma linha disponivel (nunca deve acontecer rs)
if not line:
raise Exception('Nenhuma linha vazia encontrada')
# itera sobre uma copia da lista line pq ela sera alterada
# durante o loop
for cell in line[:]:
# obtem a letra da coluna da celula a ser atualizada
row = chr(cell.col + 64)
if values.get(row):
cell.value = values[row]
else:
line.remove(cell)
# faz o request de update
sheet.update_cells(line)
def __init__(self, email, password, title, sheet="sheet1"):
"""
Connect to the database and open it.
"""
import gspread
self.gs = gspread.login(email, password)
self.ws = self.gs.open(title).worksheet(sheet)
def login(self):
try:
self.gclient = gspread.login(self.user, self.password)
except Exception as e:
print("Error:")
print(e)
self.gclient = None
return self.gclient
def google_login():
#attempt to log in to your google account
try:
gc = gspread.login(os.environ["G_USER"], os.environ["G_PASS"])
print('log in success!!')
return gc
except:
print('log in fail')
sys.exit()
def login_open_sheet(email, password, spreadsheet): """Connect to Google Docs spreadsheet and return the first worksheet.""" try: gc = gspread.login(email, password) worksheet = gc.open(spreadsheet).sheet1 return worksheet except: print 'Unable to login and get spreadsheet. Check email, password, spreadsheet name.' sys.exit(1)
def geturl(msgid, sheet):
gc = gspread.login(creds.googleuser, creds.googlepass)
spreadsheet = gc.open(sheet)
worksheet = spreadsheet.sheet1
url = ""
for x in range(1, len(worksheet.col_values(1))):
if worksheet.col_values(1)[x] == msgid:
url = worksheet.col_values(2)[x]
return url
def gdocs_login():
spreadsheet_key = '1A9uLVV21BEZBhuSBhDMUUnoP_ICAzz2YAkVKCGWZHQs'
worksheet_key = 'od6'
gd_client = gdata.spreadsheet.service.SpreadsheetsService(spreadsheet_key, worksheet_key)
gd_client.email = '*****@*****.**'
gd_client.password = "******"
gd_client.ProgrammaticLogin()
gc = gspread.login('*****@*****.**', "alkezuowfwtexxer")
return gd_client, gc
def getallmembers(sheet):
gc = gspread.login(creds.googleuser, creds.googlepass)
spreadsheet = gc.open(sheet)
worksheet = spreadsheet.sheet1
numbers = worksheet.range('A2:A%s' % len(worksheet.col_values(1)))
members = []
for number in numbers:
members.append(number.value)
return members
