Google-apps-script: Simple Check Value - Return Value

Question

I have a Google Sheet accepting form responses, and I want to have a value placed in the first blank cell in the row when a response arrives.

For example, when this script sees a response arrive from the form in row 15, and if the last value from the form fills cell D15, it should place an "X" in cell E15.

I have built apps with php, but am a javascript newb. Can anyone point me in the right direction for ideas of how to approach this in Google Sheets/Scripts?

Here is what I have so far:

function ?(event) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

//if form is submitted
if (event.?values[?] == '?') {
//Add "X" to the first empty cell in the row
?.?("x");
}
}

I have added "?"s where I am stuck. Many thanks in advance for any help!

Tiffany G. Wilson Tiffany G. Wilson · Accepted Answer · 2016-08-18T05:37:50

I would make use of the triggers for form submission within Google Sheets:

function onEdit(e){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = e.range; // Get the cells in the spreadsheet that were edited
  var col = range.getLastColumn(); // Last column with data
  var row = range.getRow(); // Row containing the new data
  sheet.getRange(row,col+1).setValue('X')
}

The above code should find the new row in the form response sheet, find the last column of existing data, and add the 'X' to the first unoccupied column. Let me know if this works for you!

Google-apps-script: Simple Check Value - Return Value

1 Answers