Copy Cell one sheet to another if not a duplicate based on criteria in multiple cells

Question

This is a follow-up to a previous post, but I could not figure out how to add it to the prior post. Here is the link to the prior post. Copying Data Sheet1 to Sheet2 so you can sort & edit both sheets (google apps script?)

PART II Thanks to Daniel, I have the following On sheet1 (Participant_Registration) there is a button that when pushed adds an ID both to sheet1 and to sheet2 (Learning_Sessions_Attendance). We then used the vlookup function to transfer columns B-D from sheet1 to sheet2 I wrote my own script to add instructions for participants.

Now here is what I need help with now. Small fix to original script The script, while fantastic, starts numbering ID’s at 2 instead of at 1. Is there any way to fix this?

New script On sheet1 (Participant_Registration) if Column L, M, or N have a value = “Group1” I would like the ID (in column A) transferred to sheet3 (Consultation1_Attendance). If L, M, or N have a value = “Group2” I would like the ID in (column A) transferred to Sheet4 (Consultation2_Attendance).

Here are the considerations for the new script, which have made it hard for me to write it. During the course of a training, people may change groups, which means the function needs to likely be an onEdit function and cannot just transfer the last row. It needs to transfer the active row and to only transfer it to the consultation attendance form if the ID is not already on that form. I tried the Query formula - does not work as then you cannot sort the rows in the consultation attendance sheet, and we are adding new data to that sheet.

Note: Eventually I will play with this script to have 10 possible consultation groups. I’m not sure if there is a way to automate copying the sheet Consultation1_Attendance by the number of groups and populating it with people who are assigned to the group.

Here is a link to the sheet. https://docs.google.com/spreadsheets/d/1cvKO2tPKeZdLWHc2XVfli8lYVylXCas4VgSG-wB_lLM/edit?usp=sharing

The code under code.gs runs. My attempts at the code for this are under ConsultGpAssign.gs (they are very sad attempts). I am just learning. I have been trying all kinds of things and could really use help.

I also pasted one attempt that is clearly not working here.

function onEdit(event) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = event.source.getActiveSheet();
  var r = event.source.getActiveRange();

  if(s.getName() == "Participant_Registration" && ((r.getColumn() == 12 &&     r.getValue() == "Group1") || (r.getColumn() == 13 && r.getValue() == "Group1") || (r.getColumn() == 14 && r.getValue() == "Group1"))) {
    var row = r.getRow();  //Not sure if this works

    //only want to transfer ID column A not sure how to do this
    var numColumns = s.getLastColumn();
    var targetSheet = ss.getSheetByName("Consultation1_Attendance");
    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
    s.getRange(row, 1, 1, numColumns).moveTo(target); //Only want to  transfer information if it's not already on sheet. Do not want to delete
  }
}

You are incrementing Biggest++; before setting the value. Line 71. Move that line beyond the two setValue() lines, and the numbering should start at 1. BUT, you need to assign a value to biggest at the same time that you declare it. Right now, you are declaring it as undefined by not assigning anything. Change var Biggest; to var Biggest=1; — Alan Wells
Thanks for the suggestion but unfortunately it didn't work. If I set biggest =1 it is always 1 var Biggest=1. The same thing happens when I move Biggest++ to after the SetValue lines. I can always put a 1 as the top ID on both sheets as a work around (already super lucky to have had help with a counter), but I just figured there was a way to program it to start from 1. — Chandra Ippen
In the original script, make var MaxID = 0; instead of var MaxID = 1; (The problem is not in Biggest++ exactly. The function getBiggestID is returning 1 as the current biggest ID even if there are no IDs) — Daniel Möller
That worked so now ID starts at 1 thanks. Was confused because code originally worked. — Chandra Ippen

Alan Wells Alan Wells · Accepted Answer · 2015-06-15T01:50:35

Looking at this line:

s.getRange(row, 1, 1, numColumns).moveTo(target); //Only want to  transfer information if it's not already on sheet. Do not want to delete

The moveTo() method cuts the range. But your comment states that you don't want to delete. Cutting the range will delete it. You already have a target range defined. Use getValues() to get what you want to copy:

var myValuesToCopy = s.getRange(row, 1, 1, numColumns).getValues();
target.setValues(myValuesToCopy);

Copy Cell one sheet to another if not a duplicate based on criteria in multiple cells

1 Answers