Preparing for execution - how to fix

[Marcel Brauner]

Hey people,

I am running a script that is basically comparing two arrays that I get from a spreadsheet. I compare values via for loop and check if they "match". Lastly I return a list of trues / falses. (each list has 200 values so 40k comparisons in total)

However in the Script I often run into preparing for execution loads of times, also the Function does not produce the same & intended outputs. 

So I would like to check on why the preparing for execution is taking so long and stack overflow gave me the idea that something in the code is taking lots of time to calculate, especially since occasionally, everything works perfectly

And ideas on how I can debug this? 

[Adam Morris]

If you produce a short sample of the code running, we could offer some performance tweaks perhaps. 

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/8a7de80d-e1a0-428a-8844-05a085a66cado%40googlegroups.com.

--

http://classroomtechtools.com

[marcel....@teampicnic.com]

Yes sure! - I am not sure if I can format it in here, does not seem like it

function afterdone_rebranding(){

var today = Utilities.formatDate(new Date(), 'Europe/Amsterdam', 'yyyy-MM-dd\' \'HH:mm:ss')

var overview_sheet = spreadsheet.getSheetByName("Rebranding & Image")

var helper_sheet = spreadsheet.getSheetByName("H1")

overview_sheet.getRange("J8").setValue("Running...").setBackground("#fab7b7")

overview_sheet.getRange("H7").setValue(today)

SpreadsheetApp.flush();

var existing_list = helper_sheet.getRange("A1:C").getValues().filter(function(el){return (el[1] != "" || el[2] != "")})

console.log(existing_list)

var articles_1 = overview_sheet.getRange("E11:E200").getValues().filter(function(el){return el[0] != ""})

var articles_2 = overview_sheet.getRange("W11:W200").getValues().filter(function(el){return el[0] != ""})

console.log("Artikel 1")

console.log(articles_1)

var articles_1_status = [];

var articles_2_status = [];

for (var i in articles_1){

  articles_1_status.push([false,null,false])

  for (var j in existing_list){

  if (existing_list[j][0] == articles_1[i][0]){

    if(existing_list[j][1] == ""){

    articles_1_status[i][0] = false

    }

    else{articles_1_status[i][0] = true;

    }

    

    if(existing_list[j][2] == ""){

    articles_1_status[i][2] = false

    ;

    }

    else{articles_1_status[i][2] = true;

    break

    }

  }

  }

}

 Logger.log(articles_1_status)

for (var j = 0; j < 30; j++){

  articles_1_status.push([false,"",false])

}

Logger.log(articles_1_status)

// overview_sheet.getRange(11,17,articles_1_status.length,3).setValues(articles_1_status)

for (var i in articles_2){

  articles_2_status.push([false,null,false])

  for (var j in existing_list){

    if (existing_list[j][0] == articles_2[i][0]){ 

      if(existing_list[j][1] == ""){

        articles_2_status[i][0] = false

      }

      else{articles_2_status[i][0] = true;

          }

     

    if(existing_list[j][2] == ""){

    articles_2_status[i][2] = false;

    }

    else{articles_2_status[i][2] = true;

        

        }

    } 

}

}

// Used to clear the rows below from potential previous articles

for (var j = 0; j < 30; j++){

  articles_2_status.push([false,"",false])

}

  

Logger.log(articles_2_status)

// overview_sheet.getRange(11,34,articles_2_status.length,3).setValues(articles_2_status)

overview_sheet.getRange("J8").setValue("Finished").setBackground("white");

  

[Adam Morris]

Any chance you can explain what you're after? Also some sample input would be good. 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/9a336704-3f6d-4abd-88af-61225f45ff3cn%40googlegroups.com.

[marcel....@teampicnic.com]

Yes so what we are after. The var existing_list is an array of items with an ID in Col1 and 2 Timestamps in Col2 & Col3

We have another list (var articles_1) that has IDs only. Now we want to look if the items from article_1 are contained in the existing_list. 

And if so, it should return true

We collect the data in a new array articles_1_status. So first I loop in a [false, null,false] - which is just a placeholder. If the article is later be found, the "false" will be changed into "true"

for (var i in articles_1){

  articles_1_status.push([false,null,false])

  for (var j in existing_list){

  if (existing_list[j][0] == articles_1[i][0]){

    if(existing_list[j][1] == ""){

    articles_1_status[i][0] = false

    }

    else{articles_1_status[i][0] = true;

    }

    

    if(existing_list[j][2] == ""){

    articles_1_status[i][2] = false

    ;

    }

    else{articles_1_status[i][2] = true;

    break

    }

  }

  }

so example: 

List 1:

ID | Timestamp 1 | Timestamp 2

101 | 12-12-20 | 12-12-20

102 | null | null

103 | null | 14-12-20

List 2:

101

102

104

Result:

101 | true | null | true

102 | false | null | false

104 | false | null | false

[bbau...@gmail.com]

In one project I was comparing 2 arrays read from sheets, so.

Maybe this will help you somehow.

// ver. 9 20200812 1635

function compareArrays(arrS, arrC) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheetAll = ss.getSheetByName("Calendars");

  var sheetNew = ss.getSheetByName("Source");

  var allLR = sheetAll.getLastRow();

  var NewLR = sheetNew.getLastRow();

  var sr = 7; // Start row of data block

  var arrDelEID = [];

  var rowsAll = sheetAll.getRange(sr, 5, allLR - sr + 1).getValues();

  var rowsNew = sheetNew.getRange(sr, 5, NewLR - sr + 1).getValues();

    

  var rowsAllArray = rowsAll.map(function(e) {return e[0];} );

  var rowsNewArray = rowsNew.map(function(e) {return e[0];} );

  // compare two arrays

  var arrMatch = rowsAllArray.reduce(function(ar, e, i) {

    if (rowsNewArray.some(function(f) {return e == f})) ar.push(i + sr);

    return ar;

  },[]);

  

  var rowsAll1 = sheetAll.getRange(sr, 1, allLR - sr + 1).getValues();

  var rowsAll1Array = rowsAll1.map(function(e) {return e[0];} );

  

  var rowsAll8 = sheetAll.getRange(sr, 8, allLR - sr + 1).getValues();

  var rowsAll8Array = rowsAll8.map(function(e) {return e[0];} );

  

  var rowsAll10 = sheetAll.getRange(sr, 10, allLR - sr + 1).getValues();

  var rowsAll10Array = rowsAll10.map(function(e) {return e[0];} );

  for(var i = 0; i < arrMatch.length; ++i) {

    var num = arrMatch[i] - sr;

    var val = [rowsAll8Array[num], rowsAll10Array[num], rowsAllArray[num], rowsAll1Array[num]];

    arrDelEID.push(val);

  }

//  Logger.log('arrDelEID_compare = '); Logger.log(arrDelEID);

  

  return arrDelEID;

}

понедельник, 14 декабря 2020 г. в 13:02:01 UTC+3, Marcel Brauner: