I am working with some colleagues who want the following to happen within a Google Sheet:

• A Google Form contains a question that asks which Counselor a student is assigned to (among other questions)
• Forms are submitted throughout the year by students
• When a form is submitted, the form data goes into a Google Sheet in a Responses sheet
• The Counselors would like a copy of each row to appear in another sheet within the main Sheet, based on the name of the Counselor
• In their own sheets, each Counselor needs to be able to manipulate the data (sorting, highlighting rows, adding notes to the row/submission) ←hence a copy is needed instead of a query

I have the following script that copies the rows in the correct Counselor sheet, and does not copy a row into a Counselor sheet if it already appears. However, if a Counselor modifies anything in the row, the script will make a duplicate row (with the original data) the next time it is run, perhaps because it sees the modified row as not an exact match.

Is there a way to modify my script so it can check against a unique part of a row in the Responses sheet (the columns at indexes 0 and 1 together in the same row create a unique entry) in any part of a Counselor sheet before it creates a copy? In other words, it would not create a duplicate row if the Counselor modifies anything except for columns 0 and 1.

function copyData() {
  var formResponses = SpreadsheetApp.getActive().getSheetByName("Form Responses 1");
  var formValues = formResponses.getDataRange().getValues();
  formValues.shift(); // remove the header row
  formValues.forEach(function(row) {
    var sheetName = row[4]; // the value of "My College Counselor is" column
    var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
    var range = sheet.getDataRange();
    var data = range.getValues();
    var duplicate = false;
    for (var i = 0; i < data.length; i  ) {
        var currentRow = data[i];
        if (currentRow.join() == row.join()) {
            duplicate = true;
            break;
        }
    }
    if (!duplicate) {
      sheet.appendRow(row);
    }
  });
}

I'm stuck at this point and am not sure how to proceed.

NOTE: I have code to add a button to the menu list for the Counselors to run this script as needed since the forms can be submitted at any time. Using "onFormSubmit" does not work because there is a potential for multiple students to submit the form at the same time, which I've seen can cause a row or two to not be copied over.

CodePudding user response：

If I understand your question correctly, you want to find a way to avoid duplicated rows, even if you edit them.

In order to do that, you have to define a value for each row that won't change and that is unique. My suggestion would be the following :

1. Installable trigger with the function custom_onFormSubmit

2. In the function get Uid (unique ID), and add it to each row submitted

3. Edit your code in order to search duplicate only with this Uid

First, add this function your Google Form Apps Script:

//add unique ID at a defined column each time a google form is submitted
function custom_onFormSubmit(e){

  var uuid = e.triggerUid;

  //alternatily you can use: 
  //var uuid = Utilities.getUuid();

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName([SHEETNAME]); 
  var range = sheet.getDataRange();
  var row = range.getLastRow();

  sheet.getRange(row, 10).setValue(uuid); //column 10 is for example, adapt to your need
}

------ EDIT: alternative function without trigger onFormSubmit, add this function before

function check_insert_uuid(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName([SHEETNAME]); 
  var range = sheet.getDataRange();
  var values = range.getValues();

  for (var x = 0; x < values.length; x  ) {
    if (values[x][10] == "") {
      let uuid = Utilities.getUuid();
      range.offset(x, 10, 1, 1).setValue(uuid);
    }
  }
  SpreadsheetApp.flush(); //force new data to sync before copyData
  copyData(); //call copy function
}

------ END EDIT -------

Then you just have to edit your function copyData

FROM:

    for (var i = 0; i < data.length; i  ) {
        var currentRow = data[i];
        if (currentRow.join() == row.join()) {
            duplicate = true;
            break;
        }
    }

TO:

    for (var i = 0; i < data.length; i  ) {
        var currentRow = data[i];
        if (currentRow[10] == row[10]) { //same example of column index 10
            duplicate = true;
            break;
        }
    }

References:

• Installable Triggers

• Google Form Events

• Apps Script getuuid (Unique ID are not 100% unique in time and space, but will certainly answer your project)

CodePudding user response：

Based on the help from @waxim-corp, here is the final script that accomplishes my goal:

function onOpen(e) {
  let ui = SpreadsheetApp.getUi(); 
  ui.createMenu("