Loop through range to find matching string google scripts

Question

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

Wicket · Accepted Answer · 2020-06-25 23:18:43Z

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();

getValue() only returns the value of top-left cell
using sheet.getMaxRows() / sheet.getMaxColumns() returns the sheet last row / column which could cause getting a lot of blank rows / columns.
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

Cooper · Accepted Answer · 2020-06-25 22:07:31Z

1

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

edited Jun 25, 2020 at 22:07

answered Jun 25, 2020 at 21:04

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

10 Comments

akajanedoe Over a year ago

Is this instead of what I have so far, or in addition to it? The code breaks at the last line you wrote and I don't understand where it specifies which string it's looking for, I passed the string in as a parameter on my previous example

Cooper Over a year ago

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

Cooper Over a year ago

I just fixed it

Cooper Over a year ago

Yes. This replaces your code. Just use idx to the return the index. I actually just do this in the beginning of my code when I want to write functions that allow the columns to move arround and be replaced.

akajanedoe Over a year ago

Okay thanks. Next time, a short written explanation rather than just a piece of code would be helpful. It's also generally good practice to house code that will be repeated often in a helper function like I am trying to do, it's a design choice.

|

Collectives™ on Stack Overflow

Loop through range to find matching string google scripts

2 Answers 2

Comments

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related