struggling to get Return values from google script

[Gokul1]

how do i insert the ewaybillno, i get from the google script, into  a column ?

my execution log 

Request successful. Response: {"success":true,"message":"E-Way Bill is generated successfully","result":{"ewayBillNo":571.........."ewayBillDate":"31/08/2023 11:12:00 AM","validUpto":"01/09/2023 11:59:00 PM","alert":""}}

[MultiTech]

Hey @Gokul1 

• You'll need to configure the script run task in your automation to accept a return value.
  • You can then utilize this value in subsequent steps.
    
    

You can see how this is done through the OpenAi sample app that I have

• link

This app shows how to implement a conversation system using OpenAi and AppSheet.

• The response is handled through a script, which in the task is configured to accept return values.
• You can check out the `Get Response` task for the specifics of how things are configured.

[Gokul1]

thank you this was helpful. Looking at your script it seems like the issue im facing i havent created a proper path to get the return values. 

i dont how do i get this data.choices[0]....from my logs 

Request successful. Response: {"success":true,"message":"E-Way Bill is generated successfully","result":{"ewayBillNo":571.........."ewayBillDate":"31/08/2023 11:12:00 AM","validUpto":"01/09/2023 11:59:00 PM","alert":""}}

[Marc_Dillon]

You use dereference notation. You have given your 'call a script' task a name, I always use "script" to make it easy, and you would have also named the returned values in the return values config section. So then in a subsequent task you'd use [script].[value]

This is described in the following article:

https://support.google.com/appsheet/answer/12309564?hl=en

[Gokul1]

the expression is not working...im getting: Error in expression '[script].[ewayBillNo]' : Unable to find column 'ewayBillNo',

[MultiTech · Accepted solution]

---

@Marc_Dillon wrote:

You use dereference notation. You have given your 'call a script' task a name, I always use "script" to make it easy, and you would have also named the returned values in the return values config section. So then in a subsequent task you'd use [script].[value]

---

This is only if the return is an object.

In the example I provided, instead of returning an object - which allows you to include multiple data types, but it can be more technical in the implementation - I instead return an array.

• If you return an array, all types have to be the same; this is why in the script example, you can see I'm converting things to a string

If you return an array, then your dot-notation in subsequent tasks to get returned values is: 

• [task_name].[output]
• Same for any regular return value as well

----------------------------------------------------------------------------------------------------------------

---

@Marc_Dillon wrote:

This is described in the following article:

https://support.google.com/appsheet/answer/12309564?hl=en

---

Best option: 

Copy the contents, go to ChatGPT, paste and ask it to help you. 🤖🧠💪

• Or ask Appster 😁 #ShamelessPlug

[MultiTech]

---

@Gokul1 wrote:

i dont how do i get this data.choices[0]

---

This is the structure of the returned object from OpenAi API.  I know how to set up this stuff in the code because the documentation for the API tells me how things will be structured; from this I can then build the dot-notation necessary to extract the specific element I want.

https://platform.openai.com/docs/api-reference/chat/create

-----------------------------------------------------------------------------------------------------------------

Your specifics return contents will depend on what your script is doing.

If you look at the object you provided, you'll likely want the "result" stuff

[Gokul1]

it worked! converted it tostring() and treated it as an array!!

[MultiTech]

Yaaaaay!

---------------------------------------------------------------------------------------------------------

It should be mentioned that returning an object will be better in the long run.

• But if you're not comfortable with JSON objects yet, working with an array might be easier to get things started.
• In the long run though... returning a JSON object will be preferable, as you can skip the type conversion to string and just return the native types (array, number, text, etc.).

[ThibaultHD]

I f***ing up my mind with these object return ...

I have a simple script that is calling a Google API to get me distance and duration for a ride. Here it is :

function getDistDur(xOrigin, xDestination, xarriveDateTime, apiKey) { 
  const xarriveDT = new Date(xarriveDateTime).toISOString();

  try {
    // Convert arrival time to a Unix timestamp in seconds
    const arrivalTimestamp = Math.floor(new Date(xarriveDT).getTime() / 1000);

    // Define the URL with parameters for Distance Matrix API
    const url = `https://maps.googleapis.com/maps/api/distancematrix/json?units=metric&origins=${encodeURIComponent(xOrigin)}&destinations=${encodeURIComponent(xDestination)}&departure_time=${arrivalTimestamp}&mode=driving&traffic_model=best_guess&key=${apiKey}`;
    
    // Fetch the response
    const response = UrlFetchApp.fetch(url);
    const data = JSON.parse(response.getContentText());

    if (data.rows[0].elements[0].status === "OK") {
      const element = data.rows[0].elements[0];
      const distanceMetre = element.distance.value;
      const distanceTime = element.duration_in_traffic.value;  

      return {
       "xdistanceMetre" : distanceMetre,
       "xdistanceTime" : distanceTime
    };

    } else {
      throw new Error("No route found");
    }

  } catch (err) {
    console.log(err.stack);
    return {
      "xdistanceMetre": "0",
      "xdistanceTime": err.message
    };
  }
}

The Apps Script Call does work as the logs show :

{"Success":true,"ReturnValue":"{ \"structValue\": { \"fields\": { \"xdistanceTime\": { \"numberValue\": 2088 }, \"xdistanceMetre\": { \"numberValue\": 28212 } } } }","Task Type":"AppsScript","Task Name":"Task for call DistDur aller","ScriptId":"1RXOm7BYVvwjk6nW6RTvSiA7J3lklmlcSupnnNSHMhKXZJUnQGv00HqWw","FunctionName":"getDistDur","FunctionArguments":"{ \"stringValue\": \"Kerdrouc'h, 29830 Plourin, France\" },{ \"stringValue\": \"Rue Alain Colas, 29218 Brest, France\" },{ \"stringValue\": \"11/17/2024 10:44:00\" },{ \"stringValue\": \"AIzaSyByVfsWaVYX5ZtwY1mh6LrdV6DbdC7KorM\" }"}

But I can't get to add a new row in a table using [call DistDur].[xdistanceTime]....

Why why why ????

Types are good (number everywhere, from table to Script to automation result (I set it as object with corresponding keys and type set to number...

[Marc_Dillon]

Have you configured those 2 return values in the call a script task?

[ThibaultHD]

Sure I did 🙂

Both as "Number"

Documentation seems pretty straight forward, but damn, I've lost my day on this...

[Marc_Dillon]

Maybe show screenshots of your full setup.

[scott192]

please show a screenshot of your bot's task that calls the Appscript Task

[ThibaultHD]

Yes sure !

Here it is :

That's my Apps Script call :

And the next step where I use the outputs to set values of a new row in another table (Courses) than the one (Events) that triggered the bot (might be something useful... )

And here is the Script itself :

function getDistDur(xOrigin, xDestination, xarriveDateTime, apiKey) { 
  const xarriveDT = new Date(xarriveDateTime).toISOString();

  try {
    // Convert arrival time to a Unix timestamp in seconds
    const arrivalTimestamp = Math.floor(new Date(xarriveDT).getTime() / 1000);

    // Define the URL with parameters for Distance Matrix API
    const url = `https://maps.googleapis.com/maps/api/distancematrix/json?units=metric&origins=${encodeURIComponent(xOrigin)}&destinations=${encodeURIComponent(xDestination)}&departure_time=${arrivalTimestamp}&mode=driving&traffic_model=best_guess&key=${apiKey}`;
    
    // Fetch the response
    const response = UrlFetchApp.fetch(url);
    const data = JSON.parse(response.getContentText());

    if (data.rows[0].elements[0].status === "OK") {
      const element = data.rows[0].elements[0];
      const distanceMetre = element.distance.value;
      const distanceTime = element.duration_in_traffic.value;  

      // Return the output in the desired format
      return {
        "distanceTime": Number(distanceTime), 
        "distanceMetre": Number(distanceMetre)
      };

    } else {
      throw new Error("No route found");
    }

  } catch (err) {
    console.log(err.stack);
    return {
      "distanceTime": 0,  // Adjusted default for error case
      "distanceMetre": 0  // Adjusted default for error case
    };
  }
}

ThX in advance for any tip you have 🙂

[Marc_Dillon]

Looks like you've mixed up your variable names. You use xDistanceTime in the script but then rename it to distanceTime in the task config return value. The latter is how you should refer to it later, with [call DistDur retour].[distanceTime] which is not what you said you tried.

[scott192]

Change the type of the returned values in your object to Decimal rather than Number?