Upload Xlsx Data to Google Sheets Python

14
WORKING WITH GOOGLE SHEETS

Google Sheets, the free, web-based spreadsheet application available to anyone with a Google account or Gmail address, has become a useful, feature-rich competitor to Excel. Google Sheets has its ain API, but this API tin be disruptive to learn and use. This affiliate covers the EZSheets third-party module, documented at https://ezsheets.readthedocs.io/. While non as full featured as the official Google Sheets API, EZSheets makes common spreadsheet tasks easy to perform.

Installing and Setting Upwardly EZSheets

You can install EZSheets by opening a new terminal window and running pip install --user ezsheets. Equally part of this installation, EZSheets volition also install the google-api-python-client, google-auth-httplib2, and google-auth-oauthlib modules. These modules allow your program to log in to Google'southward servers and make API requests. EZSheets handles the interaction with these modules, and so you lot don't need to business organization yourself with how they work.

Obtaining Credentials and Token Files

Before yous tin apply EZSheets, you lot demand to enable the Google Sheets and Google Bulldoze APIs for your Google business relationship. Visit the following spider web pages and click the Enable API buttons at the top of each:

• https://console.developers.google.com/apis/library/sheets.googleapis.com/
• https://console.developers.google.com/apis/library/drive.googleapis.com/

You'll also need to obtain iii files, which you should save in the same folder as your .py Python script that uses EZSheets:

• A credentials file named credentials-sheets.json
• A token for Google Sheets named token-sheets.pickle
• A token for Google Drive named token-bulldoze.pickle

The credentials file will generate the token files. The easiest way to obtain a credentials file is to go to the Google Sheets Python Quickstart page at https://developers.google.com/sheets/api/quickstart/python/ and click the bluish Enable the Google Sheets API push button, as shown in Figure fourteen-1. You'll need to log in to your Google account to view this page.

Figure fourteen-1: Obtaining a credentials.json file.

Clicking this button will bring up a window with a Download Client Configuration link that lets you download a credentials.json file. Rename this file to credentials-sheets.json and identify it in the same folder every bit your Python scripts.

Once yous have a credentials-sheets.json file, run the import ezsheets module. The first fourth dimension you import the EZSheets module, information technology will open a new browser window for you to log in to your Google account. Click Allow, as shown in Effigy fourteen-2.

Figure 14-2: Assuasive Quickstart to access your Google account

The message about Quickstart comes from the fact that you downloaded the credentials file from the Google Sheets Python Quickstart page. Note that this window will open up twice: first for Google Sheets admission and 2d for Google Drive access. EZSheets uses Google Bulldoze access to upload, download, and delete spreadsheets.

Afterward yous log in, the browser window volition prompt you to close information technology, and the token-sheets.pickle and token-drive.pickle files volition appear in the same binder as credentials-sheets.json. Yous only demand to go through this procedure the start time you run import ezsheets.

If you encounter an mistake subsequently clicking Permit and the page seems to hang, brand certain you take first enabled the Google Sheets and Drive APIs from the links at the kickoff of this section. It may take a few minutes for Google's servers to annals this change, so y'all may have to look before y'all can use EZSheets.

Don't share the credential or token files with anyone—treat them like passwords.

Revoking the Credentials File

If you accidentally share the credential or token files with someone, they won't exist able to alter your Google account countersign, simply they will have access to your spreadsheets. You can revoke these files by going to the Google Cloud Platform programmer's panel page at https://panel.developers.google.com/. Y'all'll need to log in to your Google business relationship to view this page. Click the Credentials link on the sidebar. So click the trash tin can icon next to the credentials file you've accidentally shared, as shown in Effigy fourteen-three.

Effigy 14-3: The Credentials page in the Google Cloud Platform developer's console

To generate a new credentials file from this page, click the Create Credentials button and select OAuth customer ID, also shown in Figure 14-3. Adjacent, for Application Blazon, select Other and requite the file whatever name you like. This new credentials file will and then be listed on the page, and y'all tin click on the download icon to download it. The downloaded file will have a long, complicated filename, so you lot should rename information technology to the default filename that EZSheets attempts to load: credentials-sheets.json. You lot can besides generate a new credential file by clicking the Enable the Google Sheets API push button mentioned in the previous department.

Spreadsheet Objects

In Google Sheets, a spreadsheet can contain multiple sheets (also called worksheets), and each sheet contains columns and rows of values. Figure xiv-4 shows a spreadsheet titled "Education Data" containing iii sheets titled "Students," "Classes," and "Resources." The first column of each sheet is labeled A, and the first row is labeled 1.

Figure 14-4: A spreadsheet titled "Education Data" with three sheets

While virtually of your work will involve modifying the Sheet objects, you lot can also modify Spreadsheet objects, as you'll come across in the side by side section.

Creating, Uploading, and Listing Spreadsheets

Yous tin can make a new Spreadsheet object from an existing spreadsheet, a blank spreadsheet, or an uploaded spreadsheet. To make a Spreadsheet object from an existing Google Sheets spreadsheet, you'll demand the spreadsheet's ID string. The unique ID for a Google Sheets spreadsheet can exist found in the URL, after the spreadsheets/d/ part and before the /edit office. For case, the spreadsheet featured in Figure xiv-four is located at the URL https://docs.google.com/spreadsheets/d/1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU/edit#gid=151537240/, so its ID is 1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU.

Notation

The specific spreadsheet IDs used in this affiliate are for my Google account'due south spreadsheets. They won't piece of work if you enter them into your interactive shell. Go to https://sheets.google.com/ to create spreadsheets under your business relationship and then get the IDs from the address bar.

Pass your spreadsheet's ID every bit a cord to the ezsheets.Spreadsheet() office 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 lot can besides obtain a Spreadsheet object of an existing spreadsheet by passing the spreadsheet'south full URL to the function. Or, if there is only one spreadsheet in your Google account with that championship, you can pass the title of the spreadsheet as a string.

To make a new, blank spreadsheet, call the ezsheets.createSpreadsheet() office and pass information technology a string for the new spreadsheet's championship. For instance, enter the post-obit into the interactive crush:

>>> 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 post-obit into the interactive trounce, replacing my_spreadsheet.xlsx with a spreadsheet file of your own:

>>> import ezsheets
>>> ss = ezsheets.upload(' my_spreadsheet.xlsx ')
>>> ss.title
'my_spreadsheet'

You lot tin list the spreadsheets in your Google business relationship past calling the listSpreadsheets() function. Enter the following into the interactive trounce later on uploading a spreadsheet:

>>> ezsheets.listSpreadsheets()
{'1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU': 'Teaching Data'}

The listSpreadsheets() part 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 tin can use its attributes and methods to manipulate the online spreadsheet hosted on Google Sheets.

Spreadsheet Attributes

While the actual data lives in a spreadsheet'south individual sheets, the Spreadsheet object has the following attributes for manipulating the spreadsheet itself: championship, spreadsheetId, url, sheetTitles, and sheets. Enter the following into the interactive crush:

>>> import ezsheets
>>> ss = ezsheets.Spreadsheet(' 1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU ')
>>> ss.championship# The title of the spreadsheet.
'Education Data'
>>> ss.title = 'Course Data' # Modify the title.
>>> ss.spreadsheetId # The unique ID (this is a read-only attribute).
'1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU'
>>> ss.url# The original URL (this is a read-only aspect).
'https://docs.google.com/spreadsheets/d/1J-Jx6Ne2K_vqI9J2SO-
TAXOFbxx_9tUjwnkPC22LjeU/'
>>> ss.sheetTitles# The titles of all the Sail objects
('Students', 'Classes', 'Resources')
>>> ss.sheets# The Sheet objects in this Spreadsheet, in order.
(<Canvass sheetId=0, championship='Students', rowCount=1000, columnCount=26>, <Sheet
sheetId=1669384683, title='Classes', rowCount=m, columnCount=26>, <Canvas
sheetId=151537240, title='Resources', rowCount=k, columnCount=26>)
>>> ss[0]# The starting time Canvass object in this Spreadsheet.
<Sail sheetId=0, championship='Students', rowCount=1000, columnCount=26>
>>> ss['Students']# Sheets can likewise be accessed by title.
<Sheet sheetId=0, title='Students', rowCount=1000, columnCount=26>
>>> del ss[0]# Delete the first Canvas object in this Spreadsheet.
>>> ss.sheetTitles# The "Students" Sail object has been deleted:
('Classes', 'Resource')

If someone changes the spreadsheet through the Google Sheets website, your script can update the Spreadsheet object to match the online data past calling the refresh() method:

>>> ss.refresh()

This will refresh not but the Spreadsheet object's attributes but besides the data in the Canvass objects it contains. The changes yous make to the Spreadsheet object will be reflected in the online spreadsheet in real time.

Downloading and Uploading Spreadsheets

Yous tin download a Google Sheets spreadsheet in a number of formats: Excel, OpenOffice, CSV, TSV, and PDF. You tin as well download it as a ZIP file containing HTML files of the spreadsheet's information. EZSheets contains functions for each of these options:

>>> import ezsheets
>>> ss = ezsheets.Spreadsheet(' 1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU ')
>>> ss.title
'Class Information'
>>> ss.downloadAsExcel() # Downloads the spreadsheet as an Excel file.
'Class_Data.xlsx'
>>> ss.downloadAsODS() # Downloads the spreadsheet as an OpenOffice file.
'Class_Data.ods'
>>> ss.downloadAsCSV() # Simply downloads the get-go canvass equally a CSV file.
'Class_Data.csv'
>>> ss.downloadAsTSV() # Merely downloads the commencement sheet every bit a TSV file.
'Class_Data.tsv'
>>> ss.downloadAsPDF() # Downloads the spreadsheet as a PDF.
'Class_Data.pdf'
>>> ss.downloadAsHTML() # Downloads the spreadsheet every bit a Nothing of HTML files.
'Class_Data.zip'

Note that files in the CSV and TSV formats tin contain only one sheet; therefore, if you download a Google Sheets spreadsheet in this format, you will get the first sheet merely. To download other sheets, yous'll need to change the Sheet object'due south index attribute to 0. See "Creating and Deleting Sheets" on page 341 for information on how to exercise this.

The download functions all return a string of the downloaded file's filename. You can also specify your own filename for the spreadsheet by passing the new filename to the download function:

>>> ss.downloadAsExcel('a_different_filename.xlsx')
'a_different_filename.xlsx'

The function should return the updated filename.

Deleting Spreadsheets

To delete a spreadsheet, call the delete() method:

>>> import ezsheets
>>> ss = ezsheets.createSpreadsheet('Delete me') # Create the spreadsheet.
>>> ezsheets.listSpreadsheets() # Confirm that we've created a spreadsheet.
{'1aCw2NNJSZblDbhygVv77kPsL3djmgV5zJZllSOZ_mRk': 'Delete me'}
>>> ss.delete() # Delete the spreadsheet.
>>> ezsheets.listSpreadsheets()
{}

The delete() method will movement your spreadsheet to the Trash folder on your Google Bulldoze. You lot can view the contents of your Trash folder at https://drive.google.com/drive/trash. To permanently delete your spreadsheet, pass Truthful for the permanent keyword argument:

>>> ss.delete(permanent=True)

In full general, permanently deleting your spreadsheets is not a proficient idea, because it would be impossible to recover a spreadsheet that a bug in your script accidentally deleted. Even free Google Drive accounts have gigabytes of storage available, so you most probable don't need to worry almost freeing up space.

Canvass Objects

A Spreadsheet object volition have one or more Sheet objects. The Sheet objects represent the rows and columns of data in each sheet. You can admission these sheets using the square brackets operator and an integer alphabetize. The Spreadsheet object's sheets attribute holds a tuple of Sheet objects in the lodge in which they announced in the spreadsheet. To access the Sheet objects in a spreadsheet, enter the following into the interactive shell:

>>> import ezsheets
>>> ss = ezsheets.Spreadsheet(' 1J-Jx6Ne2K_vqI9J2SO-TAXOFbxx_9tUjwnkPC22LjeU ')
>>> ss.sheets# The Sail objects in this Spreadsheet, in order.
(<Sheet sheetId=1669384683, title='Classes', rowCount=1000, columnCount=26>,
<Canvas sheetId=151537240, championship='Resources', rowCount=yard, columnCount=26>)
>>> ss.sheets[0] # Gets the starting time Sheet object in this Spreadsheet.
<Sheet sheetId=1669384683, title='Classes', rowCount=1000, columnCount=26>
>>> ss[0]# Also gets the first Sheet object in this Spreadsheet.
<Canvass sheetId=1669384683, title='Classes', rowCount=1000, columnCount=26>

You can as well obtain a Canvass object with the square brackets operator and a cord of the sail'southward name. The Spreadsheet object's sheetTitles attribute holds a tuple of all the sheet titles. For example, enter the post-obit into the interactive shell:

>>> ss.sheetTitles # The titles of all the Sheet objects in this Spreadsheet.
('Classes', 'Resources')
>>> ss['Classes'] # Sheets can also be accessed by title.
<Sheet sheetId=1669384683, title='Classes', rowCount=1000, columnCount=26>

Once you take a Sheet object, yous tin can read data from and write data to it using the Sheet object's methods, as explained in the next section.

Reading and Writing Information

Just equally in Excel, Google Sheets worksheets accept columns and rows of cells containing data. You can use the square brackets operator to read and write data from and to these cells. For case, to create a new spreadsheet and add data to it, enter the following into the interactive crush:

>>> import ezsheets
>>> ss = ezsheets.createSpreadsheet('My Spreadsheet')
>>> sail = ss[0] # Get the first sheet in this spreadsheet.
>>> canvas.title
'Sheet1'
>>> sail = ss[0]
>>> sheet['A1'] = 'Name' # Set the value in cell A1.
>>> sheet['B1'] = 'Age'
>>> sail['C1'] = 'Favorite Movie'
>>> sheet['A1'] # Read the value in jail cell A1.
'Name'
>>> sheet['A2'] # Empty cells return a blank cord.
''
>>> canvass[two, i] # Column 2, Row 1 is the aforementioned address every bit B1.
'Age'
>>> sail['A2'] = 'Alice'
>>> sheet['B2'] = 30
>>> sheet['C2'] = 'RoboCop'

These instructions should produce a Google Sheets spreadsheet that looks like Figure 14-v.

Effigy 14-v: The spreadsheet created with the case instructions

Multiple users can update a sheet simultaneously. To refresh the local data in the Canvass object, call its refresh() method:

>>> sheet.refresh()

All of the data in the Sheet object is loaded when the Spreadsheet object is first loaded, so the information is read instantly. However, writing values to the online spreadsheet requires a network connection and can accept about a second. If you have thousands of cells to update, updating them ane at a time might be quite ho-hum.

Column and Row Addressing

Prison cell addressing works in Google Sheets just similar in Excel. The merely difference is that, unlike Python'south 0-based list indexes, Google Sheets accept ane-based columns and rows: the commencement column or row is at index 1, not 0. Yous can convert the 'A2' string-style address to the (cavalcade, row) tuple-style accost (and vice versa) with the convertAddress() function. The getColumnLetterOf() and getColumnNumberOf() functions will also convert a column address between messages and numbers. Enter the following into the interactive beat out:

>>> import ezsheets
>>> ezsheets.convertAddress('A2') # Converts addresses...
(one, 2)
>>> ezsheets.convertAddress(1, 2) # ...and converts them dorsum, too.
'A2'
>>> ezsheets.getColumnLetterOf(2)
'B'
>>> ezsheets.getColumnNumberOf('B')
2
>>> ezsheets.getColumnLetterOf(999)
'ALK'
>>> ezsheets.getColumnNumberOf('ZZZ')
18278

The 'A2' string-style addresses are convenient if you're typing addresses into your source code. But the (column, row) tuple-style addresses are user-friendly if you're looping over a range of addresses and need a numeric course for the cavalcade. The convertAddress(), getColumnLetterOf(), and getColumnNumberOf() functions are helpful when you need to convert between the two formats.

Reading and Writing Entire Columns and Rows

As mentioned, writing information ane prison cell at a fourth dimension can often take as well long. Fortunately, EZSheets has Canvas methods for reading and writing entire columns and rows at the same time. The getColumn(), getRow(), updateColumn(), and updateRow() methods volition, respectively, read and write columns and rows. These methods brand requests to the Google Sheets servers to update the spreadsheet, so they require that you exist continued to the net. In this section's example, we'll upload produceSales.xlsx from the final chapter to Google Sheets. The offset eight rows look like Table 14-1.

Tabular array fourteen-1: The First Eight Rows of the produceSales.xlsx Spreadsheet

	A	B	C	D
one	PRODUCE	COST PER POUND	POUNDS SOLD	Full
2	Potatoes	0.86	21.6	18.58
three	Okra	ii.26	38.six	87.24
four	Fava beans	2.69	32.8	88.23
v	Watermelon	0.66	27.3	eighteen.02
6	Garlic	1.xix	4.9	5.83
7	Parsnips	ii.27	1.1	ii.v
8	Asparagus	ii.49	37.9	94.37

To upload this spreadsheet, enter the following into the interactive beat out:

>>> import ezsheets
>>> ss = ezsheets.upload('produceSales.xlsx')
>>> canvas = ss[0]
>>> canvas.getRow(ane) # The first row is row 1, not row 0.
['PRODUCE', 'COST PER POUND', 'POUNDS SOLD', 'Total', '', '']
>>> sheet.getRow(2)
['Potatoes', '0.86', '21.half-dozen', '18.58', '', '']
>>> columnOne = sheet.getColumn(1)
>>> canvass.getColumn(1)
['PRODUCE', 'Potatoes', 'Okra', 'Fava beans', 'Watermelon', 'Garlic',
--snip--
>>> sheet.getColumn('A') # Same upshot equally getColumn(1)
['PRODUCE', 'Potatoes', 'Okra', 'Fava beans', 'Watermelon', 'Garlic',
--snip--
>>> sheet.getRow(3)
['Okra', '2.26', '38.half dozen', '87.24', '', '']
>>> sheet.updateRow(3, ['Pumpkin', '11.fifty', '20', '230'])
>>> sheet.getRow(3)
['Pumpkin', '11.50', 'twenty', '230', '', '']
>>> columnOne = sheet.getColumn(i)
>>> for i, value in enumerate(columnOne):
...     # Brand the Python list incorporate uppercase strings:
...columnOne[i] = value.upper()
...
>>> sheet.updateColumn(1, columnOne) # Update the entire cavalcade in one
request.

The getRow() and getColumn() functions call back the data from every cell in a specific row or column as a list of values. Notation that empty cells become blank string values in the list. You tin laissez passer getColumn() either a column number or letter to tell it to remember a specific cavalcade'due south data. The previous example shows that getColumn(i) and getColumn('A') render the same list.

The updateRow() and updateColumn() functions will overwrite all the data in the row or column, respectively, with the list of values passed to the function. In this example, the tertiary row initially contains data near okra, only the updateRow() phone call replaces information technology with data about pumpkin. Phone call canvass.getRow(iii) once more to view the new values in the third row.

Next, let's update the "produceSales" spreadsheet. Updating cells one at a time is slow if you have many cells to update. Getting a column or row every bit a list, updating the list, and then updating the entire cavalcade or row with the list is much faster, since all the changes can be made in one asking.

To get all of the rows at in one case, call the getRows() method to return a list of lists. The inner lists inside the outer list each represent a unmarried row of the sheet. You can modify the values in this data structure to change the produce proper noun, pounds sold, and total cost of some of the rows. And then you lot pass it to the updateRows() method by inbound the following into the interactive shell:

>>> rows = canvas.getRows() # Get every row in the spreadsheet.
>>> rows[0] # Examine the values in the offset row.
['PRODUCE', 'COST PER POUND', 'POUNDS SOLD', 'TOTAL', '', '']
>>> rows[1]
['POTATOES', '0.86', '21.half-dozen', '18.58', '', '']
>>> rows[one][0] = 'PUMPKIN' # Change the produce name.
>>> rows[i]
['PUMPKIN', '0.86', '21.6', 'xviii.58', '', '']
>>> rows[10]
['OKRA', '2.26', '40', '90.iv', '', '']
>>> rows[10][two] = '400' # Alter the pounds sold.
>>> rows[10][3] = '904' # Change the total.
>>> rows[10]
['OKRA', '2.26', '400', '904', '', '']
>>> sheet.updateRows(rows) # Update the online spreadsheet with the changes.

You tin update the entire sheet in a single request past passing updateRows() the list of lists returned from getRows(), amended with the changes made to rows ane and x.

Note that the rows in the Google Sheet accept empty strings at the stop. This is considering the uploaded sheet has a column count of half-dozen, simply we have only 4 columns of data. Y'all can read the number of rows and columns in a sail with the rowCount and columnCount attributes. Then by setting these values, you can change the size of the sheet.

>>> sheet.rowCount# The number of rows in the sheet.
23758
>>> sheet.columnCount# The number of columns in the sheet.
6
>>> sheet.columnCount = 4 # Alter the number of columns to 4.
>>> sheet.columnCount# At present the number of columns in the sheet is iv.
4

These instructions should delete the fifth and 6th columns of the "produceSales" spreadsheet, as shown in Figure 14-6.

Figure 14-6: The sheet before (left) and after (right) changing the column count to iv

According to https://support.google.com/drive/answer/37603?hl=en/, Google Sheets spreadsheets can have up to five million cells in them. However, it'southward a practiced idea to brand sheets but as large as you need to minimize the time it takes to update and refresh the data.

Creating and Deleting Sheets

All Google Sheets spreadsheets start with a single sheet named "Sheet1." You can add together additional sheets to the terminate of the list of sheets with the createSheet() method, to which you laissez passer a string to use as the new sheet'southward title. An optional second argument can specify the integer index of the new canvass. To create a spreadsheet and then add new sheets to it, enter the following into the interactive shell:

>>> import ezsheets
>>> ss = ezsheets.createSpreadsheet('Multiple Sheets')
>>> ss.sheetTitles
('Sheet1',)
>>> ss.createSheet('Spam') # Create a new sheet at the stop of the listing of
sheets.
<Sheet sheetId=2032744541, title='Spam', rowCount=one thousand, columnCount=26>
>>> ss.createSheet('Eggs') # Create another new sheet.
<Sheet sheetId=417452987, title='Eggs', rowCount=chiliad, columnCount=26>
>>> ss.sheetTitles
('Sheet1', 'Spam', 'Eggs')
>>> ss.createSheet('Bacon', 0) lawmaking># Create a sheet at index 0 in the list of
sheets.
<Sheet sheetId=814694991, title='Salary', rowCount=1000, columnCount=26>
>>> ss.sheetTitles
('Bacon', 'Sheet1', 'Spam', 'Eggs')

These instructions add together three new sheets to the spreadsheet: "Bacon," "Spam," and "Eggs" (in addition to the default "Sheet1"). The sheets in a spreadsheet are ordered, and new sheets go to the cease of the listing unless you pass a second argument to createSheet() specifying the sheet's alphabetize. Hither, you lot create the canvass titled "Bacon" at alphabetize 0, making "Salary" the first sheet in the spreadsheet and displacing the other three sheets by one position. This is similar to the behavior of the insert() list method.

Yous can see the new sheets on the tabs at the lesser of the screen, as shown in Figure xiv-7.

Figure fourteen-seven: The "Multiple Sheets" spreadsheet afterward calculation sheets "Spam," "Eggs," and "Salary"

The Sheet object'southward delete() method volition delete the sheet from the spreadsheet. If you want to continue the sheet simply delete the data information technology contains, call the clear() method to clear all the cells and make information technology a blank sheet. Enter the following into the interactive beat:

>>> ss.sheetTitles
('Bacon', 'Sheet1', 'Spam', 'Eggs')
>>> ss[0].delete()# Delete the sheet at alphabetize 0: the "Salary" sheet.
>>> ss.sheetTitles
('Sheet1', 'Spam', 'Eggs')
>>> ss['Spam'].delete() # Delete the "Spam" sheet.
>>> ss.sheetTitles
('Sheet1', 'Eggs')
>>> canvass = ss['Eggs']# Assign a variable to the "Eggs" sheet.
>>> sheet.delete()# Delete the "Eggs" sheet.
>>> ss.sheetTitles
('Sheet1',)
>>> ss[0].clear()# Articulate all the cells on the "Sheet1" sheet.
>>> ss.sheetTitles# The "Sheet1" sail is empty merely still exists.
('Sheet1',)

Deleting sheets is permanent; at that place's no way to recover the data. However, you tin back upwards sheets by copying them to some other spreadsheet with the copyTo() method, equally explained in the side by side section.

Copying Sheets

Every Spreadsheet object has an ordered list of the Sheet objects it contains, and you tin can use this listing to reorder the sheets (as shown in the previous department) or copy them to other spreadsheets. To copy a Sail object to another Spreadsheet object, call the copyTo() method. Pass information technology the destination Spreadsheet object every bit an statement. To create 2 spreadsheets and re-create the commencement spreadsheet's data to the other sheet, enter the post-obit into the interactive shell:

>>> import ezsheets
>>> ss1 = ezsheets.createSpreadsheet('Kickoff Spreadsheet')
>>> ss2 = ezsheets.createSpreadsheet('2nd Spreadsheet')
>>> ss1[0]
<Sail sheetId=0, championship='Sheet1', rowCount=g, columnCount=26>
>>> ss1[0].updateRow(ane, ['Some', 'data', 'in', 'the', 'first', 'row'])
>>> ss1[0].copyTo(ss2) # Copy the ss1'south Sheet1 to the ss2 spreadsheet.
>>> ss2.sheetTitles# ss2 now contains a copy of ss1'due south Sheet1.
('Sheet1', 'Re-create of Sheet1')

Note that since the destination spreadsheet (ss2 in the previous example) already had a canvass named Sheet1, the copied sheet will exist named Copy of Sheet1. Copied sheets appear at the terminate of the listing of the destination spreadsheet's sheets. If you lot wish, you tin can change their index attribute to reorder them in the new spreadsheet.

Working with Google Sheets Quotas

Because Google Sheets is online, information technology'southward easy to share sheets among multiple users who can all access the sheets simultaneously. However, this also means that reading and updating the sheets will be slower than reading and updating Excel files stored locally on your hard bulldoze. In add-on, Google Sheets has limits on how many read and write operations you lot can perform.

According to Google's developer guidelines, users are restricted to creating 250 new spreadsheets a day, and gratis Google accounts tin perform 100 read and 100 write requests per 100 seconds. Attempting to exceed this quota volition heighten the googleapiclient.errors.HttpError "Quota exceeded for quota group" exception. EZSheets volition automatically catch this exception and retry the asking. When this happens, the function calls to read or write data will have several seconds (or even a full minute or two) earlier they return. If the request continues to fail (which is possible if another script using the same credentials is also making requests), EZSheets will re-raise this exception.

This means that, on occasion, your EZSheets method calls may take several seconds earlier they return. If yous want to view your API usage or increase your quota, go to the IAM & Admin Quotas page at https://panel.developers.google.com/quotas/ to learn near paying for increased usage. If you'd rather just deal with the HttpError exceptions yourself, you can set ezsheets.IGNORE_QUOTA to True, and EZSheet'southward methods will raise these exceptions when information technology encounters them.

Summary

Google Sheets is a popular online spreadsheet application that runs in your browser. Using the EZSheets third-political party module, you can download, create, read, and modify spreadsheets. EZSheets represents spreadsheets equally Spreadsheet objects, each of which contains an ordered list of Canvass objects. Each sheet has columns and rows of data that you lot can read and update in several ways.

While Google Sheets makes sharing data and cooperative editing easy, its principal disadvantage is speed: y'all must update spreadsheets with web requests, which can have a few seconds to execute. But for most purposes, this speed restriction won't touch Python scripts using EZSheets. Google Sheets likewise limits how oftentimes you can make changes.

For complete documentation of EZSheet'due south features, visit https://ezsheets.readthedocs.io/.

Practice Questions

1. What 3 files do yous need for EZSheets to access Google Sheets?

2. What two types of objects does EZSheets have?

3. How can you create an Excel file from a Google Sheet spreadsheet?

4. How can you create a Google Canvass spreadsheet from an Excel file?

five. The ss variable contains a Spreadsheet object. What code volition read data from the cell B2 in a canvas titled "Students"?

6. How can you lot find the column letters for column 999?

7. How can yous notice out how many rows and columns a canvass has?

eight. How do you delete a spreadsheet? Is this deletion permanent?

ix. What functions will create a new Spreadsheet object and a new Sheet object, respectively?

10. What will happen if, by making frequent read and write requests with EZSheets, you exceed your Google account's quota?

Practice Projects

For practice, write programs to practise the post-obit tasks.

Downloading Google Forms Data

Google Forms allows you to create simple online forms that brand it easy to collect information from people. The data they enter into the form is stored in a Google Canvas. For this project, write a program that tin can automatically download the form data that users have submitted. Get to https://docs.google.com/forms/ and beginning a new course; it will be blank. Add fields to the form that ask the user for a name and email address. And so click the Send button in the upper right to become a link to your new form, such equally https://goo.gl/forms/QZsq5sC2Qe4fYO592/. Try to enter a few example responses into this form.

On the "Responses" tab of your form, click the greenish Create Spreadsheet push to create a Google Sheets spreadsheet that will hold the responses that users submit. Yous should see your example responses in the first rows of this spreadsheet. Then write a Python script using EZSheets to collect a list of the e-mail addresses on this spreadsheet.

Converting Spreadsheets to Other Formats

You can use Google Sheets to catechumen a spreadsheet file into other formats. Write a script that passes a submitted file to upload(). Once the spreadsheet has uploaded to Google Sheets, download it using downloadAsExcel(), downloadAsODS(), and other such functions to create a re-create of the spreadsheet in these other formats.

Finding Mistakes in a Spreadsheet

Later on a long twenty-four hour period at the bean-counting office, I've finished a spreadsheet with all the bean totals and uploaded them to Google Sheets. The spreadsheet is publicly viewable (but non editable). You can get this spreadsheet with the following code:

>>> import ezsheets
>>> ss = ezsheets.Spreadsheet('1jDZEdvSIh4TmZxccyy0ZXrH-ELlrwq8_YYiZrEOB4jg')

You tin expect at this spreadsheet in your browser past going to https://docs.google.com/spreadsheets/d/1jDZEdvSIh4TmZxccyy0ZXrH-ELlrwq8_YYiZrEOB4jg/edit?usp=sharing/. The columns of the first sheet in this spreadsheet are "Beans per Jar," "Jars," and "Full Beans." The "Total Beans" column is the product of the numbers in the "Beans per Jar" and "Jars" columns. However, there is a fault in i of the xv,000 rows in this sheet. That's too many rows to check by hand. Luckily, you lot can write a script that checks the totals.

As a hint, you can access the individual cells in a row with ss [0].getRow( rowNum ), where ss is the Spreadsheet object and rowNum is the row number. Recall that row numbers in Google Sheets begin at ane, not 0. The prison cell values will be strings, then you'll demand to convert them to integers so your plan can work with them. The expression int( ss [0].getRow(ii)[0]) * int( ss [0].getRow(2)[1]) == int( ss [0].getRow(two)[ii]) evaluates to Truthful if the row has the correct total. Put this lawmaking in a loop to identify which row in the sheet has the incorrect total.

caldwellmanothaver61.blogspot.com

Source: http://automatetheboringstuff.com/2e/chapter14/