Google App Script Get Multiple Index Positions

Question

I'm trying to figure out a way to get the index position of each repetition of a string.

The goal is to get each of those index positions and use the difference between each position to create a subtotal. The problem at hand is two fold. First, the distance between each string is not a standard length. Second, I seem to not be able to find out how to get multiple index positions of a particular string. I'm currently using the following code to cycle through different ranges to insert equations in to the sheet.

var Architecture = function(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var activeSheet = ss.getActiveSheet();
var data = activeSheet.getRange(18, 2, 7, activeSheet.getLastColumn()).getValues();

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

    var rowData = data[i];
    var checkData = data;
    var row = checkData[i];
    var colB = row[0]; 

    if(colB == 'Subtotal'){

      activeSheet.getRange(18 + i, 5, 1, data[0].length-4).setFormula('=iferror(sum(E' + (i+12) + ':E' + (i+17) + '))');
    }else{
      activeSheet.getRange(18 + i, 5).setFormula('=iferror(sum(F' + (i+18) + ':DU' + (i+18) + '))');
      activeSheet.getRange(18 + i, 6)
      .setFormula('=iferror(sum(filter(Invoices!$E:$E,Year(Invoices!$B:$B)=year(F$12),MONTH(Invoices!$B:$B)=Month(F$12),Invoices!$F:$F=$B' + (i+18) + ',Invoices!$A:$A=$C$2)))')
      .copyTo(activeSheet.getRange('F18:DU23'));
    }
  }
};

var DueDiligence = function(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var activeSheet = ss.getActiveSheet();
var data = activeSheet.getRange(26, 2, 15, activeSheet.getLastColumn()).getValues();

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

    var rowData = data[i];
    var checkData = data;
    var row = checkData[i];
    var colB = row[0]; 

    if(colB == 'Subtotal'){

 activeSheet.getRange(26 + i, 5, 1, data[0].length-4).setFormula('=iferror(sum(E' + (i+12) + ':E' + (i+25) + '))');
    }else{
      activeSheet.getRange(26 + i, 5).setFormula('=iferror(sum(F' + (i+26) + ':DU' + (i+26) + '))');
      activeSheet.getRange(26 + i, 6)
      .setFormula('=iferror(sum(filter(Invoices!$E:$E,Year(Invoices!$B:$B)=year(F$12),MONTH(Invoices!$B:$B)=Month(F$12),Invoices!$F:$F=$B' + (i+26) + ',Invoices!$A:$A=$C$2)))')
      .copyTo(activeSheet.getRange('F26:DU39'));
    }
  }
};

The goal would be to combine all these different functions into a single array that runs much quicker than the 18 seconds it currently takes to run.

EDIT

I've made some progress. Using the below function, I can get the index of each string, but it repeats that index position in the log for the number of rows that preceded it. For example, if the string is in index position two and then index position 10, the log shows 2,2,2,10,10,10,10,10,10,10,10 (only eight positions because 10-2=8).

var test = function(){

var ss = SpreadsheetApp.getActiveSpreadsheet();
var activeSheet = ss.getActiveSheet();
var data = activeSheet.getRange(14, 2, activeSheet.getLastRow()-13,1).getValues();

var newData =  data.map(function(r){ return r[0]; });

//Logger.log(newData);  

//Logger.log(data.indexOf("Subtotal", [i]));  

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

Logger.log(newData.indexOf("Subtotal", [i])); 
//    }
  }
};

Where is the string that you want to get the index position of in your data? — Cooper
Okay so it’s column 2. I would let each unique string be the property of an object that holds an array of indexes. There is a lot of ways to do this. I would probably use reduce but that’s up to you. — Cooper
I’m fairly new to this and haven’t used an object before. Can you explain a little bit how it works and how to set it up to hold an array of indexes? Or those indexes I pass from my looped indexOf function? — Squid1622

Cooper Cooper · Accepted Answer · 2020-06-07T16:25:04

Here's an example:

function getRowNumbersOfSameStrings() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getActiveSheet();
  const rg=sh.getRange(2,1,sh.getLastRow()-1,1);
  const vs=rg.getValues();
  let u={};
  vs.forEach(function(r,i){
    if(!u.hasOwnProperty(r[0])) {
      u[r[0]]=[];
      u[r[0]].push(i+2);//row numbers
    }else{
      u[r[0]].push(i+2);//row numbers
    }
  });
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(JSON.stringify(u)), "Results");
}

Here's my sample data:

COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8
7,2,4,0,1,1,10,4
9,3,2,5,2,6,7,6
9,2,10,10,9,7,2,11
10,7,2,10,2,6,2,0
0,2,4,0,6,3,10,0
2,7,6,7,0,3,5,8
6,9,6,11,3,5,5,8
1,4,5,5,10,0,3,11
4,3,3,3,1,9,3,2
9,4,0,9,2,8,11,2
5,8,7,0,3,6,5,4
5,3,5,3,2,3,8,3

Results:

{"0":[6],"1":[9],"2":[7],"4":[10],"5":[12,13],"6":[8],"7":[2],"9":[3,4,11],"10":[5]}

Google App Script Get Multiple Index Positions

2 Answers

Edit:

Reference: