I´m new to google script and despite trying different codes I cannot get this to work properly.
I need a script that will copy data from range "SkráStarfsmann" to the next empty row in a table below (with the same number of columns) "NStarfsmenn" in the same sheet when a person clicks the button I drew.
The current code I have been trying to get to work for me is from this question How to copy a range of selected data to after the lastRow of data in the same Sheet using Google Apps Script?
function CopyData() {
var sheet = SpreadsheetApp.getActiveSheet();
var listToCopy = sheet.getRange("SkráStarfsmann");
var destRow = sheet.getLastRow() + 1;
listToCopy.copyTo(sheet.getRange("NStarfsmenn1"));
listToCopy.clearContent();
}
This is how the columns look in both ranges, the first column "DEILD" is in column B not A.
DEILD | STARF | KENITALA | NAFN | TEGUND RÁÐNINGAR | BYRJAR | Mánuður | SAKASKRÁ - STAÐA | RÁÐNINGARSAMNINGUR - STAÐA | SAKASKRÁ - STAÐA | ÖNNUR | FASTIR | VEF |
I tried to modify various codes from similar questions on this website but none of them worked properly, the current code I have makes it so when the button is clicked the data from range "SkráStarfsmann" is copied to the ENTIRE table, as in all of the rows which is not what I want at all. I think the problem lies in the get.Lastrow line but not sure how to fix it.
I've also gotten an error when I tried to just use the name of the table so instead, I made the rows a range and named it "Nstarfsmenn1".
The other code I tried that semi worked pasted the values starting from column a> but my table starts in column b>.
try:
function CopyData() {
var sheet = SpreadsheetApp.getActiveSheet();
// Define the range to copy from "SkráStarfsmann"
var listToCopy = sheet.getRange("SkráStarfsmann");
// Get the number of rows and columns in the range to copy
var numRows = listToCopy.getNumRows();
var numColumns = listToCopy.getNumColumns();
// Define the range "NStarfsmenn"
var nStarfsmennRange = sheet.getRange("NStarfsmenn");
// Get the values from the range "NStarfsmenn"
var nStarfsmennValues = nStarfsmennRange.getValues();
// Calculate the starting row for the next empty row in "NStarfsmenn"
var nextEmptyRow = nStarfsmennRange.getRow() + nStarfsmennValues.filter(row => row.some(cell => cell)).length;
// Ensure the target range fits within the sheet dimensions
if (nextEmptyRow + numRows - 1 > sheet.getMaxRows()) {
sheet.insertRowsAfter(sheet.getMaxRows(), (nextEmptyRow + numRows - 1) - sheet.getMaxRows());
}
// Copy the data from "SkráStarfsmann" to the next empty row in "NStarfsmenn"
listToCopy.copyTo(sheet.getRange(nextEmptyRow, nStarfsmennRange.getColumn(), numRows, numColumns));
// Clear the content of "SkráStarfsmann"
listToCopy.clearContent();
}