'Not completed: Timeout' How can i speed up my script? ideas

[Michael King]

Good morning all,

My Scripts keep Timing out, the account in question has 3725 Ad Groups and Ive built 3 different scripts so far.

My scripts in my opinion are not that efficient i'm pretty new to JavaScript and Adwords Scripts so there are probably loads of ways to speed up the program. Any ideas? currently my scripts are just one long program, will it speed up if I break it down into functions?

Scripts in question;
- One turns the Ad Group off if the product is out of stock according to a data feed,
- One checks for new products in the data feed and emails the account manager the details of new products
- And finally one does a dump of All the paused ad Groups into an email to manually check the URL's.

If it would help to post the scripts up here I can do.

thanks

[Ernő Horváth]

Hi,

Have you tried to find out which part of the code is slow?

Eg. once I increased the wrong variable inside a 'for' and the program obviously stack there :) but after i've corrected it run quite fast.

What I can recommend without seeing the code is try to break down the code to parts by using Logger.log() and see what happening, maybe it couldn't get through a certain point. 

But yes I think posting the code here will help.

Regards,

Erno Horvath

[Michael King]

Hi Erno,

Here is the email script,

it gets the first SKU in the 'New Product CSV' and then checks that against every SKU in the 'Old Product Spreadsheet' if it finds one that doesnt match it emails the details to the account manager.

it looks like it slows right down when it starts iterating through the Google spreadsheet. In 30 mins it only seems to make it through the first thew rows of the CSV.

function main() {

    var EMAIL='Input User Email';

    /* CSV Parse */
    var csvurl = 'http://.csv';
    var csvString = UrlFetchApp.fetch(csvurl).getContentText();
    var csvdata = Utilities.parseCsv(csvString);

    /* Spreadsheet Parse */
  var url = 'https://docs.google.com/spreadsheet/';  
  var spreadsheet = getSpreadsheet(url);
  var sheet = spreadsheet.getSheetByName('Stock');

  /* connect to spreadsheet */
  function getSpreadsheet(spreadsheetUrl) {
    var matches = new RegExp('key=([^&#]*)').exec(spreadsheetUrl);
    if (!matches || !matches[1]) {
      throw 'Invalid spreadsheet URL: ' + spreadsheetUrl;
    }
    var spreadsheetId = matches[1];
    return SpreadsheetApp.openById(spreadsheetId);
  }
 
     /* get height of CSV */
    for (var rowIndex = 0; rowIndex < csvdata.length; rowIndex++) {
        var csvrows = rowIndex;
    }

     /* get hieght of spreadsheet */
    var range = sheet.getDataRange();
    var values = range.getValues();
    for (var spreadsheetrowIndex = 0; spreadsheetrowIndex < values.length; spreadsheetrowIndex++) {
        var rows = spreadsheetrowIndex;
    }
 
    /* iterate through each SKU in CSV*/
    for (var n=1; n<rowIndex; n++) {
     
      var fail = 0;
      var success = 0;
     
      var csvSKU = (csvdata[n][0]);
     
      /* iterate through all the spreadsheet SKUs*/
      for(var x=0; x<spreadsheetrowIndex; x++){
      var y=x+1;
     

       var spreadsheetSKU = sheet.getRange("A" + y + "").getValue();
       
        if(csvSKU == spreadsheetSKU){
         
          var success = success+1;
        }
       
        else{
          var fail = fail+1;
        }
        if(fail==spreadsheetrowIndex){
         
          var csvName = (csvdata[n][1]);
          var csvDescription = (csvdata[n][2]);
          var csvLink = (csvdata[n][10]);
         
          var details = "PRODUCT NAME - "+ csvName +" PRODUCT SKU - "+ csvSKU +" PRODUCT DESCRIPTION - "+ csvDescription +" PRODUCT LINK - "+csvLink+"";
          /* Email user with new stock item*/
          MailApp.sendEmail(EMAIL, 'New Products Added to Suttons Feed', details)
        }

      }
     
    }
}

 


On Friday, May 17, 2013 9:31:32 AM UTC+1, Michael King wrote:

[Ernő Horváth]

Hi,

Well it's not an Adwords Script problem I think, it's more like a JS problem

I dont really understand these lines:

for (var rowIndex = 0; rowIndex < csvdata.length; rowIndex++) {
        var csvrows = rowIndex;
    }

     /* get hieght of spreadsheet */
    var range = sheet.getDataRange();
    var values = range.getValues();
    for (var spreadsheetrowIndex = 0; spreadsheetrowIndex < values.length; spreadsheetrowIndex++) {
        var rows = spreadsheetrowIndex;
    }

You declare every time the rows variable inside the for loop. 

Declare before the for loop and just use in inside. But I still don't know why you like to count something which you alerady have... 

csvrows = csvdata.length ???

rows = values.length    ???

Or maybe I misunderstood something.

Regards,

Erno

[Michael King]

Hi Erno,

Thanks your a star, I removed those variables as you suggested and changed the code slightly so it didn't use a Google Spreadsheet but another CSV and now the scripts are fine.

It looks like Google Spreadsheets slow Adwords Scripts right down but CSV is pretty quick.

Thanks again for your help.

On Friday, May 17, 2013 9:31:32 AM UTC+1, Michael King wrote:

[Patrick]

Hi Michael,

Did you a test with the Google Spreadsheet and the CSV or only with the CSV??

I base my scripts almost everytime on Google Spreadsheets and I wanted to know how much they slow the scripts down...

[Michael King]

Hi Patrick,

Originally I stored an old copy of the stock list in a Google Spreadsheet and the program would go through the CSV file line by line checking the whole Google Spreadsheet line by line for the SKU ID.

The problem was there are 4500+ product lines so the program is in efficient but i'm not knowledgeable enough to find a more efficient  solution yet. to test the program I added markers through my Google spreadsheet to see how far it would get before the script timed out. My test revealed I wasn't event reaching line 10 of the CSV file before the program timed out. If my Google Spreadsheet was only 10 lines long it would complete no problem. In all fairnous when I tried to load it manually it took a couple of mins to load all 4500 lines.

I altered the code to use two CSV files, one saved on the client side with new products and one saved on my web server with the old list, now the program runs perfectly in about 4 mins every day.

I think the Google Drive is excellent and I do use it allot but the CSV functions are so much faster.

On Friday, May 17, 2013 9:31:32 AM UTC+1, Michael King wrote:

[Ernő Horváth]

Hi Michael,

It's me again.

This line is very slow:

var spreadsheetSKU = sheet.getRange("A" + y + "").getValue();

I recommend to store the values in a temporally variable instead of get the ONE value from a speadsheet.
You can store it in arrays and you can reach the value of an array very fast.

If you script can't get through the first ten lines well it's definitely a script problem not a Google Spreadsheet issue.

I haven't look your code so long as I see you comparing two array... so you have two lists and you try to compare them.

As you can see you do a lot of unneeded iteration, because if you need to compare two lists with X element the number of iterations would be

10 -> 100

100 -> 10,000

1000 -> 1,000,000

so if you compare two array in this way and each array has 1,000 rows it would be 1,000,000 compares! 

I strongly recommend to get two arrays sort the two arrays (  array_name.sort() ) and compare the elements from the first place.

Eg. than your lists would be:

lista = 1,5,8,11,46,55,99

listb = 5,8,99

stepping one-by-one on lista and listb and according two which number is bigger or matching.

Because the lists are sorted you need to go through both lists only ONCE! and you exactly now what numbers/strings are not in second list, but it's in the first list.

I hope it helped :)

Regards,

Erno

 
  

On Friday, May 17, 2013 9:31:32 AM UTC+1, Michael King wrote:

[Michael King]

Thanks Erno,

Ill see what I can do and Ill post the script back up here.

Mike

On Friday, May 17, 2013 9:31:32 AM UTC+1, Michael King wrote:

[michael king]

Hi Erno,

This is my updated script,

Its much faster now, I see what you mean about being able to search an Array a lot faster than parsing the spreadsheet every time.

Can you see anything else that's slowing it right down?

function main() {

    /* Add any email addresses you want reports to go to seperated by (,)*/
    var EMAIL = 'ADD YOUR EMAIL';

    /* CSV Parse */
    var csvurl = 'URL OF MOST UP TO DATE CSV';

    var csvString = UrlFetchApp.fetch(csvurl).getContentText();
    var csvdata = Utilities.parseCsv(csvString);

    var csvSKU = new Array();
    var csvName = new Array();
    var csvDescription = new Array();
    var csvLink = new Array();

    /* Spreadsheet Parse */
    var url = 'URL OF OLD CSV FILE WE ARE CHECKING AGAINST';
    var spreadsheet = UrlFetchApp.fetch(url).getContentText();
    var sheet = Utilities.parseCsv(spreadsheet);
    var spreadsheetSKU = new Array();

    /* get height of NewCSV */

    for (var rowIndex = 0; rowIndex < csvdata.length; rowIndex++) {

        csvSKU = (csvdata[rowIndex][0]);
        csvName = (csvdata[rowIndex][1]);
        csvDescription = (csvdata[rowIndex][2]);
        csvLink = (csvdata[rowIndex][14]);
    }

    /* get height of OldCSV */
    for (var spreadsheetrowIndex = 0; spreadsheetrowIndex < sheet.length; spreadsheetrowIndex++) {
        spreadsheetSKU = (sheet[spreadsheetrowIndex][0]);

    }

    /* iterate through each SKU in CSV*/
    for (var n = 1; n < rowIndex; n++) {

        /* fail and success are equal to 0*/

        var fail = 0;
        var success = 0;

        /* iterate through all the spreadsheet SKUs roughly 4300*/
        for (var x = 0; x < spreadsheetrowIndex; x++) {

            /* if csvSKU data matches spreadsheet SKU add 1 to success */
            if (csvSKU[n] == spreadsheetSKU[x]) {

                var success = success + 1;
            }
            /* if csvSKU data doesnt match spreadsheet SKU add 1 to fail */
            else {
                var fail = fail + 1;
            }
            /* if fail is equal to the number of rows in the New CSV file than this SKU is not recorded any where in the old file*/
            if (fail == spreadsheetrowIndex) {

                var details = "PRODUCT NAME - " + csvName[n] + " PRODUCT SKU - " + csvSKU[n] + " PRODUCT DESCRIPTION - " + csvDescription[n] + " PRODUCT LINK - " + csvLink[n] + "";

                MailApp.sendEmail(EMAIL, 'New Products Added to Suttons Feed', details)

            }

        }

    }
}

[Ernő Horváth]

Hi Michael,

It's still very slow I suppose.
For example you don't need to go from the first element to the last when you find a match! So using while instead of for and adding an additional condition like  declare var found = false before the while.

while (( x < spreadsheetrowIndex) && !found) 

and when you found a match just set found = true;
Will double your speed because when you found a match you can stop carry on searching.

The solution I searched for is 'binary search' if you sort the longer array you can use binary search to find if the key is in the array or not.

http://en.wikipedia.org/wiki/Binary_search_algorithm

also this is how you can compare strings in JS 
http://stackoverflow.com/questions/2167602/optimum-way-to-compare-strings-in-javascript

I think there is an even faster way to to do this specific problem which will solve this to you within O(n+m) compute speed, but I think the binary algorythm will boost your speed enough.

If I have time I'll write you down the fastest way... but I don't know if I have time.

BR,

Erno Horvath

[Michael King]

Hi Erno,

Thanks for this,

'If I have time I'll write you down the fastest way... but I don't know if I have time.' - I don't expect you to do that at all, I really appreciate the help you are giving. This is a great opportunity for me to learn.

I'll go back and have a look at what I can do here to make it faster again, have a good weekend.

mike

On Friday, May 17, 2013 9:31:32 AM UTC+1, Michael King wrote:

[Ernő Horváth]

Hi Michael,

Haha, sorry but I actually solved it :)

Please bear in mind this _only_ works well if both arrays have unique elements, so it's a special case of comparing two arrays, however it's quite fast.

function main() {

    var array1 = ['dad','uh','hgj','zugs','bb','hello'];

    var array2 = ['hello','bb','uh']

    //concat two arrays

    var fullList=array1.concat(array2);

    //sort the concatenated array

    fullList.sort();

    

    //go through the array, stop before reach end, because of n+1 index

    for (var n = 0; n < (fullList.length-1); n++) {

      if (fullList[n] == fullList[n+1]) {

       //remove matching two elements 

       fullList.splice(n, 2); 

        

      }

      

    }

      

    for (var n = 0; n < fullList.length; n++) {

      Logger.log(fullList[n]);  

      }

  

  

}

fullList will contain elements array1 that not in array2. I think that is what you needed for.

Please let me know if it works for you.

BR,

Erno Horvath

[Michael King]

This is ideal thanks Erno

Mike

On Friday, May 17, 2013 9:31:32 AM UTC+1, Michael King wrote:

[Ernő Horváth]

Uh, Mike someone found a bug on my script :-(

And he is right... 

Please add n--; after splice like this:

if (fullList[n] == fullList[n+1]) {

       //remove matching two elements 

       fullList.splice(n, 2); 

      n--;  

      }

It should work now properly.

Erno