google-sheetsgoogle-apps-script

Auto border to a Dynamic Data

I have a range of dynamic data that shows data from a dropdown list.

The data :

October Data

September Data

Data Sample : https://docs.google.com/spreadsheets/d/1NGsqFGFt_hDHUT6LscKTKqh5hxQVU1DJ_Ixbuuvrkdc/edit?usp=sharing

I tried using script from this post.


function onEdit(e) {
  const sheetName = "MONTHLY OVERVIEW";
  const expectedRange = "C10:G1000";

  const { range } = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != sheetName) return;
  const r = sheet.getRange(expectedRange);
  const startRow = r.getRow();
  const endRow = startRow + r.getNumRows() - 1;
  const startCol = r.getColumn();
  const endCol = startCol + r.getNumColumns() - 1;
  if (range.rowStart < startRow || range.rowEnd > endRow || range.columnStart < startCol || range.columnEnd > endCol) return;
  r.setBorder(null, false, false, false, false, false);
  const last = r.getDisplayValues().findIndex(rr => !rr.join(""));
  sheet.getRange(startRow, startCol, last != -1 ? last : r.getNumRows(), endCol - startCol + 1).setBorder(true, true, true, true, true, false);
}

But the border will only appear when I edit the cell below the last dynamic data.

Is there any way to make the border automatically added according to the dynamic data, with an extra empty row before the border on the bottom, like shown on the image above?

Solution

  • Automatically Adding Borders with an Extra Empty Row for Dynamic Data in Google Sheets

    Instead of using onEdit, use onChange with installable triggers. The onEdit trigger does not detect changes made through dropdown lists unless you manually edit the data.

    Updated Code:

    function handleEdit() {
  const sheetName = "MONTHLY OVERVIEW";
  const expectedRange = "C10:G1000";

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (!sheet) return;

  const r = sheet.getRange(expectedRange);
  const startRow = r.getRow();
  const endRow = startRow + r.getNumRows() - 1;
  const startCol = r.getColumn();
  const endCol = startCol + r.getNumColumns() - 1;
  const range = sheet.getActiveRange();

  if (range.rowStart < startRow || range.rowEnd > endRow || range.columnStart < startCol || range.columnEnd > endCol) return;

  r.setBorder(false, false, false, false, false, false);

  // Find the last non-empty row
  const values = r.getDisplayValues();
  let lastRow = 0;
  for (let i = 0; i < values.length; i++) {
    if (values[i].some(cell => cell.trim() !== "")) {
      lastRow = i + 1;
    }
  }

  // Add borders around dynamic data and an extra empty row below
  if (lastRow > 0) {
    const borderRange = sheet.getRange(startRow, startCol, lastRow + 1, endCol - startCol + 1);
    borderRange.setBorder(true, true, true, true, true, false);
  }
}

    Sample Output:

    September

    October Output

    November

    To set up the Installable trigger:

    1.) Open your Apps Script project.

    2.) On the left, click Triggers alarm.

    3.) At the bottom right, click Add Trigger.

    4.) Select the On Change.

    onChange

    5.) Click Save.

    Once set up, the onChange trigger will automatically respond to changes in the spreadsheet, including updates via dropdowns or formulas.

    Reference:

    Event-driven triggers

    Installable Triggers