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

Question

I am working in goggle sheets and think I need to use a google apps script to do what I want, but I am a psychologist at a non-profit University hospital trying to do some good and not a programmer (which probably shows) and I am desperately in need of help. I am trying to set up a series of spreadsheets to track participation in workshops for our treatment method.

1) I have a sheet “Participant_Registration” where basic information is entered

2) I want to transfer information from only the first four columns (A:D) of “Participant_Registration” to a second sheet “Learning_Sessions_Attendance”

3) I am also transferring the same information to a third sheet 'Consultation1_Attendance' – but I need to first filter and select only those people assigned to that group.

Here is a link to a copy of my spreadsheet. https://docs.google.com/spreadsheets/d/17d0bT4LZOx5cyjSUHPRFgEZTz4y1yEL_tO3gtSJ4UJ8/edit?usp=sharing

More generically this is what I am trying to do. Is this possible in google app scripts? It seems it should be.

1) I have original data in sheet1

2) I want the first four columns (A:D) to transfer to sheet2 (it is fine if I need a trigger variable)

3) I want them to transfer in such a way that if you sort either sheet, the data are still fine (still linked to the right line).

4) Ideally if there is a change to the data in the source sheet (Sheet1) the same change will be made in Sheet2.

5) Ideally this would all happen automatically without human intervention through a script.

Any ideas?? I so need your help. I have been all over the forum, git hub, and done a ton of searches and tried following a lot of examples I saw but nothing works. I really need help.

Here are my sample scripts each with a problem:

//The following code copies a range from sheet1 to sheet2 as I wanted. A problem occurs if after if we copy the data from sheet1 we add data to other columns on sheet2. Later if we sort on some variable (which people are bound to do) if the function is deployed again it will overwrite data meaning the data from sheet1 are not connected to the right individual on sheet2

function CopyRange() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("Participant_Registration");
var range = sheet.getRange("A14:D");
var values = range.getValues();
var target = ss.getSheetByName("Learning_Sessions_Attendance");
var target_range = target.getRange("A10:D");
range.copyTo(target_range);
}

So I tried again. This time I tried to just copy the last edited row from sheet1 to sheet2. This function does not appear to work for me.

function CopyRow2() {
// Get Spreadsheets
var source = SpreadsheetApp.openById("1egn6pnRd6mKMGuQxX_jtgwYDtkuMUv2QJItLdh7aIEs");
var target =     SpreadsheetApp.openById("1egn6pnRd6mKMGuQxX_jtgwYDtkuMUv2QJItLdh7aIEs");

// Set Sheets
var source_sheet = source.getSheetByName("Participant_Registration");
var target_sheet = target.getSheetByName("Learning_Sessions_Attendance");


var rowIdx = source_sheet.getActiveRange().getRowIndex();
var rowValues =        source_sheet.getRange(rowIdx,1,1,source_sheet.getLastRow()).getValues();
Logger.log(rowValues);
var destValues = [];
destValues.push(rowValues[0][0]);// copy data from col A to col A
destValues.push(rowValues[0][1]);//copy data from col B to col B
destValues.push(rowValues[0][2]);//copy data from col C to col C
destValues.push(rowValues[0][3]);//copy data from col D to col D
var dest=source.getSheets()[4];
   dest.getRange(dest.getLastRow()+1,1,1,destValues.length).setValues([destValues]);//update destination sheet with selected values in the right order, the brackets are there to build the 2D array needed to write to a range
}

So I tried again and again. I have lots of examples but none seem to work.

Thanks so much. Chandra

Daniel Möller Daniel Möller · Accepted Answer · 2015-06-08T03:17:15

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

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

1 Answers