0

I'm working on a sheet that cross references between two different Sheets. It works fine, but each sheet is around 10,000 entries, so it's checking...a lot. It takes about a half hour to check the whole thing. Which is fine, for the initial check.

That being said, I'm only adding new entries to the sheets every week, so having it check the entire thing is unnecessary. I think I know how to do this, I'm just not exactly sure how to write it.

My loop currently looks like this:

for (var i = 1; i < data1.length; i++) { 
    for (var j = 1; j < data2.length; j++) {
       ....

I'm guessing the way to do this is to create a variable, replace 1 with that variable, and have that variable be entered by the user. I can do that two different ways, I can call it from a cell in the sheet (which isn't ideal) or I can have a pop up show up before the script executes that allows a user to input the number.

I'm not very familiar with JavaScript, I took a class or two but I don't really know how to do this. Could someone help me out? I'm sure I need to truncate and sanitize my input too just in case and I have no idea how to do that at all.

Edit: After messing with it a bit more I added 

var j = Browser.inputBox('Enter the number of the row to start on:')

Which should work (in theory) but for some reason if I do that I always end up with 0 results and I'm not sure why. I'll mess with it more, I suppose.

tehhowch
  • 9,645
  • 4
  • 24
  • 42
Matt
  • 5
  • 2
  • You should be able to refactor your project to eliminate a nested for loop, instead using a lookup object / index. See my answers [here](https://stackoverflow.com/a/50286994/9337071), [here](https://stackoverflow.com/a/48935477/9337071), and [here](https://stackoverflow.com/a/49519839/9337071) for similar optimizations. 10k * 10k comparisons - even if done with native JS array elements, is going to take quite a while (at 1 µs per comparison, 10k * 10k * 1e-6 is 100 seconds). You should be able to do it with max 2 reads of each (1 per index, 1 each to act on it given the indexed information). – tehhowch Aug 14 '18 at 00:10
  • That's fantastic, that should work. Probably. I don't know a lot about lookup objects but I'm trying to figure out exactly how it works. So is the basic gist that (based on [this](https://stackoverflow.com/questions/49518896/index-match-large-array-google-script-taking-very-long/49519839#49519839) example) that it turns spreadsheet A Column X into an array key and then compares spreadsheet B Column Y to it? Does spreadsheet B also need to be turned into an array? God this is going to save so much time. – Matt Aug 14 '18 at 17:24
  • That's the basic idea, yes. Create some computation that converts an identifier for a record into that record's location within your large collection. If you then perform the computation to get the location, and the location doesn't exist or doesn't have a record, then you know that there is no associated record for the key you gave, without needing to re-check every record in the collection. You can make your computation handle non-unique keys e.g. by returning an array of locations. [Use `.getValues()` rather than repeatedly calling `.getValue()`](https://stackoverflow.com/a/49020786) – tehhowch Aug 14 '18 at 18:13
  • Okay I think I understand how it works. I appreciate you explaining it simply, I took like one class on this 10 years ago so the only things I remember are syntax and basic commands lol Say there's a match. If I want to copy the contents of the matching cells to a specific cell, what's the best way to do that? Keep it as the array and then do like [r][(x) + 4] to add it to the cell four columns to the right? I'm assuming it would be entered under "var oldDataRow = database[existingIndex];" but I'm not sure exactly how to phrase it. Thank you so much for your help, you're a life saver. – Matt Aug 14 '18 at 18:46

1 Answers1

0

I think the issue is that you are re-declaring j in your inner For loop, which essentially negates the value you get from user input. The below code seems to do what you are looking to achieve. Although it's very basic and will accept input larger than 20, which won't result in any inner loop iterations. Hope this helps.

Cheers!

var input = Browser.inputBox("Enter row number:");

for (var i = 1; i<5; i++) {
 Logger.log("i = "+i);

 for (var j = input; j<20; j++) {
   Logger.log("j = "+j);
 }
}
RussR
  • 303
  • 2
  • 7