CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api
AJ Johnson
AJ Johnson

Reputation: 21

looping in google script

I'm looking for help with how to run a loop in Google script for part of a function I am writing for a Google sheet (attached to a Google form).

The purpose of this loop is to do what I suppose could be called an inverse v-lookup (i.e. the 'index' column is on the far left of the defined range instead of the far right).

Here is my code: 

//Formula to align email addresses with ticket number
function getTicketNumber(input) {

//Step 1: define spreadsheet and select sheet
  var sp = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = sp.getSheetByName("LMS-emails");

//Step 2: define sheet range
var lastRow = sheet.getLastRow();
var lastColumn = sheet.getLastColumn();  
var range = sheet.getRange(1,1,lastRow,lastColumn)
var values = range.getValues();

//Step 3: forumla   
  for (var i=0; i <=lastRow; i++) { 
    if (values[i][3] == input) {
      return "BOOM!";}
    else {return "fizzle..."}
  }

}

I've checked each step of the code leading up to the for loop and everything is working properly. If I define the row # of 'values' it will compare to my 'input' and return "BOOM!" when expected.

However, when I run the function with the for loop included the function returns neither "BOOM!" nor "fizzle..."; the cell is simply blank. Any thoughts on what I am doing wrong or the specificities of running a for loop in Google script much appreciated!

Upvotes: 2

Views: 4075

Answers (3)

AJ Johnson
AJ Johnson

Reputation: 21

For anyone who might look at this question to answer their own questions - the solve was as follows:

OLD CODE

//Step 3: forumla   
  for (var i=0; i <=lastRow; i++) { 
    if (values[i][3] == input) {
      return "BOOM!";}
    else {return "fizzle..."}
  }

NEW CODE

 //Step 3: forumla   
  for (var i=2; i <=lastRow; i++) { 
    if (values[i][3] == input) {
      return "BOOM!";}
    else {return "fizzle..."}
  }

Row 3 (i=2 in the new code) is where the data I wanted to iterate through actually began in the spreadsheet, but I don't know the why as to that being the solution. Thoughts/explanations welcome!

Upvotes: 0

Emily
Emily

Reputation: 385

getLastRow() returns the position of the last row in your sheet that has content. If you have 10 rows with content, this will return 9 and assign it to lastRow.

Your issue is in your for loop condition: i < lastRow.length. You aren't comparing i to the number of rows in your sheet - can you figure out what exactly is going wrong here?

Upvotes: 0

Jasper Duizendstra
Jasper Duizendstra

Reputation: 2617

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

var lastRow = sheet.getLastRow() is an integer and the length of an integer is undefined hence, it never enters the for loop. Try adding return "zzz" at the end of the function. See if zzz is shown in the sheet and work back from there, using the Logger and execution transcript to see what is happening

Upvotes: 1

Related Questions