I need to update a Google sheet/tab regularly as new accounts are added to the system. These rows (Accounts) have row IDs. The TAB headers are: Name - Partner - Rep - Account Name - Status - NOTE
I have a separate Google sheet for each Partner, their reps and all their accounts. I use importrange() to create a master account list of all Partner Accounts in a separate ‘Partner Sheet’. Finally, I import this master account list (using importrange() ) into the Google Sheet referenced by Glide.
I import Cols 1-6 (A-F) and the Row ID is column 7 (G). I never touch Col G ( importrange(“sheet”,masterlist!A2:F)) and Glide just works properly. The Google sheet shows the Row ID info whenever it is updated.
I started with 1800 partner accounts and added another 2000 accounts (everything rolled-up properly), then another 300 accounts (still worked) and so far so good.
Any gotchas with this method? The net result is I have to import and keep up-to-date a lot of customer-created spreadsheets which drive the App capability. I am using importrange() to create ‘Partner Master lists’ and then another importrange() to create a ‘Master list’ of ALL Partner accounts finally again using importrange() to bring them into the Google sheet driving my app.
I guess I would first ask if the sheet you use in glide is ever updated by glide? I’m curious, because mixing data from an import with data that come from glide seems like a disaster waiting to happen, unless your sheet data is ONLY sourced from the import. I think it’s important to clarify this bit first.
Back to you question…
Due to the disconnected nature of your RowID column as compared to your Imported columns, I would be very very leery about data becoming disassociated with the correct RowID. If rows that are added or removed from each partner sheet cause data to shift around in the sheet used by glide, you will most definitely mess up your relations.
Imagine a road with 10 cars in a left lane (column A) and 10 cars in the right lane (column G). Initially they are all lined up and each car in the left lane has a car lined up in the right lane. Now imagine if they start driving and the cars in the left lane drive 10 MPH faster. All of a sudden the cars on the left and right side are not matched up with the same car they started with.
I assume you created the RowID column within glide, which I think is good. I think you should be able to assign your own RowID in the partner sheet. Maybe just concat a couple key values together, then import and actually fill in the rowid column as well with that concatenated value. But this will be a problem if the sheet used by glide also receives data directly from the app. If so, then how do you handle conflicts between the imported rows and the non imported rows?
Just to prove a point, you can put anything you want into a RowID column, and glide will still assign row id’s if the cell is empty (eg. new rows).
The data set is imported only into Glide. It has 6 columns and the Row ID is created by Glide and I don’t touch it. It is the ‘source’; currently Glide does NOT create data of this type - Glide adds value to this data set. If an account is added, it will be added to the SS and ‘imported’ into Glide.
Row ID is the only data that is written back to the Google Sheet in fact. All other columns are compute columns such as relationships, ITE, lookups, rollups, templates, etc and are stored in Glide. There are a lot of compute columns in the table but ONLY the six ‘sheet’ data items (plus Row ID).
I onboard Partners and bring in their reps and their accounts. Currently I have used copy/paste to enter the into the Glide sheet but found an article on using importrange() to automate the process and gave it a try and it seemed to work. But that also led to the ‘Row ID’ overwrite problem (since an importrange() wants to overwrite everything).
Now I am not sure importrange() automation is worth the challenges when dealing with relatively large and dynamic data sets. Plus if I ever wanted to update from Glide - I think I am screwed.
Yeah, mixing Import with data coming from Glide is a no-go. You’re are just looking for trouble there. Google sheet formulas will freak out if they are going to import overtop existing data. Likewise, writing data into rows that could be populated by the import formula will also cause it to freak out.
Also, like I mentioned, if you have 3 partner sheets (1,2,3) which you merge into one master sheet, and then add rowid’s to that master sheet, then what do you think will happen is partner 1 adds a new row to their sheet? It pushes the partner data for 2 and 3 down, and now you broke all of the rowID relations because RowID did not move with the rest of the data.
The data set is ‘mostly’ stable but not 100% so it should be curated/cured for input rather than a blind ‘reload’. I can load fresh/clean data (automated to a point) easily enough by hand for now. I would rather create a MUCH tighter process than simple/dumb import rules.
I would probably explore google scripts which can run periodically, check for new data, and add it to your master table if it’s not there already. That would be the safer option.
