Data Management - Preserving Overwritten Data

Hey Y’all, I’m working on a new project and the data management for this one is causing me a headache.

Due to the back-end requirement of my job, I need to be able to sync the data in my sheet to our existing database via .csv downloads. Then I want to be able to use that data in Glide.

The problem, obviously, is when I download a new .csv I’ll be overwriting the data that I had accumulated in Glide. To be clear, I want the .csv to be the only real way to ADD rows, but then use Glide to populate those rows, then keep that data and remember what row it belongs to if the source data changes.

My system does have a unique ID, and I feel like I can match it to the Unique ID somehow, then I should be good to go, right? Anyone done that and have experience with this? It’s breaking my brain… :slight_smile:

What is your “existing database”? Is there a reason you don’t connect your sheet directly to Glide?

Oh, sorry for the confusion. Glide is connected directly to sheets. But I’m a franchisee (so don’t have access to the corporate CRM directly) so I have to rely on exports of the data, which I get as a .csv file that then I upload to sheets once a month.

I’m thinking maybe there’s a way to create a reference tab where I match the export IDs to Glide unique IDs? Then maybe create a VLookup and reference my Glide data to that table, instead of the main one…

Thank that would work?

I’m struggling to understand. Need to ask a few questions…

I guess firstly, when you say “sheets” are you talking Google Sheets or Excel?

Is your CSV a full export, or some sort of differential?

Assuming a full export, does that mean that essentially what you want to do is only add new rows?

Or is there new data in the export that needs to be updated in existing rows?

1 Like

Hola!

I have more questions :rofl:

  • Your APP’s data source is Google Sheet right?
  • Your .csv is created using MS Windows and can be used in MS Excel right?
  • Would you mind using an Excel macro in order to send the Excel data to GS whenever you want? It’s what I do sometimes to connect Windows world to GS.

Saludos

Thanks for the help! Google Sheets. Usually, I do a full Export.

Since it exports with a “Client ID” I’m thinking I can do an index/match or something to align the Glide Unique ID to the Client ID then populate the data in the right row, regardless of where it comes in from the .csv file.

You bring up an interesting point though, I guess that wouldn’t help me with “new” rows since they may be overridden… I guess I’ll need to have the user add the Client ID manually when adding a client in Glide so that it matches the next time the data is synced.

Ideally, I’d like to update existing rows, keeping the Client ID as the constant.

Hola!

Yes. Yes to Excel, but I would prefer to use sheets since that’s what all my users are already on. Can I run it via AppScript? How would that work?

That’s probably what I would do.
I do a lot of this sort of stuff - syncing data between different data sources using Apps Script. I find the best approach is to first define the logic as thoroughly as possible in pseudo-code, trying to capture all possible edge cases, and then translate that into code.

1 Like