CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Solana_Station
Solana_Station

Reputation: 321

How to add and remove borders in Google Apps Script

Goal:

  1. Add borders to a range of cells depending on if a cell in H column of the following table is not null. The table has names and total ticket numbers, which will appear or disappear automatically depending on certain dates of the data that I'm pulling from with the QUERY function. And I want to be able to add borders to the range of cells where the names appear in the cells of column H.

For example, let's say for the first time, in cell H3, a name called "John A" appears. In this case, I want to be able to add borders to H3:I3. And if "John B" appears below "John A," then I want to add another border to H4:I4. And so on.

enter image description here

  1. Remove borders depending on if the value in cells of column H have been removed.

For example, if "John B" was automatically removed for cell H4, then I want to also remove the borders for H4:I4. And if "John A" was also removed from cell H3, then I want to also remove the borders for H3:I3.

Current Code: I'm trying to pass parameters from the onEdit() function that's located in another file to the insertDynamicBorders() function as I thought that this would be event triggering matter. 

function insertDynamicBorders(row) {
  var statusSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Status");

  // Columns
  dateColumn = 1;

  if (row > 16) {
    if (statusSheet.getRange("H3") != null ) {
      var cell = statusSheet.getRange("H3:I3");
      cell.setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
    } else {
      var cell = statusSheet.getRange("H3:I3");
      cell.setBorder(false, false, false, false, false, false, "black", SpreadsheetApp.BorderStyle.SOLID);
    }
    if (statusSheet.getRange("H4") != null) {
      var cell = statusSheet.getRange("H4:I4");
      cell.setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
    } else {
      var cell = statusSheet.getRange("H4:I4");
      cell.setBorder(false, false, false, false, false, false, "black", SpreadsheetApp.BorderStyle.SOLID);
    }
    if (statusSheet.getRange("H5") != null) {
      var cell = statusSheet.getRange("H5:I5");
      cell.setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
    } else {
      var cell = statusSheet.getRange("H5:I5");
      cell.setBorder(false, false, false, false, false, false, "black", SpreadsheetApp.BorderStyle.SOLID);
    }
    if (statusSheet.getRange("H6") != null) {
      var cell = statusSheet.getRange("H6:I6");
      cell.setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
    } else {
      var cell = statusSheet.getRange("H6:I6");
      cell.setBorder(false, false, false, false, false, false, "black", SpreadsheetApp.BorderStyle.SOLID);
    }
    if (statusSheet.getRange("H7") != null) {
      var cell = statusSheet.getRange("H7:I7");
      cell.setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
    } else {
      var cell = statusSheet.getRange("H7:I7");
      cell.setBorder(false, false, false, false, false, false, "black", SpreadsheetApp.BorderStyle.SOLID);
    }
  }
}

Issue:

  1. The border doesn't add whenever a name appears in a cell of column H, however will add borders when I make direct changes to the data source that I'm pulling from using the QUERY function.
  2. When borders do get added, they are added to cell ranges where the names are included and also not included in the cells of column H.

Upvotes: 0

Views: 5274

Answers (1)

ale13
ale13

Reputation: 6062

When using Apps Script triggers, more particularly the onEdit, it is important to note the following:

onEdit(e) runs when a user changes a value in a spreadsheet.

Script executions and API requests do not cause triggers to run. For example, calling Range.setValue() to edit a cell does not cause the spreadsheet's onEdit trigger to run.

Taking the above into account then, what you want cannot be directly achieved.

Workaround

What you can do in this situation is to make use of the installable triggers and create a time driven trigger. You can create a script which will check for any changes which occur in your spreadsheet compared to the previous version. Since this will have to be done programmatically by you, it is a more cumbersome bypass.

Another option (making use of the time driven trigger still) is to check which is the last row in the columns H and I (as per your example) and also considering the fact that only the wanted values get inserted into these columns.

Reference

Upvotes: 1

Related Questions