title: Copying Data Sheet1 to Sheet2 so you can sort & edit both sheets (google apps script?)
body: <p>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.</p>
  
  <p>1) I have a sheet “Participant_Registration” where basic information is entered</p>
  
  <p>2) I want to transfer information from only the first four columns (A:D) of “Participant_Registration” to a second sheet “Learning_Sessions_Attendance”</p>
  
  <p>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.</p>
  
  <p>Here is a link to a copy of my spreadsheet.
  <a href="https://docs.google.com/spreadsheets/d/17d0bT4LZOx5cyjSUHPRFgEZTz4y1yEL_tO3gtSJ4UJ8/edit?usp=sharing" rel="nofollow">https://docs.google.com/spreadsheets/d/17d0bT4LZOx5cyjSUHPRFgEZTz4y1yEL_tO3gtSJ4UJ8/edit?usp=sharing</a></p>
  
  <p>More generically this is what I am trying to do. Is this possible in google app scripts? It seems it should be.</p>
  
  <p>1)  I have original data in sheet1</p>
  
  <p>2)  I want the first four columns (A:D) to transfer to sheet2 (it is fine if I need a trigger variable)</p>
  
  <p>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).</p>
  
  <p>4)  Ideally if there is a change to the data in the source sheet (Sheet1) the same change will be made in Sheet2.</p>
  
  <p>5)  Ideally this would all happen automatically without human intervention through a script.</p>
  
  <p>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.</p>
  
  <p>Here are my sample scripts each with a problem:</p>
  
  <p>//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</p>
  
  <pre><code>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);
  }
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>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
  }
  </code></pre>
  
  <p>So I tried again and again. I have lots of examples but none seem to work.</p>
  
  <p>Thanks so much.
  Chandra</p>