CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
user198561
user198561

Reputation: 49

Copy values from 1 sheet and set values to last row of another sheet

I know this has been done before, but I'm struggling to get mine working, I want to copy all data from sheet 'Templates' to 'History' sheet.

I want to send the values in a new row at the bottom of history sheet.

Here is my working code, I just need to change where the values are set.

var sheetTemplate = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Template');
var sheetHistory = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('History');

var getRange = sheetTemplate.getDataRange();
var DataCopied = SRange.getValues();

sheetHistory.getRange("a2:F7").setValues(DataCopied);

Upvotes: 0

Views: 1403

Answers (1)

user3075569
user3075569

Reputation:

You're using the getDataRange() and getValues() methods, this will return the corresponding values in which data is present in a two-dimensional array, so when you copy the values to the new sheet with setValues() you need to take into account the dimensions of the data retrieved so they match, you need to:

  1. Get the last available row of the History Sheet with getLastRow()
  2. Get the "height" of the DataCopied Array (number of rows) with DataCopied.length
  3. Get the "width" of the DataCopied Array (number of columns) DataCopied[0].length.
  4. Get the range of that dimensions with getRange(row, column, numRows, numColumns).
  5. Set the values of the DataCopied with setValues()

Your code should look like this:

function myFunction(){
  var sheetTemplate = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Template');
  var sheetHistory = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('History');

  var getRange = sheetTemplate.getDataRange();
  var DataCopied = getRange.getValues();

  // I defined the variables for better understanding      
  var startRow = sheetHistory.getLastRow()+1; // +1 because remember that while a range index starts at 1, 1, the JavaScript array will be indexed from [0][0].
  var startColumn = 1;
  var numRows = DataCopied.length;
  var numColumns = DataCopied[0].length;
  sheetHistory.getRange(startRow, startColumn, numRows, numColumns).setValues(DataCopied);

}

Upvotes: 2

Related Questions