Dynamically connect Appsheet with Google Sheet

[arib]

Hi,

Good day!

I want to develop one AppSheet that users in different organizations will use. Each organization has its own Google Sheets spreadsheet installed on its own Google Workspace.

On my side, I have a database connecting all spreadsheet IDs, organizations, and users.

When a user opens the AppSheet app, it should be able to dynamically connect to the appropriate spreadsheet on their organization's workspace.

Is this possible? Please point me to documentation, video, or previous thread that could let me know how to do this.

Thank you. Happy Thanksgiving.

Alex.

[1minManager]

"When a user opens the AppSheet app, it should be able to dynamically connect to the appropriate spreadsheet on their organization's workspace."   Sort of yes.  But the formulas would be hellishly complicated since the table name cannot be calculated.  So you'd end up with a formula like

IFS(
UserEmail()="abc@abc.com",
Select(ABC[KeyColumn],...),
UserEmail()="xyz@xyz.com",
Select(XYZ[KeyColumn],...)
)

Just put them all in the same table and use either slices, or security filters, to control what the users can see. 

https://support.google.com/appsheet/answer/10106592?hl=en&sjid=16669118366997752651-EU

https://support.google.com/appsheet/answer/10104488?hl=en&sjid=16669118366997752651-EU

Simon, 1minManager.com

[CaioVSMoreno]

It is not possible to connect dynamically, the tables can have different data structures, I have a solution for you but it will be quite difficult to implement.

1 - Create an Application with all spreadsheets linked
2 - Create View and Forms for each of them in ref mode
3 - Use the show of these views and forms for each different company or user, so everything will be in one application but you will only view the data through the 'Show' or 'security filters'

[arib]

Hi, 1minManager and Caio, thank you for your answers.

Sorry, but I don't quite understand your suggestion, 1minManager.

Adding to my question a little bit: I cannot get all the data in just one table - there will be hundreds of source tables, with lots of data in each - and my Terms of Services explicitly says that I don't store the user's data anywhere (which is essential because of Privacy). So, I can only access the data on the user's spreadsheet on the user's Google Workspace. No copy, no transfer. In the end, I only have a list of users and their respective spreadsheet IDs. Once the user is identified, the App should be able to dynamically connect with their spreadsheet ID and then sync the data.

Moreover, the spreadsheet list is not fixed. It will grow as more users start to use the service. If I understood you well, Caio, I would need to add a new spreadsheet and create a new app version every time a new user starts using the service, but the service is a SaaS, self-service.

Thank you.

Best, Alex.

[1minManager]

So to explain my formula above.  Select is a function where you can specify which table to pull data.  In my formula the user login in with abc@abc.com would access the ABC table.

The only other way of doing the same thing would be to use spreadsheet formulas to put in data form multiple sheets and then compine them into one sheet.  Obviously you've have to make sure the columns wheren't changed and various other stuff.  But I've done similar to this once before.