google-sheetsgoogle-apps-scriptscriptingarchive

Copy data from x range to a table in the same sheet


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>.


Solution

  • 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();
    }