2-Way Sync: Row-aligned ID

This is setting up two-way sync within the same spreadsheet using a row aligned ID.

So the problem I'm trying to solve that this is going to solve for us: I have this database here with just a bunch of students and two teachers. On each teacher sheet, I have a filter formula getting all of their students. That's it. The data for their students is as follows: Sean, Mary's, and John's are identical. The only difference is if it's pulling Mary or if it's pulling John. That's it.

Now, presumably I'm going to give this sheet to Mary and John so that they can see their students, see how they're doing, etc. But it's quite likely that Mary will want to change stuff on this tab, right? She may not, depending on how we have set up, even really have access to the database. Even if she does, it's not that convenient to go and find a particular student on the database when she's looking at them right here.

So she's here and she wants to say, "Elsa did really great on her last science. Her science is now an eight." But the whole thing crashes, and the error it gives is "Array result was not expanded because it would overwrite data in E5."

So let's get rid of the eight. All of this data is being output by this formula. In other words, the output of this formula pushes to this entire range, and it must. It doesn't have any way to not overwrite it; that's what it does. So if I just try deleting something, it comes back always because it's pulling the data from the database constantly.

But I want to be able to set it up so that you can edit something here, have it show up on the database, and have it continue to appropriately pull to Mary’s sheet. The logic we're looking at is, if anything is edited here, get the ID, find that ID back on the database sheet, make the edit on the database sheet, and clear the value—yeah, clear the value from this subsheet.

This is the first of three videos about setting up two-way sync in a really nice manner. In this one, we're using a row aligned ID, which simply means that on the database, the ID I've set up here has a mathematical relation to the row. So ID 1 is row 2, ID 2 is row 3, ID 6 is row 7, etc. Any way that the ID and the rows are aligned, we're going to use this method.

Open up our script. Google did recently change what the menus are. Apps Script used to be under Tools; Apps Script is now under Extensions.

All right, because we want to tap in when we change something, we're going to use onEdit. And of course, if the E parameter was not passed (which means that the script was run manually), throw an error: "Do not run manually; only run this script." Only allow the script to run automatically when you make the appropriate edit.

Then we need to make another script, another function. Let's call this syncWithRow, and we're going to pass the entire E parameter to it.

Now we're going to call syncWithRow and pass the entire E to it. Now here we can do:

```javascript

const source = e.source.getActiveSheet();

const r = e.range;

```

Now I want to be careful of where the proper edit should be, what type of edit that should be. So if `source.getName()` equals "database," we don't want to run on the database. We also don't want to run it if it's the first row or if it's the first column. So in any of those cases, quit.

Now, if it passes the first check, I want it to do is actually delete the value that I put here. So let's just do `r.clear()`.

Let's try that. Excellent! So the whole thing is working right now. It's coming into the onEdit, it's passing E to syncWithRow. SyncWithRow is saying this is not on the database, it is not row 1, it is not column 1, we're good to carry on. The first thing it does is delete whatever I put in there.

Right, I put an eight, it gets rid of it. Nothing's coming over to this database sheet yet; we're just making sure it is running. We will need this as well. We do need to clear that out; otherwise, it will continue to have the override challenge and the REF error.

Now I want to go get the ID. Let `id = source.getRange(...)`. And the range I want—so we need the row, then the column. The row is going to be the same row we're on, so `r.getRowStart()` and `column 1`.

Let's just get the value, and for testing, let's go ahead and log that value. So if I make an edit here, it should clear whatever I put, and it should log the ID on A5.

So column A on whatever row I made the edit, and obviously, it's still clearing what I put there. Here's my latest execution: "6," is that correct? That is excellent! So we're getting the correct one. Now what needs to be different is I need to go get the correct row on the database. ID 6—the ID we edited is row 7 on the database.

And because there's that nice relationship between the ID and the row, to get the row let `row = id + 1`. Now let's also log the row once it saves.

So we're going to log the ID and the row. Now let's go back to Mary. "Elsa did great."

Anyway, it's not doing anything other than deleting that, so that's fine. Now we're not seeing anything happen there yet. ID 6, row 7? Perfect!

So now we can do:

```javascript

let db = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("database");

db.getRange(row, r.getColumnStart()).setValue(e.value);

```

So here's the logic we're running through: If it's the correct type of edit, then delete whatever we put there, get the ID (which is here in column one), and the row is the ID plus one. Go get the database, and in the database find the correct row at the column that we edited and set the value to the value that we put in. Because we cleared the actual entry and we're setting the value in the same location on the database to the value we wanted, the filter formula or the query formula or the import range or however we're setting that up is going to continue to pull that data properly.

So Elsa did great on her last science; she got an eight. It clears it and puts an eight, which we can see here as well.

Okay, and we can do that for anything. Let's do this on John. On John, "Carlos' writing came up to a seven."

It's going to delete it; it's going to put the 7 on the database and now we can see the 7 there.

Okay, so this is just for using when the ID and the row numbers are aligned or mathematically related. There will be other sets of videos and tutorials about how to do an ID that has nothing to do with the row or a randomized ID or one that’s based on a log number or a shipping number or something like that.

Then also another one on how to set this up with no key whatsoever. But the basic logic that we're following here—and it's going to be true for the other two-way sync setups—is clear whatever you put there, find the ID, and find the row where you want the edit to be, then put that value on the database in the same location.