CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Teddy Jackson
Teddy Jackson

Reputation: 11

The Google Sheets script I'm using is copying a named Range, but to the wrong column

I'm using a modified Google Sheets script I found on here to keep a named range synced across two tabs of a document. However, when an edit happens, it just copies the named range ("Attendance") from Sheet2 to the first column of Sheet1, as opposed to the same position in Sheet1. (Basically, the range it's copying from in Sheet2 is Column I, but it copies into Column A in Sheet1, overwriting the data there.)

What can I add so it goes to the right place?

var sourceSpreadsheetID = "1SrT3N_DI1vLeh-ACisZgREA5gMPjAWQu8edQtWXC3lM";
var sourceWorksheetName = "Sheet2";
var targetSpreadsheetID = "1SrT3N_DI1vLeh-ACisZgREA5gMPjAWQu8edQtWXC3lM";
var targetWorksheetName = "Sheet1";

function onEdit(e) {
var thisSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
var thisWorksheet = thisSpreadsheet.getSheetByName(sourceWorksheetName);
var thisData = thisSpreadsheet.getRangeByName("Attendance");


var toSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetID);
var toWorksheet = toSpreadsheet.getSheetByName(targetWorksheetName);
var toRange = toWorksheet.getRange(1, 1, thisData.getNumRows(), thisData.getNumColumns())
toRange.setValues(thisData.getValues()); 
}

Upvotes: 0

Views: 206

Answers (3)

Iamblichus
Iamblichus

Reputation: 19339

Use getA1Notation() to retrieve the A1 notation of the named range, and use it for your target range.

So, replace this:

var toRange = toWorksheet.getRange(1, 1, thisData.getNumRows(), thisData.getNumColumns());

With this:

var toRange = toWorksheet.getRange(thisData.getA1Notation());

Notes:

  • If you want to copy the range (formatting included) and not just the values, and if both ranges are on the same spreadsheet, use copyTo(destination) instead of setValues.
  • If the spreadsheet/s you are working is not the container of your script, this script might need authorization, in which case I'd suggest you to install the onEdit trigger.

Upvotes: 1

Harsh
Harsh

Reputation: 241

This is because your getRange is set to Row 1, Column 1 hence when you change it to desired row and column you will get the data placed in specified row and column

var toRange = toWorksheet.getRange(1, 9, thisData.getNumRows(), thisData.getNumColumns())

Hope this solves your issue.

Upvotes: 0

Cooper
Cooper

Reputation: 64110

const vA=thisData.getValues();
var toRange = toWorksheet.getRange(1, 2, vA.length, vA[0].length);

Upvotes: 0

Related Questions