Automatically add variables to array?

Question

In a google script I have written something to check my monthly expenses, which are listed in a google sheet. Based on words the script finds, every line gets a category tag. It works fine, but the number of words to search for is getting big. And the array is getting big too.

I have listed 6 pairs (words to find, tag to add) - but in real version I have as many as 35. How can I create the pairs, and load everything automatically in the array?

This is my script:

function myFunction() {
// check usual suspects
var A1 = ["CAFE", "HORECA"]
var A2 = ["ALBERT", "AH"]
var A3 = ["VOMAR","Vomar"]
var A4 = ["HEMA","HEMA"]
var A5 = ["KRUID","Drogist"]
var A6 = ["RESTA", "Horeca"]

// in Array
var expenses = [A1,A2,A3,A4,A5,A6]

var ss = SpreadsheetApp.getActiveSheet();
var data = ss.getDataRange().getValues(); // read all data in the sheet


for (i in expenses)
{for(n=0;n<data.length;++n){ // iterate row by row and examine data in column A

if(data[n][3].toString().toUpperCase().match(expenses[i][0])==expenses[i][0]){ data[n][4] = expenses[i][1]};


// if column D contains 'xyz' then set value in index [5] (is column E)
}

Logger.log(data)
ss.getRange(1,1,data.length,data[0].length).setValues(data); // write back to the sheet
}

}

Harold · Accepted Answer · 2016-09-06 12:32:34Z

4

I can propose you that:

function multiPass(){
  var searchCriterions = [
                ["CAFE","HORECA" ],
                ["ALBERT", "AH"],
                ["VOMAR","Vomar"],
                ["HEMA","HEMA"]
              ];
   var dico = {};
   var patt = "";

   for (var i in searchCriterions) {
     dico[searchCriterions[i][0]] = searchCriterions[i][1];
     patt += "("+searchCriterions[i][0]+")";
     if((Number(i)+1)<searchCriterions.length){
     patt += "|";
     }
   }   
  var re = new RegExp(patt,"");

  var ss = SpreadsheetApp.getActiveSheet();
  var data = ss.getDataRange().getValues(); // read all data in the sheet
  Logger.log(re);
  for(n=0;n<data.length;++n){ // iterate row by row and examine data in column A
  // THAT'S NOT COLUMN "A", 3 --> "D"
  var test = data[n][3].toString().toUpperCase().match(re);
  Logger.log(test);
  if(test!==null){ 
     data[n][4] = dico[test[0]]
    };
}
ss.getRange(1,1,data.length,data[0].length).setValues(data); // write back to the sheet
}

instead of using variable for your "pairs" prefer to use a big table (it's less painfull to write) then transform your pairs in object to quickly access the second argument of the pair and create a big regexp that check at once all the keywords instead of parsing them one by one.
Now as we are using a big array as search criterions we can totally imagine that this big array is loaded instead of hard coding it. If you have a sheet where the data is you can change the code this way:
var searchCriterions = SpreadsheetApp.getActive().getRange("namedRange").getValues();

answered Sep 6, 2016 at 12:32

Harold

3,3671 gold badge20 silver badges27 bronze badges

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

1 Comment

Arie Osdorp Over a year ago

Awesome, exactly what I was looking for! Copied the code, and works perfectly. Will need to spend some time really understanding how it works, but that's a piece of homework.

Collectives™ on Stack Overflow

Automatically add variables to array?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related