0

I'm trying to catch a SQLAlchemy error and on error execute a Google Apps Script, which is deployed as an API executable.

Example error to catch:

[SQL: INSERT INTO mytbl (id, attr) VALUES (%(id)s, %(attr)s)]
[parameters: ({'id': 177, 'attr': u'dog'}, {'id': 178, 'attr': u'cat'})]
(Background on this error at: http://sqlalche.me/e/gkpj)

# omitted Traceback

IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "mytbl_pkey"
DETAIL:  Key (id)=(177) already exists.

.gs to be executed upon error. This runs as expected when run standalone.

function myfunct() {
  ss = SpreadsheetApp.openById("my_spreadsheet_id")
  ss.getRange('A10').setValue('Error');
}

.py to call .gs

# omitted libraries

try:
    # my stuff to get data and insert in db

except psycopg2.Error as e:
    logging.exception(str(e))
    error = e.pgcode


# If modifying these scopes, delete the file token.pickle.
    SCOPES = ['https://www.googleapis.com/auth/spreadsheets.currentonly']

    def get_scripts_service():
       
        creds = None

        if os.path.exists('token.pickle'):
            with open('token.pickle', 'rb') as token:
                creds = pickle.load(token)
        # If there are no (valid) credentials available, let the user log in.
        if not creds or not creds.valid:
            if creds and creds.expired and creds.refresh_token:
                creds.refresh(Request())
            else:
                flow = InstalledAppFlow.from_client_secrets_file(
                    '/my/path/oauth_client_secret.json', SCOPES)
                creds = flow.run_local_server(port=0)
            # Save the credentials for the next run
            with open('token.pickle', 'wb') as token:
                pickle.dump(creds, token)

        return build('script', 'v1', credentials=creds)


    service = get_scripts_service()
   
    SCRIPT_ID = "my_exec_api_id"

    request = {"function": "myfunct"}

    try:
        response = service.scripts().run(body=request, scriptId=SCRIPT_ID).execute()
        if 'error' in response:
            # The API executed, but the script returned an error.

            # Extract the first (and only) set of error details. The values of
            # this object are the script's 'errorMessage' and 'errorType', and
            # an list of stack trace elements.
            error = response['error']['details'][0]
            print("Script error message: {0}".format(error['errorMessage']))

            if 'scriptStackTraceElements' in error:
                # There may not be a stacktrace if the script didn't start
                # executing.
                print("Script error stacktrace:")
                for trace in error['scriptStackTraceElements']:
                    print("\t{0}: {1}".format(trace['function'],
                        trace['lineNumber']))

    except errors.HttpError as error:
       
        print(e.content)

sys.exit(1)

This doesn't output any errors or execute the .gs script. I'm new to both Python and the Execution API. Even after digging through error handling, I'm probably missing something obvious.

TheMaster
  • 45,448
  • 6
  • 62
  • 85
user8121557
  • 149
  • 2
  • 9
  • 1
    Please provide [mcve] – Cooper Jul 10 '20 at 22:45
  • Does it even get to the point of calling the API (1. is `except` reached? 2. is `service` reached? 3. Is `response` reached?) – Oleg Valter is with Ukraine Jul 12 '20 at 04:48
  • Did you try to build your credentials with `http=creds.authorize(Http()` as don in the [documentation](https://developers.google.com/apps-script/api/how-tos/execute#api_request_examples). Did you try to give the app more permissive scopes like `'https://www.googleapis.com/auth/spreadsheets` (do not forget to delete your token file after changing the scopes)? – ziganotschka Jul 14 '20 at 14:16

0 Answers0