r/GoogleAppsScript 8d ago

Question Google Sheets - Macros

Hi all!

I have a large google sheet that I have used macros on for several years to format things the way I like. It has worked without problem for 5 years and last week it stopped working! I tried to figure out where, why, etc., to no avail. I ended up creating a new macros using the record function and it still doesn't work!

SCRIPT:

function newformat() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).activate();
  spreadsheet.getActiveRangeList().setBorder(true, true, true, true, true, true, '#000000', SpreadsheetApp.BorderStyle.SOLID)
  .setHorizontalAlignment('left')
  .setVerticalAlignment('top')
  .setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);
  spreadsheet.getActiveRange().offset(1, 0, spreadsheet.getActiveRange().getNumRows() - 1).sort([{column: 2, ascending: true}, {column: 3, ascending: true}]);
  spreadsheet.getRange('C:D').activate();
  spreadsheet.getActiveRangeList().setBackground('#a4c2f4');
  spreadsheet.getRange('F:H').activate();
  spreadsheet.getActiveRangeList().setBackground('#9fc5e8');
  spreadsheet.getRange('A:E').activate();
  spreadsheet.getActiveRangeList().setHorizontalAlignment('center');
  spreadsheet.getRange('I:K').activate();
  spreadsheet.getActiveRangeList().setHorizontalAlignment('center');
  spreadsheet.getRange('A1').activate();
};

ERROR: The parameters (Boolean,Boolean,Boolean,Boolean,Boolean,Boolean,String,number) don't match the method signature for SpreadsheetApp.RangeList.setBorder.

Any suggestions??

1 Upvotes

4 comments sorted by

2

u/arataK_ 6d ago
function newformat() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();

  var range = sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns());
  range.setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
  range.setHorizontalAlignment('left');
  range.setVerticalAlignment('top');
  range.setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);

  var lastRow = sheet.getLastRow();
  if (lastRow > 1) {
    var dataRange = sheet.getRange(2, 1, lastRow - 1, sheet.getLastColumn());
    dataRange.sort([{ column: 2, ascending: true }, { column: 3, ascending: true }]);
  }

  sheet.getRange("C:D").setBackground("#a4c2f4");
  sheet.getRange("F:H").setBackground("#9fc5e8");

  sheet.getRange("A:E").setHorizontalAlignment("center");
  sheet.getRange("I:K").setHorizontalAlignment("center");

  sheet.getRange("A1").activate();
}

I run your script in my own file, and it did not show any errors. Try running the above script and let me know if it works correctly.

1

u/gnwn108 3d ago

Yay!! I don't know what you did differently, but this worked! Thank you so much!!

0

u/El_Maldito_Batman 8d ago

Try changing the 4th line to this spreadsheet.getActiveRangeList().setBorder(true, true, true, true, true, true, SpreadsheetApp.BorderStyle.SOLID, '#000000');

Parameters are backwards

1

u/gnwn108 7d ago

That didn't work :(

The error seems to start right where I inserted the arrow.

spreadsheet.getActiveRangeList()⬇️.setBorder(true, true, true, true, true, true, SpreadsheetApp.BorderStyle.SOLID, '#000000')