How do I get a cell's color from a Google Sheets doc in Colab (python) without Google API?

Question

I am importing a Google Sheets document into Google Colab for data analysis using python. I would like to get formatting information from the Google Sheets document, particularly cell background color. I know this can be done with Google API, but I'll need it longer than the free trial and don't want to pay for their service. I know this can be done with Google Scripts, but I'd rather keep in all in Colab.

I can successfully import the google sheets document without Google API with gspread.

import pandas as pd
from google.colab import auth
auth.authenticate_user()
import gspread
from google.auth import default
creds, _ = default()
gc = gspread.authorize(creds)

wb = gc.open_by_url('https://docs.google.com/spreadsheets/d/<my_spreadsheet_id>')
sheet = wb.worksheet('Assignments')
data = sheet.get_all_values()
df = pd.DataFrame(data)

Is there a way to get the individual cell formatting data too? I can't seem to find it.

Answer 1

Several points

I can successfully import the google sheets document without Google API with gspread

• gspread is a library does allows you to use the Google Sheets API in a different way. However it is based on the Google Sheets API and using it is not different than using the Sheets API without this library.

2. The Google Sheets API is free to use. What might not be free after a trial period is a Google Workspace account. This means having a Google Drive and create Google Sheets on this Drive. Thereby, it is not relevant either you use the Sheets API or not, because it is not the usage of the API you need to pay for.
3. Apart forum using a paid Workspace account, you can also opt for using a free consumer account
4. When it comes to Google Colab, you also have the option to select between a free and a paid account:

5. The probably most relevant issue for you:

   When it comes to the usage of Google Cloud Services, you will find that most products can be used for free as long as you don't surpass the quota limits. When you sign up for the free trial, you will see the following message:

So, do not worry about being charged for something you do not want to pay for.