Mis scoring sheet

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  var ss = SpreadsheetApp.getActive();

  ui.createMenu('SKM')

      .addItem('Create MIS', 'createMIS')

      .addSeparator()

      .addItem('Assisted ImportRange', 'importRangeFormula')

      .addToUi();

}

function importRangeFormula() {

  var ss = SpreadsheetApp.getActive();

 var ui = SpreadsheetApp.getUi();

 var donorSheet = ui.prompt('URL of the spreadsheet from where \ndata has to be imported', ui.ButtonSet.OK_CANCEL);

 if (donorSheet.getSelectedButton() == ui.Button.OK) {

   var donorSheet = donorSheet.getResponseText();

 }  

  

 var tabName = ui.prompt('Name of the sheet/tab from where \ndata has to be imported', ui.ButtonSet.OK_CANCEL);

 if (tabName.getSelectedButton() == ui.Button.OK) {

   var tabName = tabName.getResponseText();

 }

  

 var rangeName = ui.prompt('Enter the range from where \ndata has to be imported.', ui.ButtonSet.OK_CANCEL);

 if (rangeName.getSelectedButton() == ui.Button.OK) {

   var rangeName = rangeName.getResponseText();

 }

  

  ss.getCurrentCell().setFormula('=importrange("' + donorSheet + '","' + tabName + '!' + rangeName + '")')

  

  

}

function createMIS() {

     var spreadsheet = SpreadsheetApp.getActive();

  var ss = spreadsheet.getActiveSheet()

  

 var ui = SpreadsheetApp.getUi();

  

  var wndpl = ui.prompt('Work not done planned cell.', ui.ButtonSet.OK_CANCEL);

 

  if (wndpl.getSelectedButton() == ui.Button.OK) {

    var wndpl = wndpl.getResponseText();

 } 

  

  var wndac = ui.prompt('Work not done actual cell.', ui.ButtonSet.OK_CANCEL);

 

  if (wndac.getSelectedButton() == ui.Button.OK) {

   var wndac = wndac.getResponseText();

 } 

  

  var wndotpl = ui.prompt('Work not done on time planned cell.', ui.ButtonSet.OK_CANCEL);

 

  if (wndotpl.getSelectedButton() == ui.Button.OK) {

   var wndotpl = wndotpl.getResponseText();

 } 

    var wndotac = ui.prompt('Work not done on time actual cell.', ui.ButtonSet.OK_CANCEL);

 

  if (wndotac.getSelectedButton() == ui.Button.OK) {

   var wndotac = wndotac.getResponseText();

 } 

  

  var dwdpl = ui.prompt('Percentage delay planned cell.', ui.ButtonSet.OK_CANCEL);

 

  if (dwdpl.getSelectedButton() == ui.Button.OK) {

   var dwdpl = dwdpl.getResponseText();

 } 

   

  var dwdac = ui.prompt('Percentage delay actual cell.', ui.ButtonSet.OK_CANCEL);

 

  if (dwdac.getSelectedButton() == ui.Button.OK) {

   var dwdac = dwdac.getResponseText();

 }  

  

   

  var tat = ui.prompt('TAT for percentage delay. In hours/24 format or you can write the number of days. Example (3 for 3 days)', ui.ButtonSet.OK_CANCEL);

 

  if (tat.getSelectedButton() == ui.Button.OK) {

   var tat = tat.getResponseText();

 }  

  

  var fromdate = ui.prompt('Week start date. Must be (H$1)', ui.ButtonSet.OK_CANCEL);

 

  if (fromdate.getSelectedButton() == ui.Button.OK) {

   var fromdate = fromdate.getResponseText();

 } 

  var todate = ui.prompt('Week end date. Must be (I$1)', ui.ButtonSet.OK_CANCEL);

 

  if (todate.getSelectedButton() == ui.Button.OK) {

   var todate = todate.getResponseText();

 } 

    var namedrange = ui.prompt('Named range for Planned, Actual and Delay', ui.ButtonSet.OK_CANCEL);

 

  if (namedrange.getSelectedButton() == ui.Button.OK) {

   var namedrange = namedrange.getResponseText();

 } 

  

      var plcol = ui.prompt('Planned Column', ui.ButtonSet.OK_CANCEL);

 

  if (plcol.getSelectedButton() == ui.Button.OK) {

   var plcol = plcol.getResponseText();

 }

  

      var accol = ui.prompt('Actual Column', ui.ButtonSet.OK_CANCEL);

 

  if (accol.getSelectedButton() == ui.Button.OK) {

   var accol = accol.getResponseText();

 }

  

      var dcol = ui.prompt('Delay Column', ui.ButtonSet.OK_CANCEL);

 

  if (dcol.getSelectedButton() == ui.Button.OK) {

   var dcol = dcol.getResponseText();

 }

  

  ss.getRange(wndpl).setFormula('=query('+namedrange+',"select count('+plcol+') where '+plcol+'>date\'"&'+fromdate+'&"\' and '+plcol+'<date\'"&'+todate+'&"\' label count('+plcol+') \'\'")')

  ss.getRange(wndac).setFormula('=query('+namedrange+',"select count('+accol+') where '+plcol+'>date\'"&'+fromdate+'&"\' and '+plcol+'<date\'"&'+todate+'&"\' and '+accol+'<date\'"&'+todate+'&"\' label count('+accol+') \'\'")')

  ss.getRange(wndotpl).setFormula('=query('+namedrange+',"select count('+accol+') where '+plcol+'>date\'"&'+fromdate+'&"\' and '+plcol+'<date\'"&'+todate+'&"\' and '+accol+'<date\'"&'+todate+'&"\' label count('+accol+') \'\'")')

  ss.getRange(wndotac).setFormula('=QUERY('+namedrange+',"select count('+accol+') where '+accol+'<='+plcol+' and '+plcol+'>date\'"&'+fromdate+'&"\' and '+plcol+'<date\'"&'+todate+'&"\' label count('+accol+') \'\'")')

  ss.getRange(dwdpl).setFormula('=query('+namedrange+',"select count('+accol+') where '+accol+'>'+plcol+' and '+accol+'>date\'"&'+fromdate+'&"\' and '+accol+'<date\'"&'+todate+'&"\' label count('+accol+') \'\'")*'+tat+'')

  ss.getRange(dwdac).setFormula('=QUERY('+namedrange+',"select sum('+dcol+') where '+accol+'>date\'"&'+fromdate+'&"\' and '+accol+'<date\'"&'+todate+'&"\' label sum('+dcol+') \'\'")')

}

/*Created with love by Sanidhay Kumar*/