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()