Google Apps Script - Copy data to different worksheet and append

3
votes

I have looked at all the previous questions I could find, but I can never seem to make the different examples fit what I'm trying to do.

I have forms that will be filled out by different users. I want the data on the "Data" sheet to be copied to an identically formatted separate spreadsheet upon choosing "Yes" in the dropdown in All!H35. There will be a form ("Source") for each team that will be filled out daily and ideally they all copy data to the next empty row(s) in the same master spreadsheet ("Destination"), creating a running list of all the work.

[Source][1]

[Destination][2]

Edit: It's partially working, but only grabbing the first cell, even after adjusting for the remaining columns and rows.

// Get the contents of a cell in srcSheet
  var range = srcSheet.getRange(1, 1, 16, 38);
  var values = range.getValues();

  // place it in the last row if the destination cell
  var range = srcSheet.getRange(1, 1, 16, 38);
  var values = range.getValues();

Finally got it to work. Here's the working code for reference.

function CopyRange() {
 var sss = SpreadsheetApp.openById('1zxyKx4GsBgD7hRzWT3TSvJto-R-rJoNwLu-0s5zFtko'); //replace with source ID
 var ss = sss.getSheetByName('DataTS1'); //replace with source Sheet tab name
 var range = ss.getRange('A2:AL17'); //assign the range you want to copy
 var data = range.getValues();

 var tss = SpreadsheetApp.openById('1DrCgrLIrybN4a-UqlBsGSJy5p8XBPWNR2ftviwTA5_Y'); //replace with destination ID
 var ts = tss.getSheetByName('All Data'); //replace with destination Sheet tab name

 ts.getRange(ts.getLastRow()+1, 1,16,38).setValues(data); //you will need to define the size of the copied data see getRange()

}
2
google-apps-scriptgoogle-sheets
Take a look at MVCE – clinomaniac

2 Answers

3
votes

If you want to know how to copy data from one sheet to another sheet that belongs to different spreadsheet, then you can do the following:

function myFunction() {

  // source spreadsheets
  var sourceSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var srcSheet = sourceSpreadSheet.getSheets()[0];

  //destination spreadsheet
  var destinationSpreadSheet = SpreadsheetApp.openById('SPREADSHEET-ID');
  var destSheet = destinationSpreadSheet.getSheets()[0]; 

  // Get the contents of a cell in srcSheet
  var range = srcSheet.getRange(1, 1);
  var values = range.getValues();

  // place it in the last row if the destination cell
  var range = srcSheet.getRange(1, 1);
  var values = range.getValues();
  // get the last row
  var lastRow = destSheet.getLastRow();
  destSheet.getRange(lastRow + 1,1).setValue(values);

}

Please note that you can copy a range rather than a single cell. Please look at the following links for more info:

https://developers.google.com/apps-script/reference/spreadsheet/range

Note: I noticed that you source and destination spreadsheets are not "identically formatted", but in your question you said they are identical

2
votes

hay there thanks for your code indeed, i do some improvement base on your code, after got stuck using "copyTo"

function copyPaste() {
  var ss = SpreadsheetApp.openById("xxxx");
  var source = ss.getSheetByName("sheet1");
  var rangeSource = source.getDataRange();
  var data = rangeSource.getValues();
  var lr = rangeSource.getLastRow();
  var lc = rangeSource.getLastColumn();
  Logger.log(data);


  var sss = SpreadsheetApp.openById("xxxxxx");
  var target = sss.getSheetByName("sheet2")
  target.getRange(target.getLastRow()+1,1,lr,lc).setValues(data);
  }