[Python] I can run an RPA exe by going to the server directory and double clicking it, but get...

I've built an RPA that runs fine as a .py. I turned it into an .exe and it ran fine that way as well. Next I moved it to the production server that it will run from and using File Explorer, double clicked on the exe and again it ran fine.

The job is scheduled to run from a SQL Server Job Scheduler, and when it's ran through the scheduler, that's when we get the error:



But the file token.json IS there.

The Job Scheduler setup is this:



Below is the excerpt from the log file created. The first StartOfJob is the result of running it from the SQL Job Scheduler, you can see in the log that it's looking for the token and that's when the error message above pops up. The second StartOfJob is what I got after opening a command prompt and running it myself from a command line, it worked.

2025-04-24 09:38:31,280 ===========S T A R T O F J O B===================== 2025-04-24 09:38:31,280 Step 1. Gather Credentials 2025-04-24 09:38:31,280 Looking for token.json in \\LocationOfServices 2025-04-24 14:02:46,845 ==========S T A R T O F J O B====================== 2025-04-24 14:02:46,847 Step 1. Gather Credentials 2025-04-24 14:02:46,848 Looking for token.json in \\LocationOfServices 2025-04-24 14:02:46,876 token.json exists, use token to establish identity 2025-04-24 14:02:47,135 token.json exists but is expired, refresh and retry 2025-04-24 14:02:47,141 token.json has now been created

I don't have access to test from our SQL Server, so I know a big difference is that I'm running it as a mapped shared drive from my computer, where as the SQL Server job scheduler is running it directly.

My DBAs are running it from the Job Scheduler using a service account that has All-Access/Admin privileges on the server.

Below is the code for the py in case that's relevant. Other than that I'm not sure what else to include to help, but if anybody wants to offer suggestions on what else is needed I will be happy to provide.

Thanks in advance for any help you can provide in figuring this out!

import os
import logging
import pysftp
import requests
import sys
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError

#local defs
download_path = "\\\<ServerName>\\LocationOfServices"
SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]
SPREADSHEET_ID = "<SpreadsheetID>"
OUT_DIR = 'output/'
OUT_FILENAME = "jcpsLocationOfServices.csv"

#Set up logging
logger = logging.getLogger(__name__)
fileHandler = logging.FileHandler(os.path.join(download_path, "logs.txt"))
formatter = logging.Formatter("%(asctime)s %(message)s")
fileHandler.setFormatter(formatter)
logger.addHandler(fileHandler)
logger.setLevel(logging.INFO)
logger.info("=====================S T A R T O F J O B=================================")

#Main function to loc ingo Google Sheet
def main():
logger.info("Step 1. Gather Credentials")
credentials = None
logger.info(f" Looking for token.json in {download_path}")
if os.path.exists(os.path.join(download_path, "token.json")):
credentials = Credentials.from_authorized_user_file("token.json", SCOPES)
logger.info(" token.json exists, use token to establish identity")
else:
logger.info(" Cannot find token.json")

if not credentials or not credentials.valid:
if credentials and credentials.expired and credentials.refresh_token:
credentials.refresh(Request())
logger.info(" token.json exists but is expired, refresh and retry")
else:
flow = InstalledAppFlow.from_client_secrets_file("credentials.json", SCOPES)
credentials = flow.run_local_server(port=0)
logger.info(" token.json does not exist, use the credentials.json file to contact Google and create it")
with open(os.path.join(download_path, "token.json"), "w") as token:
token.write(credentials.to_json())
logger.info(" token.json has now been created")

try:
logger.info("Step 2. Send Google Sheet URL to the API and set up usage permissions")
#if Google Sheet contains more than one tab, the tab id is the "gid=" at the end of the URL
url = f'https://docs.google.com/spreadsheets/export?id=<SpreadsheetID>&exportFormat=csv&gid=<GridID>'
response = requests.get(url, headers = {'Authorization': 'Bearer ' + credentials.token})
if response.status_code == 200:
<ftp data>
else:
logger.info(f' *****ERROR***** downloading Google Sheet: {response.status_code}')

except HttpError as error:
logger.info(f'*****ERROR***** {error}')
print(error)

if __name__ == "__main__":
main()
logger.info("Step 4. Exit program - CONGRATS!!")
sys.exit(0); ## success


ADDENDUM:

The link to the post that solved my issue was a tremendous help and got me pointed in the right direction, but it wasn't 100% what my issue was.

If you look at these two lines in my code, the second line was the problem:

if os.path.exists(os.path.join(download_path, "token.json")):
credentials = Credentials.from_authorized_user_file("token.json", SCOPES)


In the first line I'm looking for the existence of the file by passing in the file path, so it was finding it. But then on the second line, I'm trying to open the file WITHOUT supplying the file path, so it was trying to look for the file in the temporary folder and not finding it. Once I added the file path to the second line, it started working. I'm not sure if I would have ever caught that, though, without reading about a temporary directory being created. So a huge thanks to @sevC_10 and @furas for providing links and explaining about the temporary folder that gets created!!

Continue reading...