Read Google Sheets in Python with no-code MLJAR Studio

MLJAR Studio is a desktop application for creating Python scripts. It has a graphical user interface (GUI) for code generation. In this post we would like to show you, how to easily create a Python script for reading data from Google Sheets in Python **without writing a code** - code will be generated through code-forms.

The MLJAR Studio notebook

MLJAR Studio is using the Python Notebook approach for creating the scipts. The code blocks are organized into cells which are sequentially executed. Each cell can be generated with GUI or it can be simply Python code or Markdown text.

The example of MLJAR Studio cell is in the picture below. There are three areas:

• menu with available integrations - each integration has a set of steps.
• form for selected step - it is a graphical interface where user can provide additional information.
• text editor with generated Python code. (The code can be edited manually as well.)

When the code is ready, user clicks Run button to execute the code. The menu and form are hidden. The output from execution is displayed under the cell.

Google Sheets integration with gspread

The MLJAR Studio is using gspread package to connect with Google Sheets. (It can be installed in MLJAR Studio with one-click but its a topic for the next article.)

• gspread repository https://github.com/burnash/gspread
• gspread documentation https://docs.gspread.org/

The gspread is a Python wrapper for Google Sheets API and makes accessing data from Spreadsheets easy. But what is maybe more important, it makes the code clean and readable - you don't need to have PhD in Computer Science to understand what's going on.

Read Spreadsheet data

The process of importing data from Google Sheets can be divided into several steps:

• Authenticate
• Share Spreadsheet with Service Account email
• Select Spreadsheet
• Select Worksheet
• Read data from Worksheet

Authenticate

Firstly, we need to authenticate our script to read data from selected Spreadsheet. We assume that you already have Service Account and JSON file with credentials. We describe it in the separate article.

The below a Python code that authenticates script to the Google Sheets and list all spreadsheets that are available.

gspread_client = gspread.service_account(filename="path_to_your.json")
# list all available spreadsheets
spreadsheets = gspread_client.openall()
if spreadsheets:
    print("Available spreadsheets:")
    for spreadsheet in spreadsheets:
        print("Title:", spreadsheet.title, "URL:", spreadsheet.url)
else:
    print("No spreadsheets available")
    print("Please share the spreadsheet with Service Account")

This is how it looks in the MLJAR Studio with example output (please notice that each step have different icon and title on the left of the cell):

Share Spreadsheet with Service Account email

If you don't see the title of your Spreadsheet that you would like to import, then you need to share it with Service Account email. What email? The Service Account has a special email that is associated with it. You can get the email from JSON file or display it in the Python.

print("Please share spreadsheet with", gspread_client.auth.signer_email)

You need to share the Spreadsheet with printed email as you would share with a friend. Thanks to this step you have better control over Spreadsheets remote access.

Select Spreadsheet

The Spreadsheet can be easily accessed in the Python by title (in my opinion the easiest way but you can access Spreadsheets by URL or ID).

spreadsheet = gspread_client.open(title="pokemons")
# Print available worksheets
for wrk in spreadsheet.worksheets():
    print("Worksheet:", wrk.title)

Please notice that the code for Spreadsheet selection also list all available Worksheets. It makes the next steps easier. You will know what Worksheets are available.

Select Worksheet

The Worksheet can be selected by title. The default title for the first Worksheet is Sheet1.

wrk = spreadsheet.worksheet(title="Sheet1")
# Print A1 cell
print("A1 cell value:", wrk.get("A1"))

After the Worksheet selection, the A1 cell value is printed. It is to give you more information about selected Worksheet.

Read data from Worksheet

Although the value from A1 cell was displayed in the previous step, let's list fetch options:

• get value from single cell, for example A1 (get("A1")),
• get value from cells range, for example A1:D4 (get("A1:D4")),
• get values from all cells (no arguments needed in get() method).

Let's get data from cells range and print them:

wrk_data = wrk.get("A1:M2")
# Print selected range
print("Selected range A1:M2 values:")
print(wrk_data)

It's all. We have imported data from Google Sheets to our Python script.

The MLJAR Studio notebook full code

This is how full code for reading data from Google Sheets in Python looks in MLJAR Studio.

The notebook created in MLJAR Studio is compatible with Jupyter Notebook and can be easily displayed there:

Everything in the 2 miuntes video

Above steps summarized in less than 2 minutes video.

Summary

We hope that MLJAR Studio will give an opportunity for many people to create Python programs. What will be available soon in the application:

• notebook scheduling
• GUI application builder with drag-and-drop interface for widgets (ipywidgets of course)
• support for charts creation with GUI
• deploy notebooks on remote servers as apps/dashboards/REST API

We really think it is all possible in the new notebook! What is more, the created code is controlled by you, running on your machine, it can be easily converted to Python plain script or Jupyter Notebook. No vendor lock-in.

The application is still under the development. If you would like to be informed about release please fill the form.

We are looking for feedback or comments from you. You can reach us by email to Ola.