Road Bar Charts with Auto Summary

Nowadays, Google Sheets is an important tool for data sharing and collection. Simultaneous editing by multiple people is extremely helpful for teamwork. It works seamlessly on mobile devices and from any location with internet access. Sheets can be linked together, allowing Junior/Section Engineers to enter data while others monitor.

In the context of the Maharashtra Public Works Department:

Junior/Section Engineers' data compiles into 1 Deputy Engineer
Deputy Engineers' data compiles into 1 Executive Engineer
Executive Engineers' data compiles into 1 Superintending Engineer
Superintending Engineers' data compiles into 1 Chief Engineer
All Chief Engineers' data compiles to the Road Secretary

This structure ensures single-point data entry with automatic hierarchical compilation and summaries using linked sheets.

I achieved auto-abstracting using a custom Google Apps Script function (similar to listing worksheets in Excel) combined with the INDIRECT function.

Apps Script Code (paste in Extensions > Apps Script):

function sheetnames() {
  var out = [];
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i = 0; i < sheets.length; i++) {
    out.push([sheets[i].getName()]);
  }
  return out;
}

This function returns all sheet names, enabling dynamic references like: =INDIRECT($B5&"!"&F$1&5)

Road Bar Chart Features

1. Auto Width Change using SPARKLINE
Symmetric bar charts created in two adjacent cells using conditional coloring.

Left cell formula:

=IF(B8="Yes",
  IFERROR(SPARKLINE(G8:H8, {
    "charttype","bar";
    "max",$J$3/2;
    "color1","green";
    "color2",IF(A8=DATA!$D$2,"Gray","Black")
  })),
  IFERROR(SPARKLINE(G8:H8, {
    "charttype","bar";
    "max",$J$3/2;
    "color1","white";
    "color2",IF(A8=DATA!$D$2,"Gray","Black")
  }))
)

Right cell formula:

=IF(B8="Yes",
  IFERROR(SPARKLINE(H8:I8, {
    "charttype","bar";
    "max",$J$3/2;
    "color1",IF(A8=DATA!$D$2,"Gray","Black");
    "color2","green"
  })),
  IFERROR(SPARKLINE(H8:I8, {
    "charttype","bar";
    "max",$J$3/2;
    "color1",IF(A8=DATA!$D$2,"Gray","Black");
    "color2","white"
  }))
)

2. Auto Chainage with Custom Interval
Formula: =IF(C7<$B$3, IF(C7>=$A$3, C7+$C$4, ""), "")

3. Forest Length Highlighting – Green bars indicate forest sections.