CODEWITHSUNDEEP
javascriptgoogle-sheets
Javier JIMENEZ
Javier JIMENEZ

Reputation: 21

Count colour cells-Google Sheets

I am trying to make a GANT diagram in google sheets, I will show the progress by colouring the cells, so if I have 4 cells in the diagram of GANT and I paint 2 of them I would like to show the percentage of 50%.

I am trying to make that with google script, counting the cells but I am not able, and I can´t use Power tools.

Any advice?

// Show the progress of the station

function progress_bar() 
{
   
  var app= SpreadsheetApp;
  var activeSheet = app.getActiveSpreadsheet().getActiveSheet(); // Returns the active shee
// var activeSheetName=activeSheet.getName();
  var porcentaje ;
  
// Si la hoja activa es Rebarbado entonces 
 if (activeSheet.getRange("E5:H5").getBackground()=="#ffff00" || activeSheet.getRange("E5:H5").getBackground()=="#4a86e8" ||  activeSheet.getRange("E5:H5").getBackground()=="#000000")
 {
    porcentaje=8.3;
    activeSheet.getRange("R11:AD15").getBackground();
    if ( activeSheet.getRange("R11:AD15").getBackground()=="#ffff00" ||   activeSheet.getRange("R11:AD15").getBackground()== "#4a86e8" || activeSheet.getRange("R11:AD15").getBackground()=="#000000")
    {
      porcentaje= porcentaje+8.3 ;
      activeSheet.getRange("AE17:AQ21").getBackground();
      if ( activeSheet.getRange("AE17:AQ21").getBackground()=="#ffff00" ||   activeSheet.getRange("AE17:AQ21").getBackground()== "#4a86e8" || activeSheet.getRange("AE17:AQ21").getBackground()=="#000000")
      {
        porcentaje= 2*porcentaje+8.3 
        activeSheet.getRange("AR23:BD27").getBackground();
        if ( activeSheet.getRange("AE17:AQ21").getBackground()=="#ffff00" ||   activeSheet.getRange("AE17:AQ21").getBackground()== "#4a86e8" || activeSheet.getRange("AE17:AQ21").getBackground()=="#000000")
        {
          porcentaje= 3*porcentaje+8.3 
          SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Progeso_AV").getRange("B6").setValue(porcentaje);
        }
      }
    }
 }
  
}

Regards Javier

Upvotes: 0

Views: 78

Answers (1)

Oleg_S
Oleg_S

Reputation: 161

See testSheet

As one option not using script is to use Conditional formatting.

For example, set fill and text color to green when you enter 1 or any other specified value. Then just count those value and get progress percentage.

=COUNTA(B2:E2)/(COLUMN(E2)-COLUMN(B2)+1)

where B is your start and E - ending column.

Upvotes: 1

Related Questions