CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
mortpiedra
mortpiedra

Reputation: 81

Creating a range and reassign to other sheet

I build a range that I am trying to reassign from one sheet to another, but it fails with an error.

function test() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourceSheet = ss.getSheetByName('Sheet1');
  var destinationSheet = ss.getSheetByName('Sheet2');

  thisWeek = 37;
  var firstWeek = [];
  var unprotectRange = [];
  firstWeek[0] = sourceSheet.getRange('E6:E12');
  firstWeek[1] = sourceSheet.getRange('F8:F10');

  const columnsPerWeek = 51;

  for (var k = thisWeek - 1; k < 53; k++) {
    for (var l = 0; l < firstWeek.length; l++) {
      if (
        sourceSheet
          .getRange(firstWeek[l].offset(0, columnsPerWeek * k).getA1Notation())
          .isBlank()
      ) {
        unprotectRange.push(
          sourceSheet.getRange(
            firstWeek[l].offset(0, columnsPerWeek * k).getA1Notation()
          )
        );
      }
    }
  }

  var range2 = destinationSheet.getRange(unprotectRange.getA1Notation());
  var protection = destinationSheet.protect().setDescription('Maintain');
  protection.setUnprotectedRanges(range2);
}

Now I get the error: 'TypeError: unprotectRange.getA1Notation is not a function' at the statement

var range2 = destinationSheet.getRange(unprotectRange.getA1Notation());

Upvotes: 0

Views: 81

Answers (1)

TheMaster
TheMaster

Reputation: 50406

  • unprotectRange is a array of ranges from source sheet. They need to be strings(A1Notations). Push only the A1 notations.

  • Convert the strings(A1notations) to ranges of destination sheet using array.map

  • setProtectedRanges() will then accept this array.

/*...*/
if (
  sourceSheet
    .getRange(firstWeek[l].offset(0, columnsPerWeek * k).getA1Notation())
    .isBlank()
) {
  unprotectRange.push(
    firstWeek[l].offset(0, columnsPerWeek * k).getA1Notation()/*Modified*/
  );
}
/*...*/
const ranges2 = unprotectRange.map(a1Notation => destinationSheet.getRange(a1Notation));
const protection = destinationSheet.protect().setDescription('Maintain');
protection.setUnprotectedRanges(ranges2);
protection.removeEditors(protection.getEditors())

Upvotes: 1

Related Questions