Retain sheet formatting when users insert or add rows-CodePudding

I am looking for a way to retain sheet formatting when users insert or add rows.

Row insertion can be detected:

function onChange(e){
  const sheet = SpreadsheetApp.getActiveSheet(); 
  const range = sheet.getActiveRange();
  const row = range.getRowIndex();
  
  if (row > 5 && e.changeType === 'INSERT_ROW') {
    const sheet = range.getSheet();
    var targetSheet = sheet;
    const sourceRange = targetSheet.getRange(`${5}:${5}`);
    // Get the recently added row
    const targetRange = targetSheet.getActiveRange(); // getRange(`${targetSheet.getLastRow()}:${targetSheet.getLastRow()}`);
    //SpreadsheetApp.getUi().alert(targetRange.getA1Notation());
    // Copy the format only from the previous row to the current row
    sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
  }
}

This is not be the case when pressing the button Add "add more rows at bottom"..... is there a way to trigger the event?

CodePudding user response：

By default inserted rows using the Add button inherit the format of the last row. Once said that, one of the problems with code in the question is that it relies on the active range to control the script flow. This is a problem because the Add button could be clicked no matter of what is the active range, and the inserted rows aren't activated.

Since the change event object doesn't provide information of the change done other than the change type, you should determine somehow to get the sheet maximum number of rows before the change and compare it with the sheet maximum number or rows after the change. One way to do this is by using the Properties Service.

The following code save each sheet maximum rows using the Properties Service. To use it, first run init, then create the on change installable trigger to run respondToOnChange.

const PROPS = PropertiesService.getDocumentProperties();

function init() {
    SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(sheet => {
        setMaxRowsProp(sheet);
    })
}
function setMaxRowsProp(sheet, n) {
    const gid = sheet.getSheetId();
    PROPS.setProperty('MAX_ROWS_FOR_'   gid, n ?? sheet.getMaxRows());
}

function respondToOnChange(e) {
    if (e.changeType === 'INSERT_ROW') {
        const sheet = SpreadsheetApp.getActiveSheet();
        const maxRows = sheet.getMaxRows();
        const gid = sheet.getSheetId();
        const oldMaxRows = parseInt(PROPS.getProperty('MAX_ROWS_FOR_'   gid));
        if (maxRows > oldMaxRows) {
            sheet.getRange(oldMaxRows   ':'   oldMaxRows)
                .copyFormatToRange(
                    sheet, 1, sheet.getMaxColumns(), oldMaxRows   1, maxRows
                );
            setMaxRowsProp(sheet, maxRows);
        }
    }
}