values - how to match data in excel from 2 worksheets




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

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);

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
  }
}

Copying Data Sheet1 to Sheet2 so you can sort & edit both sheets (google apps script?)

For that to happen automatically (one sheet's change updating another sheet), you will surely need an "event/trigger" to run a script whenever you change a cell. (that is the "onEdit()" function).

But since scripts are likely to fail sometimes (even when they are perfect, that's because of some Google issues), it's not guaranteed that the sheets will always contain the same data.

But, if I could suggest another way, do not let ID be optional. If that is a real ID (like the person ID card number), create another ID exclusively for working with the sheet.

I have edited your second sheet showing a suggestion of how to do it without using scripts. The only things you must be aware of are:

  • Do not create two people with the same ID.
  • You have to insert (only) the ID manually in the second sheet.

The VLOOKUP forumla will search for that ID in the first sheet and return the data in the same line. You can sort any sheet in whatever way you like. As long as you don't change people's IDs.

So, in sheet 2, use this in the First Name, Last Name and Email address:

 =vlookup(A10,Participant_Registration!$A:$D,2,false)
 =vlookup(A10,Participant_Registration!$A:$D,3,false)
 =vlookup(A10,Participant_Registration!$A:$D,4,false)

Just extend this formula downwards

I hope this helps. I would avoid scripting for that at any cost. It would be my last resort. (Scripts also need to be changed if you want to rearrange your sheet, and if not, they might cause trouble, write over existing data...)


I also added a button (insert - drawing) and put a script in it (right button, click down arrow, "transfer? script" -- translated from Portuguese).

If you lock all four columns in sheet2 and lock the ID column in sheet 1, people will not be able to chang IDs and cause mess. They can edit people in sheet 1 and not change the formula in sheet2. Script is not affected by sorting or empty spaces (it adds the person in the first empty row it finds).

I added "named ranges" for the four column headers. (With named ranges, the script can refer to names instead of coordinates, which enables you to rearrange the sheet inserting and deleting columns, or moving them with CUT and paste - but the VLOOKUP formula will need manual update if you rearrange columns).

Here is the code: (it could get better if you manage to create dialog boxes and ask for the person's data inside that dialog, then you could lock everything - and you would need an edit button besides the add).

function AddPerson()
{
  var S1Name = "Participant_Registration";
  var S2Name = "Learning_Sessions_Attendance";

  var ID1Name = "regID";
  var ID2Name = "learnID";

  //these vars are not used in this script
  var FN1Name = "regFirstName";
  var FN2Name = "learnFirstName";
  var LN1Name = "regLastName";
  var LN2Name = "learnLastName";
  var Email1Name = "regEmail";
  var Email2Name = "learnEmail";


  var sSheet = SpreadsheetApp.getActiveSpreadsheet();
  var Sheet1 = sSheet.getSheetByName(S1Name);
  var Sheet2 = sSheet.getSheetByName(S2Name);

  var ID1 = getRangeByName(sSheet, Sheet1.getName(), ID1Name);
  var ID2 = getRangeByName(sSheet, Sheet2.getName(), ID2Name);     Logger.log("ID2: " + ID2.getValue());

  var Empty1 = getFirstEmpty(ID1);
  var Empty2 = getFirstEmpty(ID2);

  var Biggest1 = getBiggestID(ID1);    Logger.log("Biggest 1: " + Biggest1);
  var Biggest2 = getBiggestID(ID2);    Logger.log("Biggest 2: " + Biggest2);



  if (Biggest1 !== Biggest2)
    Browser.msgBox("Warning: there are IDs in one sheet that are not in the other sheet");

  var Biggest;
  if (Biggest1 > Biggest2) Biggest = Biggest1;
  else Biggest = Biggest2;

  Biggest++;

  Empty1.setValue(Biggest);
  Empty2.setValue(Biggest);
}

function getFirstEmpty(Header)
{
  while (Header.getValue() !== "")
  {
    Header = Header.offset(1,0);
  }

  return Header;
}

function getBiggestID(Header)
{
  var Sheet = Header.getSheet();
  var LastRow = Sheet.getLastRow();

  var Values = Sheet.getRange(Header.getRow(), Header.getColumn(), LastRow - Header.getRow() + 1).getValues();

  var len = Values.length;
  var MaxID = 1;
  for (var i = 0; i < len; i++)
  {
    var val = Number(Values[i]);
    if (!isNaN(val) && val > MaxID)
      MaxID = val;
  }

  return MaxID;
}


function getRangeByName(spreadSheet, sheetName, rangeName)
{
  Logger.log("Trying range: " + "'" + sheetName + "'!" + rangeName);
  return spreadSheet.getRangeByName("'" + sheetName + "'!" + rangeName);
}