Google-apps-script – Google App Script If Statement Based On Cell Value

google-apps-script

I have a spreadsheet that is populated with data via a google form. I also have a Google App script associated with the response spreadsheet. The script has several versions that do slightly different things and I need to run a specific version of the script based on a cell value in the response sheet. The problem is it always needs to be the last cell in column "J" with data in it.

For example, the form gets submitted and populates row 5. I need the "if statement" to look in J5. The next time the form is submitted it will populate row 6. That time I need the "if statement" to look in J6. And so and so on.

I'm fairly new to google app script, but I'm familiar with if statements.

Any help is greatly appreciated.

Best Answer

If your script runs when the form is submitted, then presumably it is triggered on form submit. In this case, the function will receive an event object, in which the property values is the array of values submitted. This is the preferable way to access just-submitted form values, compared to fetching them from responses sheet.

The function would be like,

function processForm(e) {
  if (e.values[9] == 'something') { 
    // do something
  }
  else {
    // do something else
  }
}

Here the index is 9 because J is the 10th letter of the alphabet, and JavaScript index begins with 0.

Related Solutions

Google-sheets – Parsing timestamp in Google Sheets with script

The following piece of code will set the background color of the entire row:

function onFormSubmit(e) {
  // get data and make it a 1D array
  var time = e.range.getValues()[0];

  // get the minutes and hours of the timestamp (time[0]) 
  var minutes = time[0].getMinutes(), hours = time[0].getHours();

  // calculate the total amount of minutes
  var totalMin = (hours * 60) + minutes;

  // do the logic (thanks to geekzspot for the 1087)
  if(totalMin > 1087) {
    e.range.setBackgroundColor('yellow');
  } else {
    e.range.setBackgroundColor('red'); 
  }  
}

UPDATE
This code will set only the back ground color of the timestamp:

function onFormSubmit(e) {
  // get active spreadsheet and sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();

  // get data and make it a 1D array
  var time = e.range.getValues()[0];

  // get row index
  var row = e.range.getRowIndex();

  // get the minutes and hours  
  var minutes = time[0].getMinutes(), hours = time[0].getHours();

  // calculate the total amount of minutes
  var totalMin = (hours * 60) + minutes;

  // set range to timestamp
  var range = sh.setActiveCell("A"+row);

  // do the logic (thanks to geekzspot for the 1087)
  if(totalMin > 1087) {
    range.setBackgroundColor('yellow');
  } else {
    range.setBackgroundColor('red'); 
  }  
}

Add this script, via the script editor, to your spreadsheet and set the trigger:
enter image description here

Here's the form: Test For Web Applications and here's the sheet

Google-apps-script – Google Apps Script E-Mailing Stale Data

The relevant script method, as said in a comment, is

SpreadsheetApp.flush();

Which makes sure that any pending changes to the spreadsheet are actually applied before the execution continues. For example, to make sure that new data is actually entered (and not just batched, awaiting its CPU time) before the contents are emailed.

Documentation explains:

Spreadsheet operations are sometimes bundled together to improve performance, such as when doing multiple calls to Range.getValue(). However, sometimes you may want to make sure that all pending changes are made right away, for instance to show users data as a script is executing.

Best Answer

Related Solutions

Google-sheets – Parsing timestamp in Google Sheets with script

Google-apps-script – Google Apps Script E-Mailing Stale Data

Related Topic