Copy last row from one sheet to another depending on cell value with constant updates in master shee-CodePudding

I am very technologically inept, but I will try to explain my problem as best as possible.

I have a Google Form, where I will be receiving a lot of submissions, I want to split the responses in the master response Google sheet into multiple tabs depending on the answer to one of the questions.

I want the script to trigger when the form has been submitted, and so the different tabs auto update with the last submission that is relevant to the specific tab.

I have tried one script, however, on submission, it copies all previous inputs together, so I end up with a lot of duplicates.

I am trying the following code where it only transfers the last submission:

function eA() {
  var sSheet = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = sSheet.getSheetByName("Form Responses 1");
  var lastRow = srcSheet.getLastRow();
  var lastColumn = srcSheet.getLastColumn();
  var lastRowOfData = srcSheet.getRange(lastRow, 1, 1, lastColumn).getValues();
  var oneD_array = lastRowOfData.join();
  var cellValue = srcSheet.getRange(2,2,999).getValue();
  if ( cellValue == "EA" ) {
    var tarSheet = sSheet.getSheetByName("EA");
    tarSheet.appendRow(oneD_array);
}

However, this doesn't seam to work.

Any help would be incredible.

Thank you so much.

CodePudding user response：

Append data to Sheet EA if answer in colum1 is EA

function eA(e) {
  const ss = SpreadsheetApp.getActive();
  if (e.values[1] == "EA") {
    ss.getSheetByName('EA').appendRow(e.values);
  }
}

function createTrigger() {
  if (ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == "eA").length == 0) {
    ScriptApp.newTrigger("eA").forSpreadsheet(SpreadsheetApp.getActive()).onFormSubmit().create();
  }
}

onFormSubmit Event Ojbect

If you want to learn more about the even object the add Logger.log(JSON.stringify(e)) near the beginning of the trigger handlerfunction.

Attempting to get the last row on a form submission is not a good idea because it triggers are comming at you very fast then you may actually be grabbing the wrong data. It's best to utilize the event object either values or namedValues.

CodePudding user response：

In this situation I wouldn't use google apps script and triggers, I'd use a query function.

Say your form results are on Sheet1, for the EA result sheet, you should paste this into cell A1:

=query(Sheet1!A:H,"select * where B='EA'")

Assuming column B has the variable you want to split on. Note that query language is case sensitive so if you said "where b = 'ea' " it would not work.

This will always be up to date as long as you don't put any rows in the selected range.