App script just 'Fails' with no log information to troubleshoot!

[arimakidd]

I've created a an app script that works with Appsheets. From appsheet I'm calling a webhook and passing the the URL of the app script.  The app script is designed to create a new Task in Workspace 'My Tasks'.  Based on the appsheet monitor everything runs fine.  But the app script executions Status only says, "Failed".  I have no idea where to start.  Why don't we get better log data?  Can someone help me?

[Marc_Dillon]

---

@arimakidd wrote:

appsheet I'm calling a webhook and passing the the URL of the app script

---

It sounds like you're sending a webhook to a web-deployed app script. That's the old way of doing this. Nowadays we have a "call a script" task type that you can use within the bot. This should be a lot easier to use.

And to answer your question about the web-deployed script's logging capabilities, that has nothing to do with Appsheet, GAS web apps can't output to the normal log like a typical GAS can. I typically set up a GSheet that I send any log messages to when I need to.

[scott192]

I believe that using a webhook to POST to an Appscript endpoint will always fail as Appscript does not return what is written in your return statement.  Instead, Appscript seems to return a url link that when clicked then displays the results of your return statement.  You can usually configure the application that calls the webhook to follow the redirect and get the returned values.

Appsheet on the other hand does not expect this and will not follow the link automatically to get the Appscript returned values.  I don't think it is possible to reconfigure Appsheet to follow redirects.

Using an Appscript Task solves all these problems.

[arimakidd]

@scott192 and @Marc_Dillon Thank you so much for your response.  Looks like I'm getting more help here than from Google Support.  I used the webhook because I didn't know how to pass the 3 parameters my app script is expecting in the 'doPost(e)' function.  Here is the code block from that function:

function doPost(e) {
  var data = JSON.parse(e.postData.contents);
  var email = data.email;
  var staffName = data.staffName;
  var poID = data.poID;

  createTask(email, staffName, poID);
}

What I have to pass in is:

[PO]
[Staff]
[Staff].[Staff email]

How do I pass that in to the Function parameters?  As a select?  As a list? Or just layout?

Appsheet interface is waiting for an expression to pass it in:

[scott192]

So, my advice is to create a new Appscript project.  In that project copy and paste your createTask(email,staffname,poid) function.

You can also use this project to add many other functions that you want to pass data to and from in Appsheet.

Your functions can return data back to Appsheet as well.  I use it all the time to create folders in drive and pass the folder id back to Appsheet for storing.

To use the project in Appsheet, add it in a task the way you have discovered with your doPost(e) project function.  It will all become clear after you have done that 😀

Appscript Tasks are AMAZING!

[arimakidd]

I tried to pass it in as a JSON and used the following in the Function parameter e:

{
  "email": [Staff].[Staff email],
  "staffName": [Staff],
  "poID": [PO]
}

And the app script exection log gave me this error

TypeError: Cannot read properties of undefined (reading 'contents') at doPost(Code:3:36)

what should I do? 

[scott192]

Did you see my reply above?

[arimakidd]

@scott192 Okay.  I'm creating that new project and I want no slip ups.  What should I deploy it as?  

[scott192]

You don't deploy it!

Just save your script and add it in your bot  as an Appscript Task

[arimakidd]

@scott192 you were definitely right about what I would see in appsheet😄

And I filled it out accordingly

But I get the error:

Which I don't understand cause my function only calls for 3 arguments.  Why is it expecting 1 to 2?

// This function creates a task in Google Tasks
function createTask(email, staffName, poID) {
  // Load the Google Tasks service
  var taskService = Tasks.Tasks;
  var tasklist = taskService.list().items[0].id; // Assumes the first task list

  // Define the new task
  var task = {
    title: `PO Closed for ${staffName}`,
    notes: `The PO with ID ${poID} has been closed.`
  };

  // Create the task
  taskService.insert(task, tasklist);

  Logger.log(`Task created for ${email}`);
}

[arimakidd]

I think something is wrong with my app script.  I'll go back to it

[scott192]

If it was working and inserted tasks correctly with your doPost() function, then it should work ok now.  I am just looking up the Tasks API as I have not used that before.  I assume you are looking at these develope notes? https://developers.google.com/apps-script/advanced/tasks

[scott192]

By the way, you realise that you only have permissions to get your own tasks and set your own tasks using this script?  You cannot pass somebody else's useremail and manipulate their tasks.

[arimakidd]

@scott192 .  Didn't see the 'No Entry' sign there.  I'll have to use email in the Bot automation then or SMS.  Okay.  Thanks.  

[scott192 · Accepted solution]

I think the general workflow would be to create the task in your account and then use the Tasks service to Share that task with the supplied useremail.  I do this all the time when generating Google documents via script.  The final stage of the generation shares the document, gives the user ownership and then removes myself as an editor of the document.  It's convoluted, but is really the only way (currently) to use all Workspace functionality with Appsheet as it doesn't support service accounts.