Google App Script: Speed up / alternative to loop

Question

I am a newbie on Google App Scripts. So far so good. Trying to do some of the stuff I used to do in an MsAccess DB. I think I have hit a bit of a problem, which could be related to the sheer amount of data I have. To illustrate my problem:

I have two arrays:

**Array1**

ID || Name <br>
0001 || Peter<br>
0005 || Arthur<br>
0008 || Gloria<br>
0010 || Martin<br>
0014 || Jess <br>


**Array2**

ID || Age <br>
0001 || 23<br>
0002 || 18<br>
0005 || 24<br>
0010 || 7<br>

As you can imagine, I want to produce something like:

**Array3**

ID || Name || Age <br>
0001 || Peter || 23<br>
0005 || Arthur|| 24 <br>
0010 || Martin || 7<br>

I have used the most simple code I can think of to the 'join':

for (var a=0; a<array1.length; a++) {
    for (var b=0; b<array2.length; b++) {
        if (array1[a][0] == array2[b][0]) {
            array3.push([array1[a][0],array1[a][1],array2[b][1]); break;
        }
    }
}

Problem is: array1 is 70,000 entries long, and array2 is about 10,000 entries long, meaning the code runs out of time.

What would you guys do?

Also, have you thought about using a free database, like mysql or postgre? :) — Cody Geisler
– Cody Geisler, Commented Nov 1, 2017 at 22:38
If your data is sorted numerically by "id" you should "find" the id by looking at the halfway-point, and then comparing it with the first point... if the id requested is greater than the half point, look at the next half point... ect. — Cody Geisler
– Cody Geisler, Commented Nov 1, 2017 at 22:40
@CodyG. Hi Cody. I am getting my arrays from google sheets. The getValues only takes a few seconds, so I don't think I have a problem there. Is the loop that is killing me! — Ozz
– Ozz, Commented Nov 2, 2017 at 1:52
@CodyG. Hi Cody. That is the other option I have been considering, but I have a lot of services integrated in Google (email, drive, forms), so it seems like giving google a shot will be a good option. If everything fails I will just go to an online DB... — Ozz
– Ozz, Commented Nov 2, 2017 at 1:53

Pointy · Accepted Answer · 2017-11-01 22:45:23Z

1

Create an auxiliary object whose property names are the id values from array2 (or array1; you could try both and see which is faster):

var a2map = {};
for (var a = 0; a < array2.length; a++)
  a2map[array2[a][0]] = array2[a];

Now you can iterate through array1 looking for matches:

for (var a = 0; a < array1.length; a++)
  if (array1[a][0] in a2map)
    array3.push([ array1[a][0], array1[a][1], a2map[array1[a][0]][1] ]);

Your original algorithm will have a runtime that's roughly proportional to 70000 * 20000, which is a big number. This one will be closer to just 90000 or so, making it many thousands of times faster.

edited Nov 1, 2017 at 22:45

answered Nov 1, 2017 at 22:40

Pointy

415k62 gold badges600 silver badges631 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Google App Script: Speed up / alternative to loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related