CODEWITHSUNDEEP
google-apps-script
Justin Kaese
Justin Kaese

Reputation: 321

How can i set a range to be the last row of data in the spreadsheet? Using Google Apps Script

How can I change my variable "range" to be the last row of data entered into the spreadsheet instead of 'A2:Z2'?

function findFault() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Inspections");

  //Set the range to be search through 
  var range = sheet.getRange('A2:Z2');

  //Turn the range into data values
  var data = range.getValues();

  //Declare fault and the value to search for
  var fault = "Fault";

  //Iterate through data to find fault
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      if (data[i][j] == fault) {

        //log to test if working
        Logger.log("Found Fault");
      }
    }
  }
}

Upvotes: 1

Views: 451

Answers (1)

Justin Kaese
Justin Kaese

Reputation: 321

I updated my code and got it working. If anyone has tips on making it more efficient please post.

function findFault() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Inspections");

  //Get the last row if data
  var lastRowNumber = sheet.getDataRange().getNumRows();
  var allColumns = sheet.getDataRange().getNumColumns();

  //Set the range to be search through 
  var range = sheet.getRange(lastRowNumber, 1, 1, allColumns);

  //Turn the range into data values
  var data = range.getValues();

  //Declare fault and the value to search for
  var fault = "Fault";


  //Iterate through data to dinf fault
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      if (data[i][j] == fault) {

        //log to test if working
        Logger.log("Found Fault");
      }
    }
  }
}

Upvotes: 1

Related Questions