Update a column value in a task using a value from Userssettings

[conscientia]

I've an automated task that at the end of the task I'm trying to set a column value on the updated row with a USERSETTINGS("Value").  

I get the following error: Unable to find column '_THISUSER', did you mean '_RowNumber'?

Tried to explicitly use: [_THISUSER].[Value] - still no luck.

Seems like this should work?

Thanks!

[Landan_QREW]

The USERSETTINGS table is a client-side component and will not be able to be utilized in a server-side bot.

I would recommend having a user or a user_variable table where you can add variables (just like you would in user settings) that are unique for each user in your app. This could be used in server-side bots.

[conscientia]

Ahh, ok, that makes sense.  Is there any way to track a unique client side variable without accessing USERSETTINGS or other user specific attributes? i.e. a unique session variable that can be used to map to a Server-side table (i.e. user_variable) row?  Trying to build an app for a non-prophet that can't afford a paid plan.  Also, do emails/sms message work on the free plan?

[Landan_QREW · Accepted solution]

Well, you really need the variable to be server-side if a bot is going to access it. That is not much trouble as any table in the app can be accessed server-side (with the exception of the USERSETTINGS table).

So in essence, you just need to create a table in Sheets or whatever your back-end is and have one row per user (as shown in the image below).

In the example, you will notice the app_role column is essentially a variable. This can be accessed by a bot in a number of ways.

The best way is to do a query based on the current user's email: ANY(SELECT(user_variables[app_role],[email]=USEREMAIL()))

Regarding the payment question, I don't think any deployed app can be free. Only undeployed apps can be free, but undeployed apps will not send emails to anyone but the app owner and I believe you can not have more than 10 monthly users on an undeployed app.

[conscientia]

Ok, understand.  Looks like no matter what, we'll have to pay for a plan as functions like USEREMAIL() require the paid subscription. Unfortunately, the cost would be an issue.  Thanks for the feedback/input though!

[Landan_QREW]

I don't think the USEREMAIL() function is a paid feature, but the email automations may be a deal breaker for you.

You can probably replicate the email functionality in Apps Script for free, but would cost quite a bit more of your time. Just a thought though.

[conscientia]

Thanks, I'll give that a try! Can you point me in the right direction for integrating an Apps Script with Appsheets?