15GOOGLE SHEETS

Google Sheets is a free, web-based spreadsheet application, and the EZSheets package offers a simplified form of its official API that enables your Python code to interact with online spreadsheets. Using EZSheets, you can download, create, read, and modify spreadsheets directly from your Python code.

A simple drawing of a light bulb. LEARNING OBJECTIVES

  • Create authentication credentials the EZSheets package can use to access spreadsheets from your Google account.
  • Know the difference between the online Google Sheets spreadsheets and offline Excel spreadsheet files.
  • Read and update the data in your Google Sheets spreadsheets.
  • Add, reorder, and remove individual sheets from a spreadsheet.
  • Gather data from the public through Google Forms and retrieve the data with Google Sheets.
  • Understand how the Google Sheets API works and limitations of account quotas.

A grey circle with a white question mark at the center Practice Questions

These questions test your knowledge of EZSheets setup, reading and editing spreadsheets, and the structure of Spreadsheet and Sheet objects.

Installing and Setting Up EZSheets

Before your Python scripts can use EZSheets to access and edit your Google Sheets spreadsheets, you need a credentials JSON file and two token JSON files. Google may slightly change the layout or wording on its Google Cloud Console website, but the basic steps of this one-time setup process should remain the same.

  1. 1. Can you use Google Sheets for free?

  2. 2. Do you need to enter your Google account password into your Python source code to use EZSheets?

  3. 3. At what website can you download the credentials JSON file for your Google account?

  4. 4. Which two Google APIs must you enable to use EZSheets?

  5. 5. After you download your credentials, what files will be created the first time you run import ezsheets?

  6. 6. What should you do if you accidentally share your credentials file?

  7. 7. Are the project name (which looks like “My Project 23135”) and project ID (which looks like “macro-nuance-362516”) visible to the users of your Python programs who import EZSheets?

Spreadsheet Objects

In Google Sheets, a spreadsheet can contain multiple sheets (also called worksheets), and each sheet contains columns and rows of cells. You can make a new Spreadsheet object from an existing Google Sheets spreadsheet, a new blank spreadsheet, or an uploaded Excel spreadsheet. All Google Sheets spreadsheets have a unique ID included in their URL, after the spreadsheets/d/ part and before the /edit part.

  1. 8. Which function creates a new spreadsheet?

  2. 9. Excel files use the filename extension .xlsx. Which function uploads a .xlsx spreadsheet to Google Sheets?

  3. 10. Do Spreadsheet objects have titles? If so, how can you access them?

  4. 11. If you open the string in the url attribute of a Spreadsheet object in your browser, what do you see?

  5. 12. Which function converts a spreadsheet to an Excel file and downloads it?

  6. 13. In what six spreadsheet formats can you download your Google Sheets spreadsheet?

  7. 14. How can you list all spreadsheets in your Google account?

  8. 15. If you call the delete() method on a Spreadsheet object, is it permanently deleted?

  9. 16. If someone changes your spreadsheet in their web browser while your Python program is running, how can you update the local Spreadsheet object in your program?

Sheet Objects

A Spreadsheet object contains one or more Sheet objects. The Sheet objects represent the rows and columns of data in each sheet. You can access these sheets using the square brackets operator and an integer index, much like accessing values in a list.

  1. 17. What method creates a new, blank Sheet object?

  2. 18. How can you access the Sheet objects of a Spreadsheet object?

  3. 19. Do Sheet objects have titles?

  4. 20. Say you have a Sheet object in a variable named sheet. How do you set the value in cell C5 to the text “Hello”?

  5. 21. If cell D9 is set to the integer 30, does sheet['D9'] return an integer or a string?

  6. 22. How do you copy an entire Sheet object of a Spreadsheet object to a different Spreadsheet object?

  7. 23. Use the getColumnLetterOf() function to calculate the column letters for column 900. What is the result?

  8. 24. Use the getColumnNumberOf() function to calculate the column number for column ZZ. What is the result?

  9. 25. What does the code ezsheets.convertAddress(2, 3) return: B3 or C4?

  10. 26. What does the code ezsheets.convertAddress('A2') return?

  11. 27. What Sheet methods can return an entire column or an entire row of cells at once?

Google Forms

Your Google account also gives you access to Google Forms at https://forms.google.com/. You can create surveys, event registrations, or feedback forms with Google Forms, then receive the answers that users submit in a Google Sheets spreadsheet. Using EZSheets, your Python programs can access this data from the spreadsheet.

  1. 28. How is Google Forms related to Google Sheets?

  2. 29. If someone updates a Google Sheets spreadsheet by filling out a form on Google Forms while your Python program is running, which method will update the local Spreadsheet object in your program?

Working with Google Sheets Quotas

Because Google Sheets is online, you can easily share sheets among multiple users who can all access the sheets simultaneously. Google Sheets has quotas limiting how many read and write operations you can perform, however.

  1. 30. Does Google restrict how many spreadsheets an account can create per day?

  2. 31. What happens if your code exceeds the activity limit of the Google Sheets API?

  3. 32. Can you check your account’s API usage on the Google Cloud Console website?

A simple drawing of a sharpened pencil. Practice Projects

Try the following short projects to practice working with Google Sheets.

Uploading All Files in a Folder

Write a function named upload_all_spreadsheets() that searches for all .xlsx and .csv files in the current working directory and uploads them to Google Sheets. Before uploading each spreadsheet, print the string f'Uploading {filename}...' to indicate the upload progress.

Save this function in a file named uploadAllSpreadsheets.py.

Google Sheets Home Folder Report

This project is similar to the “Excel Home Folder Report” project in Chapter 14 of this workbook. Write a program that lists the 100 largest files on your computer, along with their sizes, in a nicely formatted Google Sheets spreadsheet that you can share with your boss before the end of the day. They will, once again, forget to read it.

Start this project by writing a function named get_home_folder_size() that returns a list of tuples. Each tuple has two items: a filename string and an integer of the file’s size in bytes. Using the os.listdir() function and the stat() method of Path objects discussed in Chapter 10 of Automate the Boring Stuff with Python, your Python program can examine every file in the home folder.

Then, write a function named make_google_sheets_report() that calls the get_home_folder_size() function and places the results in a Google Sheets spreadsheet with the title Home Files Report. In this spreadsheet, column A should list the filenames and column B should list the file sizes.

Save this program in a file named homeFilesReportGoogleSheets.py.