• Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Register
  • Login
TRIGGERcmd
  • Categories
  • Recent
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Register
  • Login

Getting data from Google Sheets

General Discussion
2
10
702
Loading More Posts
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • J
    Jonas Leite
    last edited by Feb 29, 2024, 11:53 AM

    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?

    R 1 Reply Last reply Feb 29, 2024, 10:35 PM Reply Quote 0
    • R
      Russ @Jonas Leite
      last edited by Russ Feb 29, 2024, 10:45 PM Feb 29, 2024, 10:35 PM

      @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.

      Russell VanderMey

      1 Reply Last reply Reply Quote 0
      • J
        Jonas Leite
        last edited by Jonas Leite Mar 1, 2024, 5:38 PM Mar 1, 2024, 4:44 PM

        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.

        R 1 Reply Last reply Mar 1, 2024, 9:34 PM Reply Quote 0
        • R
          Russ @Jonas Leite
          last edited by Mar 1, 2024, 9:34 PM

          @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.

          Russell VanderMey

          J 2 Replies Last reply Mar 5, 2024, 5:37 PM Reply Quote 0
          • J
            Jonas Leite @Russ
            last edited by Mar 5, 2024, 5:37 PM

            This post is deleted!
            J 1 Reply Last reply Mar 5, 2024, 7:59 PM Reply Quote 0
            • J
              Jonas Leite @Jonas Leite
              last edited by Mar 5, 2024, 7:59 PM

              This post is deleted!
              1 Reply Last reply Reply Quote 0
              • J
                Jonas Leite @Russ
                last edited by Mar 5, 2024, 9:42 PM

                @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.

                e8ecb844-d5ad-438c-b843-2a71c5827f6a-image.png

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

                R 1 Reply Last reply Mar 5, 2024, 10:01 PM Reply Quote 0
                • R
                  Russ @Jonas Leite
                  last edited by Russ Mar 5, 2024, 10:08 PM Mar 5, 2024, 10:01 PM

                  @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/

                  Russell VanderMey

                  J 2 Replies Last reply Mar 6, 2024, 1:58 PM Reply Quote 0
                  • J
                    Jonas Leite @Russ
                    last edited by Mar 6, 2024, 1:58 PM

                    This post is deleted!
                    1 Reply Last reply Reply Quote 0
                    • J
                      Jonas Leite @Russ
                      last edited by Mar 7, 2024, 3:35 AM

                      @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.

                      1 Reply Last reply Reply Quote 0
                      8 out of 10
                      • First post
                        8/10
                        Last post