For example I want to reorder ranges on EDIT B3:J6, B9:J12, B15:J18, B21:J24, B27:J30, B33:J36, B39:J42 and B45:J48 by points in J row.

How can I do that?

Table: https://docs.google.com/spreadsheets/d/1AguSXhSEcsXBMV8ty_MqWdb16zJtzuybXXH3FQBZkWA/edit#gid=1473685733

CodePudding user response：

Sorting through a rangelist

function myfunction( ) {
  const ss = SpreadsheetApp.openById("ssid");
  const sh = ss.getSheetByName('Sheet0');
  const rgl = ss.getRangeList(['B3:J6', 'B9:J12', 'B15:J18', 'B21:J24', 'B27:J30', 'B33:J36', 'B39:J42', 'B45:J48']);
  //const rgl = sh.getRangeList(['B3:J6', 'B9:J12', 'B15:J18', 'B21:J24', 'B27:J30', 'B33:J36', 'B39:J42', 'B45:J48']);//You can also do it on a sheet
  rgl.getRanges().forEach(r => {
    let col = r.getColumn();
    r.sort({column: col,sortAscending:true})
  });
}

CodePudding user response：

I believe your goal is as follows.

• You want to sort the ranges of B3:J6, B9:J12, B15:J18, B21:J24, B27:J30, B33:J36, B39:J42, and B45:J48 by the column "J" in your Spreadsheet.

In this case, how about the following sample script?

Sample script:

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('###SheetName###'); // Please set the sheet name here.

  const a1Notations = ["B3:J6", "B9:J12", "B15:J18", "B21:J24", "B27:J30", "B33:J36", "B39:J42", "B45:J48"];
  const ranges = sheet.getRangeList(a1Notations).getRanges();
  const formatRanges = a1Notations.map(r => r.replace(/^./, "J"));
  sheet.getRangeList(formatRanges).setNumberFormat("0");
  ranges.forEach(r => r.sort({ column: r.getLastColumn(), ascending: true }));
  sheet.getRangeList(formatRanges).setNumberFormat("@");
}

References:

• forEach()
• sort(sortSpecObj)
• setNumberFormat(numberFormat)