def copiarPlantilla(titulo):
ss = ezsheets.Spreadsheet(PLANTILLA)
ss2 = ezsheets.createSpreadsheet(titulo)
ss[0].copyTo(ss2)
ss2[0].delete()
return ss2
def __create_trade_journal__(self):
ss = ezsheets.createSpreadsheet(self.title)
queued_trades = ss[0]
queued_trades.title = "Queued Trades"
queued_trades.updateRow(1, [
'Ticker', 'Type', 'Entry Price', 'Exit Price', 'Stop Loss',
'Notes', 'Expiration in Days', 'Metadata', 'Sell At End of Day'
])
trades = ss.createSheet('Trades')
trades.updateRow(1, [
'ID', 'Create Date', 'Ticker', 'Type', 'Status', 'Entry Date',
'Exit Date', 'Planned Entry Price', 'Planned Exit Price',
'Stop Loss', 'Shares', 'Entry Price', 'Exit Price', 'Gain',
'Buy Order', 'Sell Order', 'Notes', 'Comments', 'Metadata',
'Buy Metadata', 'Sale Metadata'
])
return ss
def init():
global TEST_SS
ezsheets.init()
# Create a new spreadsheet
TEST_SS = ezsheets.createSpreadsheet(title='Delete Me')
# Use an existing spreadsheet:
#TEST_SS = ezsheets.Spreadsheet('https://docs.google.com/spreadsheets/d/1lRyPHuaLIgqYwkCTJYexbZUO1dcWeunm69B0L7L4ZQ8/edit#gid=0')
tempName = 'temp_%s' % (random.sample('abcdefg' * 10, 10))
TEST_SS.createSheet(tempName, index=0)
while len(TEST_SS) > 1:
del TEST_SS[1]
TEST_SS.createSheet('Sheet1', index=0)
del TEST_SS[tempName]
checkIfSpreadsheetInOriginalState()
def google_sheets_processor():
#google sheets area
my_spreadsheet = ezsheets.createSpreadsheet("Grade Data")
my_url = my_spreadsheet.url
#set current google sheet and set getrows
curr_sheet = my_spreadsheet[0]
rows = curr_sheet.getRows()
#set headers in google sheet
rows[0][0] = "Parental Level of Education"
rows[0][1] = "Test Preparation"
rows[0][2] = "Math Score"
rows[0][3] = "Reading Score"
rows[0][4] = "Writing Score"
row_count_goog = 0
#csv file is opened and read, data is then added to the rows variable
with open("text_files/exam_data.csv") as csv_file:
csv_data = csv.reader(csv_file, delimiter=",")
next(csv_data)
for data in csv_data:
rows[row_count_goog][0] = data[0]
rows[row_count_goog][1] = data[1]
rows[row_count_goog][2] = data[2]
rows[row_count_goog][3] = data[3]
rows[row_count_goog][4] = data[4]
row_count_goog = row_count_goog + 1
#send rows to sheet
curr_sheet.updateRows(rows)
webbrowser.open(my_url)
#take current date and time
time_now = datetime.datetime.now()
#open log in text_files, and writes to it in the format listed
log_open = open("text_files/script_log.txt", "a")
log_open.write(
time_now.strftime("%b %d %Y %H:%M:%S") +
" - Google Sheet Processing is Complete! \n")
log_open.close()
import ezsheets
ss = ezsheets.createSpreadsheet('Multiple Sheets')
ss.sheetTitles
ss.createSheet('Spam') # Create a new sheet at the end of the list of
ss.createSheet('Eggs') # Create another new sheet.
ss.sheetTitles
ss.createSheet('Bacon', 0) code># Create a sheet at index 0 in the list of
ss.sheetTitles
ss.sheetTitles
ss[0].delete() # Delete the sheet at index 0: the "Bacon" sheet.
ss.sheetTitles
ss['Spam'].delete() # Delete the "Spam" sheet.
ss.sheetTitles
sheet = ss['Eggs'] # Assign a variable to the "Eggs" sheet.
sheet.delete() # Delete the "Eggs" sheet.
ss.sheetTitles
ss[0].clear() # Clear all the cells on the "Sheet1" sheet.
ss.sheetTitles # The "Sheet1" sheet is empty but still exists.
"""read_write_data.py
구글 스프레드 시트 문서에 데이터 입력 및 접근하기
"""
import ezsheets
ss = ezsheets.createSpreadsheet("My SpreadSheet")
sheet = ss[0] # 첫번째 시트에 접근
print(sheet.title) # '시트1'
# 데이터 입력
sheet["A1"] = "Name"
sheet["B1"] = "Age"
sheet["C1"] = "Favorite Movie"
print(sheet["A1"]) # Name
print(sheet[2, 1]) # Age
sheet[1, 2] = "Alice"
sheet[2, 2] = 30
sheet[3, 2] = "RoboCop"
# 특정 행, 열 접근하기
print(sheet.getRow(1)) # 첫 번째 행 접근
print(sheet.getColumn(1)) # 첫 번째 열 접근하기
# print(sheet.getColumn("A")) # 첫 번째 열 접근하기
# 특정 행, 열 수정하기
sheet.updateRow(3, ["Pumpkin", 25, "Halloween"])
column_one = sheet.getColumn(1)
for i, value in enumerate(column_one):
import ezsheets
ss = ezsheets.Spreadsheet('1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU')
ss.title
ss.downloadAsExcel() # Downloads the spreadsheet as an Excel file.
ss.downloadAsODS() # Downloads the spreadsheet as an OpenOffice file.
ss.downloadAsCSV() # Only downloads the first sheet as a CSV file.
ss.downloadAsTSV() # Only downloads the first sheet as a TSV file.
ss.downloadAsPDF() # Downloads the spreadsheet as a PDF.
ss.downloadAsHTML() # Downloads the spreadsheet as a ZIP of HTML files.
ss.downloadAsExcel('a_different_filename.xlsx')
ss = ezsheets.createSpreadsheet('Delete me') # Create the spreadsheet.
ezsheets.listSpreadsheets() # Confirm that we've created a spreadsheet.
ss.delete() # Delete the spreadsheet.
ezsheets.listSpreadsheets()
curr_sheet["A" + str(new_max_row)].font = Font(b=True)
curr_sheet["A" + str(new_max_row)].alignment = Alignment(wrap_text=True,
horizontal="center")
curr_sheet["A" + str(new_max_row)].value = "Average:"
sales_workbook.save("files/sales_data.xlsx")
row_num = 3
curr_sheet.cell(row=new_max_row,
column=avg_col_start).value = "=ROUND((AVERAGE(B" + str(
row_num) + ":" + "B" + str(new_max_row - 1) + ")), 2)"
sales_workbook.save("files/sales_data.xlsx")
my_spreadsheet = ezsheets.createSpreadsheet("Sales Data")
my_url = my_spreadsheet.url
curr_sheet = my_spreadsheet[0]
rows = curr_sheet.getRows()
rows[0][0] = "Product"
rows[0][1] = "Quantity"
row_count_goog = 1
for val in combined_quantities:
if isinstance(val, dict):
for key, value in val.items():
rows[row_count_goog][0] = str(key)
rows[row_count_goog][1] = int(value)
row_count_goog = row_count_goog + 1
NOTE
The specific spreadsheet IDs used in this chapter are for my Google account’s spreadsheets. They won’t work if you enter them into your interactive shell. Go to https://sheets.google.com/ to create spreadsheets under your account and then get the IDs from the address bar.
Pass your spreadsheet’s ID as a string to the ezsheets.Spreadsheet() function to obtain a Spreadsheet object for its spreadsheet:
>>> import ezsheets
>>> ss = ezsheets.Spreadsheet('1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU')
>>> ss
Spreadsheet(spreadsheetId='1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU')
>>> ss.title
'Education Data'
For convenience, you can also obtain a Spreadsheet object of an existing spreadsheet by passing the spreadsheet’s full URL to the function. Or, if there is only one spreadsheet in your Google account with that title, you can pass the title of the spreadsheet as a string.
To make a new, blank spreadsheet, call the ezsheets.createSpreadsheet() function and pass it a string for the new spreadsheet’s title. For example, enter the following into the interactive shell:
>>> import ezsheets
>>> ss = ezsheets.createSpreadsheet('Title of My New Spreadsheet')
>>> ss.title
'Title of My New Spreadsheet'
To upload an existing Excel, OpenOffice, CSV, or TSV spreadsheet to Google Sheets, pass the filename of the spreadsheet to ezsheets.upload(). Enter the following into the interactive shell, replacing my_spreadsheet.xlsx with a spreadsheet file of your own:
>>> import ezsheets
>>> ss = ezsheets.upload('my_spreadsheet.xlsx')
>>> ss.title
'my_spreadsheet'
You can list the spreadsheets in your Google account by calling the listSpreadsheets() function. Enter the following into the interactive shell after uploading a spreadsheet:
#pip install ezsheets
#https://console.developers.google.com/apis/library/sheets.googleapis.com to enable the api
#https://console.developers.google.com/apis/library/drive.googleapis.com to enable api
#https://developers.google.com/sheets/api/quickstart/python/ for the credentials file to put in the working directory.
#Rename credential.json file to credential-sheets.json
import ezsheets #Running this for the first time with the credential-sheets.json will open your browser to grant the app access to your drive
#Create a new spreadsheet
ss = ezsheets.createSpreadsheet('Test')
print(ss.spreadsheetId)
print(ss.title)
print(ss.url)
print(ss.sheetTitles)
print(ss.sheets)
#Get first sheet
sheet = ss[0]
#Change title
sheet.title = 'Test Data'
#Set header row
sheet['A1'] = 'Name'
sheet['B1'] = 'Age'
sheet['C1'] = 'Occupation'
#Set a row
sheet['A2'] = 'Bob'
sheet['B2'] = 40
sheet['C2'] = 'Engineer'
# Testing out various commands and utilities of the EZsheets module
import ezsheets
ss = ezsheets.Spreadsheet('11gn7L4eP2jhKuzG1-fcGqGZ1BC0KKHbB6emQ_G9zAO4')
print(ss)
print(ss.title)
ss = ezsheets.createSpreadsheet('Testing EZsheets')
print(ss.title)
ss = ezsheets.upload('censuspopdata.xlsx')
print(ss.title)
print(ezsheets.listSpreadsheets())
ss.title = "Top r/Science posts"
print(ss.spreadsheetId) # get spreadsheet ID
print(ss.url) # get spreadsheet url
print(ss.sheetTitles) # get titles of all sheet objects
print(ss.sheets) # get sheet objects in order
print(ss[0]) # get first sheet object
print(ss.title)
ss.downloadAsCSV() # Download spreadsheet as CSV file
ss.downloadAsExcel() # Download spreadsheet as Excel file
ss.downloadAsHTML() # Download spreadsheet as ZIP of HTML files
ss.downloadAsODS() # Download spreadsheet as OpenOffice file
ss.downloadAsPDF() # Download spreadsheet as PDF file
ss.downloadAsTSV() # Download spreadsheet as TSV file
import ezsheets
ss = ezsheets.Spreadsheet('1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU')
ss
Spreadsheet(spreadsheetId='1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU')
ss.title
ss = ezsheets.createSpreadsheet('Title of My New Spreadsheet')
ss.title
ss = ezsheets.upload('my_spreadsheet.xlsx')
ss.title
csvPath = os.path.join(systemfilePath, 'dataImport.csv')
# UNCOMMENT WHEN GOING LIVE
createGradesTable()
with open(csvPath, newline='') as csvData:
reader = csv.DictReader(csvData)
my_db = DB_Connect('root', '', 'python_projects')
for row in reader:
addGradesToDatabase(row, my_db)
logAction(LogFile, "Loaded Database Tables.")
my_db.conn.close()
workbook = xlsxwriter.Workbook(os.path.join(systemfilePath, "gradesWorkbook.xlsx"))
worksheet = workbook.add_worksheet("Grades")
with open(csvPath, newline='') as csvData:
dataSet = csv.DictReader(csvData)
writeSpreadsheet(worksheet, workbook, dataSet)
logAction(LogFile, "Created Spreadsheet.")
my_spreadsheet = ezsheets.createSpreadsheet("My Spreadsheet")
curr_sheet = my_spreadsheet[0]
rows = curr_sheet.getRows()
addHeadersGoogleDocs(rows)
curr_sheet.updateRows(rows)
with open(csvPath, newline='') as csvData:
data = csv.DictReader(csvData)
loadGoogleTableData(curr_sheet,my_spreadsheet,data)
curr_sheet.updateRows(rows)
logAction(LogFile, "Created Google Spreadsheet.")
import ezsheets
ss1 = ezsheets.createSpreadsheet('First Spreadsheet')
ss2 = ezsheets.createSpreadsheet('Second Spreadsheet')
ss1[0]
ss1[0].updateRow(1, ['Some', 'data', 'in', 'the', 'first', 'row'])
ss1[0].copyTo(ss2) # Copy the ss1's Sheet1 to the ss2 spreadsheet.
ss2.sheetTitles # ss2 now contains a copy of ss1's Sheet1.
"""create_upload_spreadsheets.py
구글 스프레드 시트 문서 열기, 생성하기, 업로드 하기
"""
import ezsheets
# 등록된 문서 열기
sheet_id = "1jDZEdvSIh4TmZxccyy0ZXrH-ELlrwq8_YYiZrEOB4jg"
ss = ezsheets.Spreadsheet(sheet_id)
print(ss) # <Spreadsheet title="Bean Count", 1 sheets>
print(ss.title) # Bean Count
# 빈 스프레드 시트 문서 만들기
ss = ezsheets.createSpreadsheet("Title of My New Spreadsheet")
print(ss.title) # Title of My New Spreadsheet
ss.delete() # 스프레드 시트 삭제하기
# 이미 존재하는 엑셀 문서 업로드
ss = ezsheets.upload("example.xlsx")
print(ss.title)
print(ss.url)
import ezsheets
ss = ezsheets.createSpreadsheet('My Spreadsheet')
sheet = ss[0] # Get the first sheet in this spreadsheet.
sheet.title
sheet = ss[0]
sheet['A1'] = 'Name' # Set the value in cell A1.
sheet['B1'] = 'Age'
sheet['C1'] = 'Favorite Movie'
sheet['A1'] # Read the value in cell A1.
sheet['A2'] # Empty cells return a blank string.
sheet[2, 1] # Column 2, Row 1 is the same address as B1.
sheet['A2'] = 'Alice'
sheet['B2'] = 30
sheet['C2'] = 'RoboCop'
"""create_delete_sheets.py
구글 스프레드시트 문서 시트 생성 및 삭제
"""
import ezsheets
ss = ezsheets.createSpreadsheet("Multiple Sheets")
print(ss.sheetTitles) # ("시트1",)
# 시트 생성
ss.createSheet("Spam")
ss.createSheet("Eggs")
print(ss.sheetTitles) # ("시트1", "Spam", "Eggs")
ss.createSheet("Bacon", 0)
print(ss.sheetTitles) # ("Bacon", "시트1", "Spam", "Eggs")
# 시트 삭제
ss[1].delete()
print(ss.sheetTitles) # ("Bacon", "Spam", "Eggs")
ss["Eggs"].delete()
print(ss.sheetTitles) # ("Bacon", "Spam")
# 시트 데이터 초기화
ss[0].clear()