I am trying to launch a python function using Gspread from excel via xlwings. For the context, I am doing this to be able to import data from a google sheet 'database' into my hard-copy excel file. The function works perfectly when I launch it from a Python console but doesn't seem to work when I launch it from Excel via a macro using RunPython.
More precisely, I have added flags to see where was the problem:
def main():
wb = xw.Book.caller()
wb.sheets['Dashboard'].range('J3').value=0#used as a flag
import gspread
from oauth2client.service_account import ServiceAccountCredentials
wb.sheets['Dashboard'].range('J3').value=1.5#used as a flag
# use creds to create a client to interact with the Google Drive API
scope = ['https://spreadsheets.google.com/feeds']
wb.sheets['Dashboard'].range('J3').value=1.6#used as a flag
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
wb.sheets['Dashboard'].range('J3').value=1.7#used as a flag
client = gspread.authorize(creds)
wb.sheets['Dashboard'].range('J3').value=1#used as a flag
The value that I get in my J3 cell after trying to run the program is 1.6, meaning that the line that causes the problem is:
creds = ServiceAccountCredentials.from_json_keyfile_name('client_secret.json', scope)
The file client_secret.json is in the same working directory as both the python script and the excel file. If you have any idea on what could be the problem and how I can solve it, that would be great!
Thank you for the help!
As specified by @Felix Zumstein, the solution is to specify the full path of the 'client_secret' json file, possibly using os.path.dirname(os.path.abspath(file))