Road Bar charts with Auto Summary

Nowadays, Google sheet is an important tool for data sharing, collecting etc. Simultaneous working by Multiple people at a time is quite helpful for teamwork. one can work from mobile devices also. Work from any location where internet access is available can be done. The google sheets can be connected to each other , in this way only Junior/Section Engineer need to enter the data, rest can just monitor.

in case of Maharashtra Public Works Department

    Junior/Section Engineers data can be compiled with 1 Deputy Engineer

    Deputy Engineers data can be compiled with 1 Executive Engineer

    Executive Engineers data can be compiled with 1 Superintending Engineer

    Superintending Engineers data can be compiled with 1 Chief Engineer

All Chief Engineers data can be compiled with Road Secretary

in this way only one time entry will be there, compilation can be made auto by connecting google sheets with each other. Auto abstract can be done from various compiled sheets.

I have achieved auto abstract by using one google script (equivalent to VBA code which can enlist all sheets in a workbook) and Indirect function for further formulas.

Abstract snap is shown as below

code is to be written in "Extension" Tab, then go to "Apps Script" where one can paste following script

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


this sheetnames() function provides names of all sheets,
using indirect function one can get cell values from that sheet
e.g. =indirect($B5&"!"&F$1&5)

I have created Road Bar Chart with following features

1) Auto width change :- Earlier Junior or Section Engineers used to fill the the size reduced Excel Cells to represent some widths such as 3.75m, 5.5m and 7m., in this google sheet i have used Sparkline function to do so in two adjacent cells so as to maintain symmetry.

use of Function as shown below

formula in left cell --> =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")}),))

Formula in Adjacent Right cell -->

=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 User defined Interval:- In this way we can drastically reduce bar chart making time. This is achieved using user inputs such as "Start Chainage", "End Chainage","Interval" and formula --> =if(C7<$B$3,if(C7>=$A$3,C7+$C$4,""),"")

3) Forest Length :- As shown in above formulas Chainages highlighted in Green represents Road chainages which passes through forest length. White border means Road is not passing through forest length. For better understanding check Picture below

4) Road Type :- If the road is Bituminous the road strip appears black and if the road is Concrete Pavement then the road strip appears Gray. The best part is that one can easily get Length of Bituminous and Concrete surfaces easily.  It is summarised in abstract in new version of Google sheet.

5) Bridge Location:- just by entering River Name and spanning arrangement cell gets highlighted.

6) Road condition :- Along specific chainages one can select condition from Dropdown list and each category lengths can be calculated automatically.

7) Dupilcation of works under different chainages can be avoided:- just by entering ongoing/sanctioned works start and end chainages of respective roads sheet, auto abstract can give 

a) Uncovered Lengths under any Work Heads

b) Duplication of Works under different Work Heads on same chainages

How to enter such data is shown in picture below

As soon as start and end chainages are entered, those chainages gets highlighted.

It is easy to understand by Video as below

8) Auto summary as shown above is the most important feature as it summarizes the data and can play important role in data connections through hierarchy.

Further additions Done to get Summaries depending on stages that also headwise such as 03, 04, DLP, FDR, SR, NABARD etc.

1) Auto summary of Road information Sub-Division wise

2) Auto summary of Road information Division wise

3) Auto summary of Road information Sect Engg/AE-2/JE wise

Any corrections or suggestions will be appreciated.

Google sheet link as below

Link for Google Sheet Of Road Bar Chart