Using Google Sheet with Imported Data as an Appsheet Source

[gellison]

My application uses a Google sheet containing a table created with the Import function. The imported table contains a user ID column. In Appsheet I tried to use the user ID column as the unique ID for the application, but the system would not allow it. Appsheet said that the ID column contained a formula (the Import function???). So I am stuck with the row number as my unique ID. Is there a way around. this?

Garrett

[SkrOYC]

You need it to be the key?

[gellison]

Yes, I need the user ID in my source to be the key used in Appsheet. I don't want to use the _Row Number as the key default key.

[Koichi_Tsuji]

Not exactly sure what "imported table" in your context, but I assume you are using importrange() kind of spreadsheet expression to pull the data from other sheet?  In that case, this table is just view-only, you will not able to change data from AppSheet app.

[gellison]

You are correct. I am using the importrange() feature in Google Sheets to populate the source data. Apologies for omitting this point. Importrange allows me to seamlessly update the source data from another workbook. From the users' point of view the table the table is read only. 

[Kirk_Masden]

Actually, I think I might be doing what your are attempting to do.  I have a table that looks like this:

I just checked and I found that I had set the key in the table to the _RowNumber, not the "Key," as follows:

In my case, this table is read-only data that is waiting to be imported (copied) into the app.  THEN my "Key" column actually becomes a key in the table to which to data is copied.

Hope this is relevant to your situation.

[gellison]

Your description sounds similar to what i am doing. But it's not clear to me what steps you took to make the "Key" column the actual key instead of _RowNumber. Each time I attempt to change the key column, Appsheet reverts to _RowNumber. What do you think I am missing?

[Kirk_Masden]

Hi @gellison! Sorry for not being clear.  In the table you see in the image above, the column with the name "Key" never actually becomes to key column.  However, when users (language students) copy a row from this table into a different table, then the "Key" column becomes a "real" key -- kind of like Pinocchio becoming a "real" boy.  😉

My app is pretty big and complicated now but you can look at it as a sample / template app, if you like:

https://www.appsheet.com/templates?appGuidString=33231d72-dcf8-4dfd-92b8-d59aa04fb0ba

The table in which I'm using the the IMPORTRANGE() function is "Seminar."  In the app, if you go to "Add cards" and then to "Copy from public list," you can import a key and make it a real one. 🙂

[Koichi_Tsuji]

Most likely you have spreadsheet formula in key column. Just remove it from column setting and set to key . And save. 

[Kirk_Masden]

Right.  In my app, I do have the formula in the key column.  In my case, that's OK. But, perhaps @gellison should take your advice. It all depends on your particular situation and what you're trying to achieve. 🙂

[Marc_Dillon]

Load the table in to the app with just the headers first, without any formulas. Then go back and delete the headers and add the formula. You'd need to repeat that process in the future if you ever need to add more columns and regenerate.

[gellison]

I'll give this a try. Thanks!

[Kirk_Masden]

Footnote to my comment above: I'm using IMPORTRANGE() in a Google sheet.  That was an important point that I neglected to mentioned.  IMPORTRANGE works nicely but only as read-only data that can be copied by the user in to other tables.  Again, apologies if my method isn't relative to what you want to do.

P.S. I use this in an app for students.  All student copy a template I have put on the web.  The IMPORTRANGE() function allows me to continually provide new data to them after they have already begun using their apps.

[Steve]

Put the IMPORTRANGE() function in cell A1. Import the header row, too. AppSheet won't care if the header row has formulas.

[Kirk_Masden]

Thanks Steve!  @gellison , I think this may be your solution!  In my app, I'm going to stick with my current system because there's a possibility that the sheet from which the importrange() formula reads data can be wrong; it's user designated so the URL could be wrong, etc. In such a case, I think bad headings would "break" the app.  But, with my usage the app keeps running even with such errors -- and I have an internal error message that is displayed to the user.  Again, case-by-case I think, but I had forgotten about using the importrange() function in cell A1.  

One more thought: Sometimes IMPORTRANGE() fails on the Google side.  See the following:

https://blog.coupler.io/importrange-not-working/

I'm pretty sure that such a failure would break your app. (The user would get an error message that says the app cannot be used.). So, come to think of it, perhaps Steve's approach is not the best after all.

[DPM_reports · Accepted solution]

if this table with the import function is for view only.....you wont get this error

[Kirk_Masden]

Hi @DPM_reports ! Are you replying to me?  if so, my point was that IMPORTRANGE() sometimes fails.  This is quite common.  If it does fail, and IMPORTRANGE() is what you rely on for your header row, your app will be broken until IMPORTRANGE() comes back to life.

[gellison]

That was the answer to the problem. Thank you!

[Kirk_Masden]

I'm glad you found something that works for you.  Just be aware that, as I wrote above, using IMPORTRANGE() to fill your column labels can lead to problems if there's an unexpected glitch on the Google sheets side (IMPORTRAGE() formulas that are working perfectly can sometimes fail unexpectedly).

[Kirk_Masden]

I know this has been solved but I recently experienced a temporary IMPORTRANGE() fail that only lasted a short while but would have caused the kind of problem I described above: