Reputation: 99
Sure I had this down but just can't get it right. Had a script set up to get data from one sheet and put it into another one, which I have done but it leaves gaps when copying and I can't figure out how to solve it. I'm sure in the code, its where I have put a question mark, is where the problem lies.
I have tried put i
, last
, last+1
, 10
, 12
but none of these work, feel like I'm missing something small to get this right. Below is the code a link to view the sheet if needed (the sheet is just for me to learn from, a basic example if you will).
Thanks in advance and also if the code could be better written, please just let me know as still learning this :)
function copyInfo() {
var app = SpreadsheetApp;
var copySheet = app.getActiveSpreadsheet().getSheetByName("Copy");
for (var i = 2; i <12; i++) {
var getInfo = copySheet.getRange(2,2,i,2).getValues();
// get the info from range above - start at row 2 on column 2 (b), get number of rows i , number of columns = 2, b,c
var last = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Paste").getLastRow();
var pasteSheet = app.getActiveSpreadsheet().getSheetByName("Paste");
// Tell it where you want the info to go to
pasteSheet.getRange(last+1,1,?,2).setValues(getInfo);
var clearIt = copySheet.getRange(2,2,i,2).clearContent();
// this clears the copy range aka getInfo
}}
Upvotes: 2
Views: 47296
Reputation: 19006
From answer https://stackoverflow.com/a/44967382/717267, I created the following version. That is copying dynamically all table data into other sheet.
function copyInfo() {
var response = Browser.msgBox(
'Confirm',
'Copy all data?',
Browser.Buttons.YES_NO
);
if (response === 'yes') {
var ss = SpreadsheetApp.getActiveSpreadsheet();
// Source sheet
var copySheet = ss.getSheetByName("Copy");
// Destination sheet
var pasteSheet = ss.getSheetByName("Paste");
// First data row number
var firstDataRow = 3;
// Get last data row
var lastRow = copySheet.getLastRow();
// Get last column
var lastColumn = copySheet.getLastColumn();
// Get source range
var source = copySheet.getRange(firstDataRow, 1, lastRow - firstDataRow, lastColumn);
// Get destination range
var destination = pasteSheet.getRange(pasteSheet.getLastRow() + 1, 1);
// Add rows to copy data
let maxRows = pasteSheet.getMaxRows();
if ((pasteSheet.getLastRow() + lastRow) >= maxRows) {
pasteSheet.insertRowsAfter(maxRows, lastRow - firstDataRow);
}
// Copy values to destination range
source.copyTo(destination, { contentsOnly: true });
Browser.msgBox(
'Done!',
'Operation finished.',
Browser.Buttons.OK
);
} else {
Browser.msgBox(
'Cancelled!',
'Operation cancelled!',
Browser.Buttons.OK
);
}
}
Upvotes: 0
Reputation:
You can copy whole range at once using copyTo
, so your function could be rewritten as:
function copyInfo() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var copySheet = ss.getSheetByName("Copy");
var pasteSheet = ss.getSheetByName("Paste");
// get source range
var source = copySheet.getRange(2,2,12,2);
// get destination range
var destination = pasteSheet.getRange(pasteSheet.getLastRow()+1,2,12,2);
// copy values to destination range
source.copyTo(destination);
// clear source values
source.clearContent();
}
Upvotes: 9