CODEWITHSUNDEEP
loopssearchgoogle-apps-scriptgoogle-sheetsrange
akajanedoe
akajanedoe

Reputation: 15

Loop through range to find matching string google scripts

I'm trying to loop through the top title row of my spreadsheet to find the index number of a column based on the title name so that if someone inserts a column my code won't break. Here's what I have so far:

  var sheet = SpreadsheetApp.getActive().getSheetByName('RawData');
  var values = sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns()).getValue(); //line that's breaking
  range.setNote("inside function");
  var i = 1;
  while(values[1][i] != name) {
    i++;
  }return i;
}

The code appears to break on the line where I set 'values,' and I can't figure out how to access the array I created in order to check which one contains the same string as the 'name' parameter. The setNote line is just for testing purposes, you can ignore it. Thanks in advance.

EDIT

function getColumnByName() {
  var name = "Ready For Testing?";
  var ss=SpreadsheetApp.getActive().getSheetByName('RawData');
  var vA=ss.getRange(1,1,ss.getLastRow(),ss.getLastColumn()).getValues();
  var hA=vA.shift();//returns header array vA still contains the data
  var idx={};
  var index = hA.forEach(function(name,i){idx[name]=i});//idx['header name'] returns index  
  return index;
}

I set name just for testing purposes, it will be passed as a parameter when I use the actual function

Upvotes: 1

Views: 1746

Answers (2)

Wicket
Wicket

Reputation: 38425

Try

function getColumnByName() {
  var name = "Ready For Testing?";
  var ss=SpreadsheetApp.getActive().getSheetByName('RawData');
  var vA=ss.getDataRange().getValues();
  var hA=vA.shift();//returns header array vA still contains the data
  var idx= hA.indexOf(name);
  if(idx === -1 ) throw new Error('Name ' + name + ' was not found');
  return idx + 1; // Returns the name position 
}

Instead of

var values = sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns()).getValue();

and instead of

var vA=ss.getRange(1,1,ss.getLastRow(),ss.getLastColumn()).getValues();

use

var values = sheet.getDataRange().getValues();
  1. getValue() only returns the value of top-left cell
  2. using sheet.getMaxRows() / sheet.getMaxColumns() returns the sheet last row / column which could cause getting a lot of blank rows / columns.
  3. compared with the second line code (getLastRow() / getLastColumn()) the proposed code line is "cheaper" (one call to the Spreadsheet Service instead of three (1. getRange, 2. getLastRow, 3 getLastColumn Vs. getDataRange) to get the data range and usually is faster too.

Regarding

var index = hA.forEach(function(name,i){idx[name]=i});

forEach returns undefined ref. Array.prototype.forEach

Upvotes: 2

Cooper
Cooper

Reputation: 64110

Try this:

function myFunction() {
  var ss=SpreadsheetApp.getActive()
  var sh=ss.getSheetByName('RawData');
  var vA=sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn()).getValues();
  var hA=vA.shift();//returns header array vA still contains the data
  var idx={};
  hA.forEach(function(h,i){idx[h]=i});//idx['header name'] returns index  
  var end="is near";//set a break point here and run it down to here in the debugger and you can see hA and idx
}

hA is the header name array

idx is an object that returns the index for a given header name

idx is all you need just learn to put these three lines of code in your script in the future and you won't need to use an external function call to get the index.

 var hA=vA.shift();//returns header array vA still contains the data
 var idx={};
 hA.forEach(function(h,i){idx[h]=i});//idx['header name'] returns index  
 vA still contains all of your data

Upvotes: 1

Related Questions