Failed App Script type translation: Schema has more than one column but no object was returned

[SEIU_503]

I'm getting this error on a Bot and can't figure out which step it's coming from. Based on a similar question asked by another user (https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Gemini-Script-Extracts-Data-Perfectly-But-AppSh...) I'm guessing it has to do with the return value of one of the steps not being in the correct format. But I can't figure out which step is throwing the error, or how I would change the return format if I found it.

Here's what the bot looks like. It's supposed to do this:

1. An app user clicks a button which calls a task that updates a timestamp on the user record.
2. This triggers a call to an AppsScript called 'getUser', which sends an API call to Salesforce to return a User record that matches the logged-in app user's email.
3. If getUser is successful, the bot then calls a second AppsScript called 'loadUserTurf', which sends a second API call to Salesforce to retrieve a list of contacts that belong to the logged in user.

All of the writing data (User and Contact fields) back to the data source (Google sheet) happens in the script -- using something like `sheet.appendRow(row)` with the data retrieved from Salesforce. So the return value of the steps in the process don't include any row data to write to the data source; they're just returning 'Success', or 'Error'.

So for example the loadTurf step (which calls the loadTurf AppsScript) looks like this

and then I am checking for Success = TRUE in the next step in the process. But it's not returning the actual sheet data (contact records) to the bot.

I have reviewed the Process > Steps > Detail pane for each step in the process in the Runs tab of the Monitor dashboard, and each step appears to be returning the data I would expect, and I can't figure out where exactly the issue is. I do see that in 'Task Properties', the 'Success' key appears to be returned as part of a JSON string and the formatting is not what I would expect (doesn't look like the object in the 'Output Data' key just above it):

Could that be the issue? If so, how would I change the format here? 
If that's not it, any suggestions for figuring out what that error message is referring to?

[TeeSee1]

As far as I know, return values from a Call a script step you have access to are the ones you specifically return from your function using a return statement.

Your function is "directly" updating the spreadsheet (not using Appsheet API) and the bot does not know the data used to write the row.

If you want to use the data, apparently retrieved from Salesforce in your Apps Script, in the next step of the bot, you have to explicitly return it from your script.

[scott192]

If Salesforce can recieve an API request then why are you using Appscript Tasks as an extra step when you could just use the built in Appsheet API?

API calls can now get values returned from the service you are calling and you can use Appsheet to write those values to your data sources.  Is there a reason you haven't done this?

[SEIU_503]

@scott192 @TeeSee1 thank you for your responses. I am only using the bot for client-side error handling. So the only things I am trying to consume from the API calls sent by the script are a boolean 'Success' value and a string for an Error if one exists -- all the actual row data returned by the API is being written to the Google sheet programmatically by the script. I am explicitly returning the Success and Error values from the script, as you can see in the second screenshot in the initial post.

Also for the first two months after I built this app, the 'Success' and 'Error' values were being successfully returned. There seems to be some kind of new bug in the way the Success and Error keys are being returned and consumed by AppSheet, and I was trying to figure out what was causing that, or if there's a way to control how those values are returned so that AppSheet can successfully convert them.

I guess if there were a relatively easy way to do the client-side error-handling with Apps Script, I could use the bot ONLY to trigger the script, and then let the script handle everything else from there. But building out my own client-side error handling seems to defeat my whole reason for using AppSheet to begin with, which was to take advantage of a pre-packaged and consistent UI so I don't have to build and maintain a new front end for each app.

Do you know of any examples of roll-your-own client-side error handling built in Apps Script for this use case? Or does anybody have any suggestions on how to troubleshoot the type translation error I'm getting on the 'Success' and 'Error' keys?

[TeeSee1 · Accepted solution]

---

@SEIU_503 wrote:

. I am explicitly returning the Success and Error values from the script, as you can see in the second screenshot in the initial post

---

From the Task Properties screen shot I do not see a return value your script is returning because the Returnvalue key contains a nullvalue. The first Success value is not the usable in your bot.

[SEIU_503]

@TeeSee1 aaahh thank you! I saw the 'Success' key at the beginning of the object and assumed that was the one I was looking for. It looks like my return values are not working. Do you have any suggestions for troubleshooting that?

This is how I'm trying to do it at the end of the script, do I need to nest it in a key called 'ReturnValue' or is there something else I'm missing?

[SEIU_503]

I figured this out, I was returning the value from a secondary function instead of from the function actually called by AppSheet 🫠... Thank you for your help and for pointing me in the right direction!

[TeeSee1]

Uh, that explains it.