Reputation: 61
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()
}
Upvotes: 5
Views: 28139
Reputation: 21
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);
}
Upvotes: 2
Reputation: 676
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
Upvotes: 4