def convert_spreadsheet(path, export_type):
print(f'Uploading file {os.path.basename(path)} to Google Sheets...')
ss = ezsheets.upload(path)
print(f'Downloading file in {export_type} format...')
if export_type == 'HTML':
ss.downloadAsHTML()
elif export_type == 'ODS':
ss.downloadAsODS()
elif export_type == 'PDF':
ss.downloadAsPDF()
elif export_type == 'EXCEL':
ss.downloadAsExcel()
def get_spreadsheet(ss_name):
# Make sure file exists
if Path(ss_name).absolute().exists() == False:
raise Exception(
f"{ss_name} was not found in current working directory.")
sheet_list = ezsheets.listSpreadsheets()
# Strip extension off ss_name
ss_no_ext = re.sub(r"(\..*)", "", ss_name)
# Check if spreadsheet is already in Google Drive, if not upload it.
if ss_no_ext not in sheet_list.values():
ss = ezsheets.upload(f"{ss_name}")
return ss
# If so, get the key and open it.
else:
for key, value in sheet_list.items():
if ss_no_ext == value:
ss = ezsheets.Spreadsheet(key)
return ss
def spreadsheetConverter(fileName, fileFormat):
"""
Summary:
Uploads a file to google sheets
then downloads it to the specified format.
Args:
fileName (str): name of the file to upload.
format (str): name of file format to convert to.
"""
ss = ezsheets.upload(fileName)
if fileFormat.lower() == "excel":
ss.downloadAsExcel()
elif fileFormat.lower() == "pdf":
ss.downloadAsPDF()
elif fileFormat.lower() == "ods":
ss.downloadAsODS()
elif fileFormat.lower() == "csv":
ss.downloadAsCSV()
elif fileFormat.lower() == "html":
ss.downloadAsHTML()
elif fileFormat.lower() == "TSV":
ss.downloadAsTSV()
def convertidor(archivo, formato):
""" Esta función convierte un archivo spredsheet a un formato
específico: '.xlsx', '.ods', '.csv', '.tsv', '.pdf'
o '.html'. """
# Carga el archivo.
ss = ezsheets.upload(archivo)
# Comprueba el formato de salida y realiza la descarga.
if formato == 'excel':
ss.downloadAsExcel()
elif formato == 'openoffice':
ss.downloadAsODS()
elif formato == 'csv':
ss.downloadAsCSV()
elif formato == 'tsv':
ss.downloadAsTSV()
elif formato == 'pdf':
ss.downloadAsPDF()
elif formato == 'html':
ss.downloadAsHTML()
else:
print(
'Elige un formatoo válido: excel, openoffice, csv, tsv, pdf o html.'
)
import ezsheets
upload = ezsheets.upload(
r'C:\Users\Dr. Wan Asna\Desktop\Python Projects\Automate the Boring Stuff\Ch.13 - Working with Excel Spreadsheets\example.xlsx'
)
upload.title = 'example.xlsx'
ss = ezsheets.Spreadsheet(upload.spreadsheetId)
ss.downloadAsODS('exampleODS.ods')
ss.downloadAsCSV('exampleCSV.csv')
ss.downloadAsPDF('examplePDF.pdf')
ss.downloadAsHTML('exampleHTML.html')
#! python3
# converting_spreadsheeets.py - converts a spreadsheet file into other formats with google spreadsheets
import ezsheets
file_name = input('File ? ')
#TODO: get the file
#TODO: upload the file
ss = ezsheets.upload(file_name)
#TODO: download the file into diferents formats
ss.downloadAsExcel()
ss.downloadAsPDF()
ss.downloadAsODS()
print('Done.')
"""
Converting Google Speadsheets.
Convert excel spreadsheet and download into other formats.
"""
import ezsheets
# Keeps you updated on what the program is doing.
print('Uploading...')
# Upload spreadsheet to Google sheets
ss = ezsheets.upload('C:\\Users\\username\\spreadsheet.xlsx')
# Download spreadsheet into other formats.
print('Downloading as Excel file...')
ss.downloadAsExcel()
print('Downloading as ODS file...')
ss.downloadAsODS()
print('Downloading as CSV file...')
ss.downloadAsCSV()
print('Downloading as TSV file...')
ss.downloadAsTSV()
print('Downloading as PDF file...')
ss.downloadAsPDF()
print('Downloading as HTML file...')
ss.downloadAsHTML()
# Another update!
print('Downloaded as all the formats!')
"""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)
#!/usr/bin/env python3
"""Converts Google Spreadsheet into other formats."""
import ezsheets, os
excel_file = input(
'Enter the path to the excel file you would like to upload to Google Sheets: '
)
# Upload spreadsheet onto Google
ss = ezsheets.upload(excel_file)
# Create directory named after file without extension to store all converted formats
# Creates folder only if it does not already exist
file_name = os.path.basename(excel_file).rstrip('.xlsx')
try:
os.mkdir(file_name)
except:
pass
# Downloads Open Office, CSV, and PDF formats of the uploaded excel file into created directory
os.chdir(os.path.join(os.getcwd(), file_name))
ss.downloadAsODS()
ss.downloadAsCSV()
ss.downloadAsPDF()
#USAGE: in the commmand line run: python3 ch-14b.py <spreadsheet Name>
#second argument should be the spreadsheet name along with extension
#keep the spreadsheet in the same directory as this .py script
import ezsheets,sys
spreadsheetOld=sys.argv[1]
ss=ezsheets.upload(spreadsheetOld)
print('Spreadsheet name: '+ ss.title)
ss.downloadAsCSV()
print('Done!')
import ezsheets
ss = ezsheets.upload('example.xlsx')
ss.downloadAsODS()
ss.downloadAsExcel()
ss.downloadAsPDF()
ss.downloadAsCSV()
# 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
>>> 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:
>>> ezsheets.listSpreadsheets()
{'1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU': 'Education Data'}
The listSpreadsheets() function returns a dictionary where the keys are spreadsheet IDs and the values are the titles of each spreadsheet.
Once you’ve obtained a Spreadsheet object, you can use its attributes and methods to manipulate the online spreadsheet hosted on Google Sheets.
import ezsheets
ss = ezsheets.upload(
'1jDZEdvSIh4TmZxccyy0ZXrH-ELlrwq8_YYiZrEOB4jg') #uploads the google sheet
ss.downloadAsCSV() #downloads as a CSV file locally
ss.downloadAsExcel() #downloads as an excel sheet locally
ss.downloadAsPDF() #downloads as a PDF locally
ss.downloadAsODS() #downloads as a ODS file locally
ss.downloadAsHTML() #downloads as a HTML file locally
ss.downloadAsTSV() #downloads as a TSV file locally
#ss.downloadAsExcel('a_different_filename.xlsx')
#ss.delete() # Delete the spreadsheet.
#ezsheets.listSpreadsheets() #Get all of the sheets on the account
# sheet.refresh() #refresh the local data in the sheet object
""" Start pyxl stuff here..."""
#ss.downloadAsExcel()
ss.downloadAsExcel('barChartSheet.xlsx')
wb = openpyxl.load_workbook('barChartSheet.xlsx')
sheet = wb['Sheet1']
chartObj = openpyxl.chart.BarChart()
labels = openpyxl.chart.Reference(sheet, min_col=1, min_row=2, max_row=5)
data = openpyxl.chart.Reference(sheet, min_col=2, min_row=2, max_row=5)
#Add the data and labels (by set_categories)
chartObj.add_data(data, titles_from_data=True)
chartObj.set_categories(labels)
#Chart Formatting
chartObj.title = 'Quick Start'
chartObj.x_axis.title = 'Quick'
chartObj.y_axis.title = 'Start'
sheet.add_chart(chartObj, 'F5')
wb.save('barChartSheet.xlsx')
ss = ezsheets.upload('1JCVnNPNvjvecRMULcCNffGjbYFKb7Are0DvQcaS7G_A')
import ezsheets
ss = ezsheets.Spreadsheet('1Qit1982XhY0mpbCJt3Ofh9Q2CSQRgA8slFivANKl_2U')
print(ss.title)
# creating new file: ezsheets.createSpreadsheet('')
# upload an existing file: ezsheets.upload('')
# list the spreadhssets in google account: ezsheets.listSpreadsheets()
# ss.downloadAsCSV() # only downloads the first sheet as file
# ss.downloadAsPDF()
# refresh method: sheet.refresh()
sw = ezsheets.upload('produceSales.xlsx')
sheet = sw[0]
sheet.getRow(1) # The first row is row 1, not row 0.
sheet.getRow(2)
#Counts
print(sheet.rowCount)
print(sheet.columnCount)
#Create a new sheet
ss.createSheet('Test Data - 2')
print(ss.sheetTitles)
#Copy the first sheet
sheet.copyTo(ss)
#Refresh the data
ss.refresh()
#Download as Test.xlsx
ss.downloadAsExcel()
#Delete
ss.delete()
#Check to see if spreedsheet is gone
print(ezsheets.listSpreadsheets())
#Uploads the downloaded file
ss = ezsheets.upload('Test.xlsx')
#Check to see if spreedsheet is uploaded
print(ezsheets.listSpreadsheets())
ss.delete()
def formatConverting(fileOne, fileTwo):
ss = ezsheets.upload(fileOne)
ss.downloadAsExcel(fileTwo)
from pathlib import Path
import pyinputplus as pyip
#gets user directory and file name
userDirect = input('What directory is the file in?\n')
userInput = input('What file do you want to upload to Google Sheets?\n')
#sets absolute path of file
file = os.path.join(userDirect, userInput)
#Matches naming of uploaded spreadsheet names in google sheets
ssName = userDirect + '\\' + (Path(file).stem)
#Tests if file can be uploaded
try:
ezsheets.upload(file)
print('Uploaded')
except:
print('Invalid file format')
#Searches list of spreadsheets to match name with an ID
ssDict = ezsheets.listSpreadsheets()
for ids, names in ssDict.items():
if names == ssName:
ssId = ids
#Gets Spreadsheet object based off ID
ss = ezsheets.Spreadsheet(ssId)
#Asks users for what format to convert to - will default to whats typed in list if typed differently - ie 'excel' will equal 'Excel'
userFormat = pyip.inputChoice(