Getting data from Google Sheets

Jonas Leite

I want to get data from a specific cell in Google Sheets. Example: I ask Alexa a question, Alexa how many pieces there are of item A. Then she tells me what is contained in cell A1. Can someone help me?

Russ

@Jonas-Leite, I asked the built-in AI Script Assistant and it gave me this script. I haven't tested it but it seems like it should work.

AI Prompt: python script on Windows to read cell A1 from a google sheet

To read cell A1 from a Google Sheet using Python on Windows, you can use the Google Sheets API and the gspread library. Here’s an example script:

First, you need to install the gspread library. Open a command prompt and run the following command:

pip install gspread

Next, you need to create a project in the Google Cloud Console and enable the Google Sheets API. Follow the instructions in this guide: https://gspread.readthedocs.io/en/latest/oauth2.html

Once you have created a project and enabled the Google Sheets API, you need to download the credentials file. Save it as “credentials.json” in the same directory as your Python script.

Now, you can use the following Python script to read cell A1 from a Google Sheet:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# define the scope of the API access
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']

# authenticate with the API using the credentials file
creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
client = gspread.authorize(creds)

# open the Google Sheet by its URL
sheet_url = 'https://docs.google.com/spreadsheets/d/your-sheet-id/edit#gid=0'
sheet = client.open_by_url(sheet_url)

# select the first worksheet
worksheet = sheet.get_worksheet(0)

# read the value of cell A1
cell_value = worksheet.acell('A1').value

print(cell_value)

Replace “your-sheet-id” with the ID of your Google Sheet

At the end of that script you'll also need to run:

%USERPROFILE%\.TRIGGERcmdData\SendResult.bat "cell_value"

Make sure you have {{result}} in the Voice Reply field in your command's config, and use one of the conversational skills. The Smart Home skill does not support the Voice Reply field.

Jonas Leite

Well, I understand the logic, but something is wrong. And I couldn't figure out what it is. Would you help me?

My command

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# define the scope of the API access
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']

# authenticate with the API using the credentials file
creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
client = gspread.authorize(creds)

# open the Google Sheet by its URL
sheet_url = 'https://docs.google.com/spreadsheets/d/1AgCtusxv7xgLq9G_Q7Ea5YQsEwjg8Klz/edit#gid=0'
sheet = client.open_by_url(sheet_url)

# select the first worksheet
worksheet = sheet.get_worksheet(0)

# read the value of cell A1
cell_value = worksheet.acell('E12').value

print(cell_value)


ERROR

Traceback (most recent call last):
  File "C:\Users\jonas\PycharmProjects\Teste do SHEETS\Sheets.py", line 8, in <module>
    creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jonas\AppData\Local\Programs\Python\Python311\Lib\site-packages\oauth2client\service_account.py", line 221, in from_json_keyfile_name
    return cls._from_parsed_json_keyfile(client_credentials, scopes,
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\jonas\AppData\Local\Programs\Python\Python311\Lib\site-packages\oauth2client\service_account.py", line 171, in _from_parsed_json_keyfile
    raise ValueError('Unexpected credentials type', creds_type,
ValueError: ('Unexpected credentials type', None, 'Expected', 'service_account')

The credential is correct, I took the test and managed to authenticate, but when I enter the command you gave me it doesn't work.

Russ

@Jonas-Leite I looked up the error in your output. This is the line that's failing:

creds = ServiceAccountCredentials.from_json_keyfile_name('credentials.json', scope)

You need to create your credential under "Service Accounts" according this this:
https://github.com/burnash/gspread/issues/393

Also you wrote, "... when I enter the command you gave me it doesn't work."

Which command, do you mean this one?

%USERPROFILE%\.TRIGGERcmdData\SendResult.bat "cell_value"

If you're running that from python it's actually something like this:

import os
import subprocess

home_folder = os.path.expanduser('~')
batch_script_relative_path = '.TRIGGERcmdData\SendResult.bat'
batch_script_path = os.path.join(home_folder, batch_script_relative_path)
parameters = [cell_value]
try:
    result = subprocess.run([batch_script_path] + parameters, capture_output=True, text=True, check=True, shell=True)
    print("Output:", result.stdout)
except subprocess.CalledProcessError as e:
    print("Error:", e)
    print("Output:", e.output)

FYI, for the SendResult.bat script to report your result, it needs to run in the context of your TRIGGERcmd command because the uses the TCMD_COMPUTER_ID and TCMD_COMMAND_ID environment variables.

Jonas Leite

This post is deleted!

Jonas Leite

This post is deleted!

Jonas Leite

@Russ I managed to get the code to work, see below. Is correct? It is taking the values from the spreadsheet.

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# Configurar as credenciais do Google Sheets
scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('serviceaccount.json', scope)
client = gspread.authorize(creds)

# Abrir a planilha pelo seu ID
sheet = client.open_by_key('1TuSui3-9aPT0jwJuEJ5S-VmBzue1jnKFbdCAprm_DIk')

# Acessar a planilha desejada
worksheet = sheet.get_worksheet(1)  # Supondo que seja a primeira aba da planilha

# Ler o valor de uma célula específica
cell_value = worksheet.acell('E11').value
print("Valor da célula E11:", cell_value)

import os
import subprocess

home_folder = os.path.expanduser('~')
batch_script_relative_path = '.TRIGGERcmdData\SendResult.bat'
batch_script_path = os.path.join(home_folder, batch_script_relative_path)
parameters = [cell_value]
try:
    result = subprocess.run([batch_script_path] + parameters, capture_output=True, text=True, check=True, shell=True)
    print("Output:", result.stdout)
except subprocess.CalledProcessError as e:
    print("Error:", e)
    print("Output:", e.output)

But I didn't understand how to put it in TriggerCMD.

I used the TRIGGERcmd command to execute the “test” command but Alexa only returns “no result.

Russ

@Jonas-Leite, what do you see in your results.log file in your .TRIGGERcmdData folder?
You should see something like this:

{"data":[{"name":"result","voice":"","voiceReply":"{{result}}","allowParams":false,"user":"blahblah","computer":"blahblah","switch_is_on":false,"runCount":1,"createdAt":"2020-01-13T15:08:55.389Z","updatedAt":"2020-01-13T15:10:16.077Z","lastResult":"The result","id":"5e1c8807692cfd00191fb558"}],"message":"Command result updated successfully."}

Also, when you run it via TRIGGERcmd, do you see your cell value in the runs list for your command?

Also, if you run the python script manually, outside of triggercmd, do you get output like this?

Output: 
C:\path>if "(cell value)" == "" goto noparams 

C:\path>goto yesparams 

C:\path>set /p TCMD_TOKEN= 0<C:\Users\you\.TRIGGERcmdData\token.tkn

C:\path>curl -X POST https://www.triggercmd.com/api/command/result -H "Authorization: Bearer (token)" -H "content-type: multipart/form-data" -F computer_id= -F command_id= -F result=(cell value)  1>>C:\Users\you\.TRIGGERcmdData\results.log

Also, I just remembered - you need to install curl if you haven't already because the SendResult.bat script uses it to send your result to TRIGGERcmd.
https://curl.se/windows/

Jonas Leite

This post is deleted!

Jonas Leite

@Russ
It worked. The error was in credential.json, TRIGGERcmd couldn't find the file, so I entered the data directly into the python file.

Good job. Thanks for the help my friend.