CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Corey Coble
Corey Coble

Reputation: 1

Getting Last Row of Data, Ignoring Formulas and Use Row Data to send email - Google Apps Script

I'm collecting data from a Google Form that will be used in formulas that calculate costs and mileage. I've used =QUERY to bring the responses to another sheet 'Event Calculator'. When I run the forEach loop to get the data, it picks up cells with a formula. I would like my code to find the last row with data and pull some numbers from cells to use in an email that will be sent right after they submit the form (on a trigger). Is there a way to find the last row and then run the forEach loop? or am I going about this all wrong?

I've tried using if statements and forEach, but don't seem to have the correct order and am unable to just find the last row with data in it, not a formula.

The expected results are a single line of data that can be placed in an email function to send the results of the formulas to the respondent.

I've tried using the forEach loop, but it returns rows that contain formulas. I have tried if statements but cannot seem to get it to work with the forEach loop to get the data.

function travelReport() {

  // get data from the Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Event Calculator');
  var allRange = sheet.getDataRange();
  var allData = allRange.getValues();

  // remove the header row
  allData.shift();

  // loop over rows of data
  allData.forEach(function(row) {


    // get data
    var email = row[0];
    var eventName = row[1];
    var coordName = row[2];
    var  startName = row[3];
    var destinationName = row[4];
function travelReport() {

  // get data from the Sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Event Calculator');
  var allRange = sheet.getDataRange();
  var allData = allRange.getValues();

  // remove the header row
  allData.shift();

  // loop over rows of data
  allData.forEach(function(row) {

The results so far is that the forEach loop picks up all the rows I have a formula in. Several errors occur when I try to add code to find the last row with data.

Upvotes: 0

Views: 209

Answers (1)

Cooper
Cooper

Reputation: 64062

Try this approach:

function travelReport(e) {
  var ss=SpreadsheetApp.getActive();
  Logger.log(e);
  //I assume timestamp is actually e.values[0].  But you can just look at Logger.log to figure it out.
  var email=e.values[1];
  var eventName=e.values[2];
  var coordName=e.values[3];
  var startName=e.values[4]
  var destinationName=e.values[5];
  GmailApp.sendEmail(email, subject, body, options);//Presumably you know how to configure all of this

}

function createTrigger(name) {
  var ss=SpreadsheetApp.getActive();
  if(!isTrigger(name)) {
    ScriptApp.newTrigger(name).forSpreadsheet(ss.getId()).onFormSubmit().create();
  }
}

function isTrigger(funcName){
  var r=false;
  if(funcName){
    var allTriggers=ScriptApp.getProjectTriggers();
    for(var i=0;i<allTriggers.length;i++){
      if(funcName==allTriggers[i].getHandlerFunction()){
        r=true;
        break;
      }
    }
  }
  return r;
}

Want a more precise answer...Give me more details.

Upvotes: 0

Related Questions