Very new at this and I'm probably doing something very basic wrong but I hope someone can help.

I'm trying to preserve the formatting of a sheet that is open for editing by multiple users. I'm using the onEdit() trigger and using a second sheet (formatSheet) to restore the formatting when a user changes the values.

I'm working through this piecemeal checking that each line works so this is not complete (ie I still need to ensure data validation and conditional formatting is retained, I'm not sure that copyFormatToRange does this). However the copyFormatToRange command does not work, script stops executing on this line.

// Retain Formatting

  var formatSheetName = "Master Format";
  var formatSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(formatSheetName);
  var watchSheetRange = SpreadsheetApp.getActiveSpreadsheet().getActiveRange();
  var formatSheetRange = formatSheet.getRange(watchSheetRange.getA1Notation());

  var firstRow = formatSheetRange.getRow();
  var lastRow = formatSheetRange.getLastRow();
  var firstColumn = formatSheetRange.getColumn();
  var lastColumn = formatSheetRange.getLastColumn();
  var gridID = watchSheetRange.getGridId();

  watchSheetRange.copyFormatToRange(gridID,firstRow,firstColumn,lastRow,lastColumn);

I've checked that watchSheetRange is in fact a Range (it is) that gridID is a gridID (it is), that the row/column variables are integers (they are).

Any help appreciated.

CodePudding user response：

This works

function lfunko() {
  const ss = SpreadsheetApp.getActive();
  const fsh = ss.getSheetByName("Sheet0");
  const wsh = ss.getSheetByName("Sheet1")
  const frg = fsh.getRange("A3:S3");//cols 1 to 19
  let wrg = wsh.getActiveRange();
  let id = wrg.getGridId();
  frg.copyFormatToRange(id, 1, 19, wrg.getRow(), wrg.getRow());
}

I'm using the same columns and whatever the active row and I'm only doing one row. The params for the copyFormatToRange are id, columnStart, columnEnd, rowStart, rowEnd

CodePudding user response：

Have you considered creating a form instead of sharing a spreadsheet for data input? Form responses automatically appear in a separate tab in the spreadsheet in a row-oriented fashion and are thus easy to process with spreadsheet functions such as query() and filter().