Form To Api

[clintinthecode]

Hi,

I have a script to pull data from my project management software into a google sheet, where it is then displayed along with a heap of other stuff in App Sheet.

I want to be able to add a new task to the project management software from app sheet.

Ideally I would like a form in App Sheet, which then does an api call to my project management software to submit the new task, call a function in google app script to sync and pull the new task into the sheet, then force AppSheet to do a reload.

the second part is possible, and from what ive read the last part is possible too, however im unsure about the first. From what I can see a form can only save to a table?

My best guess is to have a seperate table eg temp tasks, have the form enter the data into that table, on save do the api call, trigger the google app script function, do the reload. seems a bit inefficient so was just wondering if there was a way to skip the save to a temp table step and go straight from a form to an api call.

Any help or suggestions would be great! thank you!

[clintinthecode]

Anyone have any suggestions for this one?

[dbaum]

AppSheet only saves to a connected data source, like a spreadsheet. If you don't want to use a temp tasks table for some reason, consider saving to to the main tasks table and having that row subsequently updated with info generated in the project management software. Whichever table connected to AppSheet that you use to initially save a new task, you can create an automation for that table that is triggered by adding a new row and the automation can include a Webhook task to invoke the API directly and/or a Call a script task to have an Apps Script script invoke the API.

[clintinthecode]

At the moment I am saving to the main tasks table, its just pretty slow, It takes 5 or so seconds for Appsheet to save to the google sheet, then in google sheets I have a on change app script function that grabs the change, uploads it to my project management software, then does a sync from the PM Software and updated the spreadsheet, I then need to manually refresh Appsheet after the change. its pretty slow. I was thinking if Appsheet could call an api without saving to the table, in the time it takes for Appsheet to save to google sheets it could have performed the api call to the PM software.

[dbaum]

Maybe you want to consider using an intake form outside of AppSheet for new tasks--e.g., Google Forms. From AppSheet, you could create an action that launches the form for data entry (including with pre-filled data if that's helpful).

[scott_robinson]

Do what @dbaum suggests.

Create a temp table.

Make a button that adds a new row to that table.

Create a bot that runs on adds to that table.

The bot uses Call Appscript Task 

Forget about using onChange() events in your sheet and rewrite your code as a standalone task to call your project API.

You can pass a value back to Appsheet from this task which will effectively make the bot update once your task finishes interacting with your project API.

[clintinthecode]

Does the bot run before the data is saved to google sheets? If so then there could be some performance increase, but if not and it needs to wait for the save to google sheets to happen before the bot is run, then I am at no performance improvement.

[dbaum]

The performance lag you’re referencing is between entering a new row’s data in the local app and when it syncs and writes to the spreadsheet data source. If you try the following idea, the user will be entering a new row’s data directly to the cloud data source—so, no lag related to sync from app to spreadsheet. 

@dbaum wrote:

create an action that launches the form for data entry (including with pre-filled data if that's helpful).

---

in this scenario, if you also applied @scott_robinson’s idea to use an AppSheet automation rather than onChange, I think you’d need to configure something called AppSheet external eventing, which I’m not familiar with.