Step 1: Setting up your Google Sheet

Begin by creating a new Google Sheet. Label the first row with the following headers:

  1. A1: Date
  2. B1: Item
  3. C1: Amount
  4. D1: Due Date
  5. E1: Status
  6. F1: All (Hosting) - {SUM}
  7. G1: All (Dev) - {SUM}

The 'Date' (A1) column represents the bill's issue date. 'Item' (B1) refers to the service provided, which could be 'Hosting' or 'Dev'. 'Amount' (C1) indicates the bill's value in your chosen currency. 'Due Date' (D1) is when the bill must be paid by, and 'Status' (E1) will show whether the bill has been paid.

F1 and G1 will contain dynamic sums of all paid bills for Hosting and Development services respectively. We will detail how to set this up later in the guide.

Step 2: Data Validation

We'll apply data validation to the 'Item' and 'Status' columns as follows:

  1. 'Date' and 'Due Date' Columns (A & D): Click on column A, then hold the Ctrl key and click on cell A1 to deselect it. This will select all cells in the 'Date' column, excluding the header. Repeat this process for the 'Due Date' column (D). Go to Data > Data Validation > Add rule. Under 'Criteria', select  'Is valid date'.
  2. 'Item' Column (B): Click on column B, then Ctrl+click on cell B1 to deselect it. This should select all cells in the 'Item' column excluding the header. Then, go to Data > Data Validation > Add rule, under 'Criteria', select 'Drop-down' and enter items 'Hosting', 'Dev'.
  3. 'Amount' Column (C): Select the C column, then Ctrl+click on C1 to deselect it. Go to Data > Data Validation > Add rule, select 'Custom formula is' and enter =ISNUMBER(C2) in the field below. This will validate that the entered value is a number.
  4. 'Status' Column (E): Click on column E, then Ctrl+click on cell E1 to deselect it. Go back to Data > Data Validation > Add rule, and under 'Criteria', select 'Tic box'.

Don't forget, you can adjust the 'Apply to range' field in the data validation box to be more precise. For instance, for the 'Item' column, you could enter Sheet1!B2:B1000.

Lastly, we'll add formulas to the F1 and G1 cells:

  1. 'All (Hosting) - {SUM}' (F1): Click on cell F1 and enter the following formula: 
= "All (Hosting) - " & IFERROR(SUMIF(B2:B, "Hosting", C2:C), 0)

This will calculate the sum of all amounts where the Item is 'Hosting'.

  1. 'All (Dev) - {SUM}' (G1): Click on cell G1 and enter this formula:
= "All (Dev) - " & IFERROR(SUMIF(B2:B, "Dev", C2:C), 0)

This will calculate the sum of all amounts where the Item is 'Dev'.

By applying these steps, you ensure that the data validation rules do not affect the header row and that all cells in your sheet are correctly validated and formatted.

Step 3: Automating the Status Checkbox with Google Apps Script

We want the 'Status' checkbox to automatically check itself when a due date and an amount are added, and uncheck when either the due date or amount are removed. To achieve this, we'll use Google Apps Script:

  1. Go to Extensions > Apps Script.
  2. In the Apps Script editor, replace the existing code with the following script:
function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  var column = range.getColumn();
  var row = range.getRow();

  var dateDueColumn = 4;
  var statusColumn = 5;
  var amountColumn = 3; // Assuming "Amount" is the third column

  if(column == dateDueColumn || column == amountColumn) { 
    var dueDate = sheet.getRange(row, dateDueColumn).getValue();
    var amount = sheet.getRange(row, amountColumn).getValue();
    if(dueDate == '' || amount == '' || amount == null) {
      // Uncheck the box if the due date or amount is missing
      sheet.getRange(row, statusColumn).setValue(false);
    } else {
      // Check the box if the due date and amount are both present
      sheet.getRange(row, statusColumn).setValue(true);
    }
  } else if(column == statusColumn) {
    // Reset the checkbox's value to its original state based on the due date and amount
    var dueDate = sheet.getRange(row, dateDueColumn).getValue();
    var amount = sheet.getRange(row, amountColumn).getValue();
    var status = (dueDate == '' || amount == '' || amount == null) ? false : true;
    sheet.getRange(row, statusColumn).setValue(status);
  }
}

Step 4: Formatting the Header Row and Keeping it in View

This step will focus on enhancing the visibility and aesthetics of our spreadsheet. We want the first row, which contains the headers, to be always visible even when we scroll down. Furthermore, we're going to change the background color of this row and make the text bold. Here's how to do it:

  • Freeze the header row: To make the first row always visible even when you scroll down, you need to "freeze" it. To do this, go to the "View" menu in Google Sheets, hover over the "Freeze" option, and then select "1 row". Now the first row will stay at the top of the sheet as you scroll.
  • Change the background color of the header row: Next, we'll change the background color of the header row. Click the number '1' to the left of the first row to select the entire row. Then click on the "Fill color" icon (which looks like a paint bucket) in the toolbar, and choose the color you prefer for the background.
  • Bold the header row: To finish up, let's make the text in the header row bold. With the first row still selected, click on the "Bold" icon (which is represented by a 'B') in the toolbar.

Now your spreadsheet should look even better, and it'll be easier to read and navigate thanks to the clear, distinguishable header row that remains visible at all times.