Transcription
This is setting up two-way sync without a row-aligned ID, or in other words, with an ID that is not row aligned.
Previously we had this sheet set up, and on the Mary tab, I could change, "Oh, let's drop this science down to, uh, six," and it'll override that. That worked really well because the IDs had a mathematical one-to-one relationship with the row, so it made it really easy to find.
This time, these are the IDs I'm working with: H0005 and S0009 have nothing to do with the rows that they're on, and this tends to match real-world data more directly. So, I still have the same script setup; this is a copy of the previous one we did, so it's still the exact same script.
I just want to show what happens if I try to run this. Currently, there should be no executions. Let's come back to Mary and do the same thing: drop this down to a 6. Totally failed—didn't do anything. Let's go check this out.
So, "Cannot convert J0008 to int." Makes sense. Where is it finding that? Sync with row 15, 6. Yeah, so it's saying right here it just can't do this, right? It can't say, "This is the ID," it can find the ID, but then it can't do anything about the row.
Okay, so it actually did everything else right; it deleted what was there or what I put in. So, let's do that one more time. It deleted it, but it didn't overwrite it. We obviously wanted to override, so I'm going to make a new function here: sync non-row.
A lot of this is actually going to be the same; all of this is going to be the same. We still want the source, the range—we're still looking at the same type of edit that's going to be valid—and the ID is still column A of that row. That's all the same.
Now, though, I need to go get the database. So, let's call my constant database: `SpreadsheetApp.getActive().getSheetByName("database")`. Then, `const ids = database.getRange("A:A").getValues()`. For the time being, let's just show what that gives out, so let's log the IDs here.
Let's change from sync with row to sync non-row, still passing `e`, so now when I edit something it's going to run sync non-row, and I'm going to be looking in the execution log for the list of IDs.
Once again, let's go back to Mary and drop this down to a 6. Okay, it cleared; it ran the script right, because here it's still clearing out what's there—we're still good there. All right, and now it's giving me all the IDs in the two-dimensional array that I would expect; that's just fine.
Now we want to loop through. Let me pull those back up. I want to loop through all of these IDs until I find the ID that we're at. So now I need to loop through those IDs. That's going to be simply:
```javascript
for (let row = 0; row < ids.length; row++) {
if (ids[row][0] === id) {
break;
}
}
```
We don't even need a second parenthesis there because we're just going to break out. To show that, let's log `row`.
Changing this to an 8 failed: "i is not defined," because I wrote it with “i” and then changed it to "row." So edit that again to force the script to run. That one completed; row three and we edited right here.
So let's do that one more time. Actually, what's really important, what we're looking for is this, "Jasania." So let's drop this to a 7 and wait for that run.
So Jasania is returning row three back on the database. Jasania is row 4; arrays are 0 indexed, so it's 0, 1, 2, 3. It found the correct location—that's awesome.
So row is correct. Now let's copy this and bring it back up. So let's get the db row, and here I actually want to do plus one or alternatively we could do row plus plus right here. Let's do that because again it's zero indexed.
So Jasania is in the sheet row four in the array row three, so I need to pop my row one higher. If I found row three, make that into four.
Then down here on the database, get the range of that row, the column of the edit, and set the value to whatever our value was. Let's go try it: Yesenia, let's make that a seven. Deletes and there it is, overridden with the seven.
Okay, let's cheat it better this time; bring that up to an eight. Perfect, and let's make sure that's working on John as well. Sebastian did a lot better this time—got a seven. Perfect, brought that up to a seven.
So the difference that we're doing here: I can get rid of this log. Instead of simply saying that the row equals the ID plus one, now we're making the row zero to use as our incrementation variable through the IDs.
We're getting all of the IDs, looping through those IDs, and when the ID we're looking at matches the ID we're looking for, we stop the loop. Now row is already declared, so we can continue to use that, increment it one more time since we need to have that the array is zero indexed and the sheet is one indexed.
Then just like before, get that same range and set the value to the value of the edit we made. Pretty simple—not that different.
The final video I'm going to have on this is how to do a two-way sync when there isn't any ID at all, so that's coming next. But for now, here's how to do sync when the row and the ID have some relationship. Here's how to do sync when the row and the IDs do not have any relationship.