@kowts i did all the steps that you said but it is giving me this error when i running the bot
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’}
I think this is happening because the gmail user need to set permissions as an editor to in the Excel document.
For additional information, check the section " Grant Access" in the “Guide step-by-step” for more information about permissin.
1 Like
hI @kowts . need your help with something. can i get the spreadsheet id fro a google drive then read that spreadsheet later
If file is there in google drive than you can use google drive sync software to sync file to local file this way you can update file stored in google drive using simply using excel activities.(App scripts feature of google sheet will not work if file stored in google drive )
Hi @Shivendra_Pratap
Interesting, you asked because I was helping a colleague to archive the same things that you asked. I just gave him some ideas on how to do it (don’t know if it will work). Try this:
In order to obtain the spreadsheet ID from a Google Drive using gspread, you’ll first need to have the title or name of the spreadsheet. The spreadsheet ID is part of the URL of the Google Sheets document.
There is a function list_spreadsheet_files() will return a list of dictionaries, each representing a spreadsheet. Each dictionary contains information about a spreadsheet, including its ID and title.
import gspread
from google.oauth2.service_account import Credentials
# Define the scope
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
# Add your service account file
creds = Credentials.from_service_account_file('path/to/file/credentials.json', scopes=scope)
# Authorize the client
client = gspread.authorize(creds)
# Fetch the list of spreadsheets
spreadsheet_list = client.list_spreadsheet_files()
# Initialize the spreadsheet_id to None
spreadsheet_id = None
# Loop through all available spreadsheets to find the one with the matching title
for spreadsheet in spreadsheet_list:
if spreadsheet['name'] == 'your_spreadsheet_title':
spreadsheet_id = spreadsheet['id']
break
# Check if the spreadsheet was found
if spreadsheet_id:
print('Spreadsheet ID:', spreadsheet_id)
else:
print('No spreadsheet with title', 'your_spreadsheet_title', 'found.')
Please note,You need to use your service account previewly created and the service account needs to have the necessary permissions to access the Google Drive where the spreadsheet is stored.
@kowts Thanks for your response. But it is giving me this-No spreadsheet with title ImagesLinks found.
If the script is returning “No spreadsheets found,” this means that the list_spreadsheet_files() function isn’t returning any spreadsheets. There could be a few reasons for this but I think is related with permissions.
Open the Google Cloud Console, you can select roles to give your service account the necessary permissions with Roles like ‘Editor’, ‘Owner’, or ‘Viewer’ will work for accessing Drive and Sheets.
Now, your service account should have the necessary permissions to access the Drive and Sheets APIs.
Full code: https://pym.dev/p/32q5e/
@kowts I have given the roles and permissions in my service account. but still i cannot be able to fetch the spreadsheet id
@Shivendra_Pratap Open your credentials.json and check the service account email in the JSON key file, It should be a value for the “client_email” key.
That’s “client_email” you need to grant access as “owner” or “editor” and share your spreadsheet with the service account email.
1 Like