google sheets - Auto border to a Dynamic Data - Stack Overflow

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

The data :

Data Sample :

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?

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

The data :

Data Sample : https://docs.google/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?

• google-sheets
• google-apps-script

Share Improve this question Follow edited Nov 23, 2024 at 14:15 Wicket 38.5k99 gold badges7878 silver badges193193 bronze badges asked Nov 20, 2024 at 7:00 Tanti HuangTanti Huang 355 bronze badges 2

• 1 Please do NOT share data by way of images. In most cases, users need to use your data to find an answer; if the data is presented as an image then there is a disincentive to work on your problem. Please supply sample data as markdown tables AND a spreadsheet if appropriate. – Tedinoz Commented Nov 20, 2024 at 7:14
• @Tedinoz The image's purpose is to show the sheet layout rather than share sample data, so in this case, including an image is acceptable. – Wicket Commented Nov 23, 2024 at 14:17

Add a comment  | 

4 Answers 4

Sorted by: Reset to default 0

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:

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.

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

If data has been updated while document was closed. You could use a different trigger such as onOpen, or just update it with specific interval.

Info on those triggers

You want to query/filter data based on a "Month" dropdown, and then set borders around the entire data output.

Consider this answer:

Logic

• The user select a date from the dropdown cell (cell C4)
• A QUERY populates the output based on the dropdown date in cell C4.
• The QUERY tests whether the transaction date is between the first and the last date of the relevant month
  • Col1 >= date '"&TEXT(C4,"yyyy-mm-dd")&"' and Col1 <= date '"&TEXT(eomonth(C4,0),"yyyy-mm-dd")&"'
• An onEdit script clears any existing borders
  • sheet.getRange(firstRowOfData,startCol,rows-firstRowOfData+1,5).setBorder(null, false, false, false, false, false)
• Identifies the output range
  • var targetRange = sheet.getRange(firstRowOfData,startCol,lastRow-firstRowOfData+1,5)
• set borders for the output range:
  • targetRange.setBorder(true, true, true, true, true, true);

---

SCRIPT

function onEdit(e) {
  Logger.log(JSON.stringify(e)) // DEBUG
  const sheetName = "answer"; // This is from your provided Spreadsheet.
  const expectedRange = "C4"; // This is from your question.
  const { range } = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != sheetName) return;
  const r = sheet.getRange(expectedRange);
  // define the parameters
  const startRow = 4
  const startCol = 3
  var firstRowOfData = 6
  Logger.log("DEBUG: rowStart = "+range.rowStart+"startRow = "+startRow)
  Logger.log("DEBUG: Column Start = "+range.columnStart+"\nstartCol = "+startCol)
  if (range.rowStart !== startRow || range.columnStart !== startCol) {
    // Logger.log("DEBUG: IF failed")
    return;
  }

  // get the last row of data
  var lastRow = sheet.getLastRow()
  // get the maxmumum number of rows
  var rows = sheet.getMaxRows()
  Logger.log("DEBUG: the last row of data = "+lastRow+"\nthe number of rows = "+rows)
  
  // clear any existing borders
  sheet.getRange(firstRowOfData,startCol,rows-firstRowOfData+1,5).setBorder(null, false, false, false, false, false);
  
  // create new border
  var targetRange = sheet.getRange(firstRowOfData,startCol,lastRow-firstRowOfData+1,5)
  Logger.log("DEBUG: the target range = "+targetRange.getA1Notation())
  targetRange.setBorder(true, true, true, true, true, true);
}

---

QUERY

Enter this formula in cell C6 (assuming the raw data is on sheet name = "Sheet1":

=query(
  {Sheet1!A1:E},
  "select Col1,Col2,Col3, Col4, Col5 
    where Col1 is not null and 
    Col1 >= date '"&TEXT(C4,"yyyy-mm-dd")&"' and 
    Col1 <= date '"&TEXT(eomonth(C4,0),"yyyy-mm-dd")&"' ",1
)

---

Data Validation (dropdowns)

• Create a Data Validation Rule for Cell C4 of sheet="answer"
  • Format the cell as "Month(full name) space Year (4 digits)"
• Create a Dropdown (from a range)
• Enter dates in the data range being the first date of the respective months (e.g. 1/9/2024, 1/10/2024)and format them the same as the dropdown cell ("Month(full name) space Year (4 digits)")

---

SAMPLE - Raw data

---

SAMPLE - September Results

---

SAMPLE - October results

---

Here's another solution. It's a little known fact that Google Sheets supports conditional formatting rules for borders when those rules have been imported from an Excel file.

I took your sample spreadsheet and exported it as an Excel file. Once opened in Excel, I created the following three CF rules.

First row: For C10:G10, I applied the solid top, left, and right border along with the dotted line for the bottom using custom formula:

=COUNTA($C10:$G10)>0.

Mid rows: For C11:G100, I used the solid left and right borders plus the dotted top and bottom border using custom formula:

=COUNTA($C11:$G11)>0.

Bottom row: For the same range, I used the custom formula:

=(COUNTA($C11:$G11)=0)*(COUNTA($C10:$G10)>0)>0

This identified the bottom row, for which I made the top border dotted and the left, right, and bottom borders solid.

Once those were created, I downloaded the .xlsx file and imported it into Sheets.

The result, instant dynamic borders without the use of Apps Script.