@skadefro Hi, is there any way i can work with the google sheets in openrpa
Google has a REST API for Google sheets, you could maybe use that. Google Sheets API overview. So you could make API calls from OpenRPA. Don’t know your specific use case but you can use their API to a lot of stuff with Google Sheets.
1 Like
I have no examples using google sheet from openrpa, but there are multiple ways do it from nodered ( there is a “google” node that allows you to authenticate using one of 3 methods ) or using agent’s with python/nodejs
1 Like
Unfortunately, I never worked with GSheet in OpenRPA but once I saw a video on youtube from @Allan channel about GSheet and OpenFlow. Try to implement any of the suggestion from @Allan_Zimmermann or @vigneshn95.
There is a nuget package and python for GSheet if you prefer work with GoogleAPI like @vigneshn95 suggested.
Youtube:
NodeRed
If you can provide more information about what specifically you want to achieve, I might be able to assist you further.
i have a sheet with some data in google drive which i want to read and get that data in the open rpa as datatable. @kowts
You can work with Google Sheets in Python by using the Google Sheets API and the gspread library. Here’s are some info to help you get started:
You need set up your project in Google Cloud Console and Install the necessary libraries:
pip install gspread
pip install google-auth
Checkout this link for further instruction:
gspread docks
Guide step-by-step
Here’s a complete script that will help you (works for me):
from google.auth import credentials
from google.oauth2 import service_account
import gspread
import pandas as pd
# Load credentials and add permissions
credPathRoot = 'path/to/file/credentials.json'
scope = ['https://www.googleapis.com/auth/spreadsheets']
creds = service_account.Credentials.from_service_account_file(credPathRoot, scopes=scope)
# Authorize the client
client = gspread.authorize(creds)
# Open the Google Sheet
spreadsheet_id = 'your_spreadsheet_id'
sheet = client.open_by_key(spreadsheet_id).sheet1
# Get all values from the sheet as a list of lists
data = sheet.get_all_values()
# Convert the data to a DataTable
# Create a variable datatable in openrpa (System.Data.DataTable)
df = pd.DataFrame(data)
# Convert the DataFrame to a datatable
dataTable = df.to_dict('records')
# Print the datatable
print(dataTable)
Make sure to replace 'your_spreadsheet_id' with the actual ID of your Google Sheet. Remember to place the credentials.json file in a directory in your machine.
When you run this script, it will fetch all the data from the specified Google Sheet and print it as a Pandas DataFrame/DataTable. Then you can do further process or manipulate the data as needed.
Let me know if you need any further assistance!
2 Likes
You can use it in openrpa, using invoke code and usenpip install to install libs
Or as an agent and then add libs in requirements.txt
1 Like
In the step-by-step guide I shared there is a section called “Create Credentials”.
They explain how to get the json file and rename it to “credencials.json”.
You can find a way to generate the file inside the Service Account you create.
@kowts i did all the steps that you said but it is giving me this error when i running the bot
SyntaxError : (“(unicode error) ‘unicodeescape’ codec can’t decode bytes in position 2-3: truncated \UXXXXXXXX escape”, (‘’, 5, 15, None))
To resolve this issue, you can try one of the following solutions:
- Use a raw string: When specifying the file path, you can prefix it with an ‘r’ character to indicate a raw string. This will prevent the backslashes from being treated as escape characters. Here’s an example:
credPathRoot = r'path\to\file\credentials.json'
- Double backslashes: Alternatively, you can use double backslashes to escape the backslash character. Here’s an example:
credPathRoot = 'C:\\path\\to\\file\\credentials.json'
Either of these approaches should help resolve the SyntaxError related to the file path.
Let me know if it works!
@kowts after doing that. now it is giving me this error of module name not found
No module named ‘google’
Use pip install activity to install the module
use like this in the field Modules: {‘gspread’, ‘google-auth’}
According to the error message the problem is the expressin. Please change the quotation marks to simple[ ’ ] or double [ “ ]
1 Like
@kowts Hi. now it is giving me this error-# APIError : {‘code’: 403, ‘message’: ‘The caller does not have permission’, ‘status’: ‘PERMISSION_DENIED’}