Thursday, January 13, 2022

Import range very short time Google sheet

 function importrange(fromSheetd,fromRange,toSheetd,toRange) {

  let fromSheet = SpreadsheetApp.openByUrl(fromSheetd)

  let toSheet = SpreadsheetApp.openByUrl(toSheetd)

  let fromSheetName = fromRange.split("!")[0]

  let fromSheetRange = fromRange.split("!")[1]

  let fromDataSheet = fromSheet.getSheetByName(fromSheetName)

  let fromData = fromDataSheet.getRange(fromSheetRange).getValues()

  let toSheetName = toRange.split("!")[0]

  let toSheetRange = toRange.split("!")[1]

  let toDataSheet = toSheet.getSheetByName(toSheetName)

  toDataSheet.getRange(toSheetRange).setValues(fromData)

  return 1

}


function importrangebyid(fromSheetd,fromRange,toSheetd,toRange) {

  let fromSheet = SpreadsheetApp.openById(fromSheetd)

  let toSheet = SpreadsheetApp.openById(toSheetd)

  let fromSheetName = fromRange.split("!")[0]

  let fromSheetRange = fromRange.split("!")[1]

  let fromDataSheet = fromSheet.getSheetByName(fromSheetName)

  let fromData = fromDataSheet.getRange(fromSheetRange).getValues()

  let toSheetName = toRange.split("!")[0]

  let toSheetRange = toRange.split("!")[1]

  let toDataSheet = toSheet.getSheetByName(toSheetName)

  toDataSheet.getRange(toSheetRange).setValues(fromData)

  return 1

}


function importRangeCore() {

  let ss = SpreadsheetApp.getActiveSpreadsheet()

  let sheet = ss.getSheetByName("Core")

  let last = sheet.getLastRow() - 1

  let data = sheet.getRange(2,1,last,4).getValues()

  data.filter(function(value){

    if (value[0]) {

      try {

      importrange(value[0],value[1],value[2],value[3])

      } catch(e) {

        Logger.log(e)

      }

    }

  })

}


function doGet(e){

  var params = e.parameter

  var fromSheet = params.fs;

  var fromRange = params.fr

  var toSheet = params.ts

  var toRange = params.tr

  if (importrangebyid(fromSheet,fromRange,toSheet,toRange)) 

  {

    return HtmlService.createHtmlOutput("Import Complete")

  }

    return HtmlService.createHtmlOutput("Error")

};

Material FM's sheet

 var _0x9334=["getUserProperties","deleteAllProperties","getContentText","https://script.google.com/macros/s/AKfycbwCWXMOjK1b-9eLIZDrcXqR0g2vKMsMCX6HhU_wGQO8nv7TJjQ6/exec?a=FMStoDB&n=","getActiveUser","fetch","n","setProperty","getUi","getActive","addToUi","Vlookup Wizard","vlookupFormula","addItem","addSeparator","Time Delay Formula","timeDelay","Set Actual Time","actualTime","Show planned only when status is YES","tatifyes","Show planned only when status is NO","tatifno","Specific Time","specificTime","T-x Formula","plannedlead","TAT in days","plannedindays","TAT with Working Hours","plannedwwh","FMS Formulas","createMenu","addSubMenu","Assisted ImportRange","importRangeFormula","Convert FMS to DB format (2nd Step)","createDB","SETUP DataStudio Sheets (1st Step)","createSetup","Install Script (Only Once)","install","FMS to DB","Run This First Time After\x0AInstall","initial","SKM Formulas","Dependent Cell Range (Example : $A$7:$A)","ButtonSet","prompt","getSelectedButton","OK","Button","getResponseText","Unique Key Range (Example : $B$7:$B","Step Code Cell : (Example : $G$1)","=arrayformula(if(isblank(","),\"\",ifna(VLOOKUP(","&",",{Data!$A:$A&Data!$B:$B,Data!$C:$C},2,FALSE),\"\")))","setFormula","getCurrentCell","URL of the spreadsheet from where \x0Adata has to be imported","Name of the sheet/tab from where \x0Adata has to be imported","Enter the range from where \x0Adata has to be imported.","=importrange(\"","\",\"","!","\")","Opening Time","Closing Time","activate","A1","getRange","=now()","C1","setValue","D1","B1","=","/24-","/24","B2:F2","1:1","getRow","getActiveRange","getNumRows","hideRows","getActiveSheet","2:2","getValue","Date Cell in which you want to add TAT","TAT cell (example G$5)","=if(",",if(and(hour(","+",")>",",(hour(",")<",")),",",workday.intl(int(","),1,\"0000001\")+hour(","-$B$1)/24+minute(",")/1440),\"\")","dd/MM/yyyy HH:mm:ss","setNumberFormat","getActiveRangeList","Direction","getNextDataRange","getSelection","activateAsCurrentCell","CopyPasteType","copyTo",",WORKDAY.INTL(",",",",\"0000001\")+hour(",")/24+MINUTE(",")/1440,\"\")","Date Cell in which you want to add lead time","Lead Time Cell","Number of Days Before Lead Time","-",",\"\")","Date Cell","Number of Days after previous planned (Write 0 if same day)","Time of day in hour/24 format","),",",\"0000001\",)+","Status Cell","getA1Notation",",if(","<>\"\",$A$1,\"\"))","Planned Cell","Actual Cell","getConditionalFormatRules","<>\"\",if(",">",",\"\"),$A$1-","),\"\")","[h]:mm:ss","build","#B7E1CD","setBackground","whenCellNotEmpty","setRanges","newConditionalFormatRule","push","setConditionalFormatRules","length",",1,0),0)","whenFormulaSatisfied","splice","#F4C7C3",",0,if(","<$A$1,1,0))","#FCE8B2","substr","getFormula","=\"No\",","=\"Yes\",","RecalculationInterval","setRecalculationInterval","setIterativeCalculationEnabled","setMaxIterativeCalculationCycles","setIterativeCalculationConvergenceThreshold","getProperty","getActiveSpreadsheet","DB_Format","getSheetByName","insertSheet","setName","Unique Key","Planned","Step","How","Link","For PC","appendRow","B:B","WrapStrategy","setWrapStrategy","E:E","G1","Steps Directory","Step Code","Planned/Actual Range","Unique Key Range","Query","Domain Not Registered. (If you think this is an error, please click the install button and try again.)","alert","getLastRow","getValues","select Col1,Col2,\'","\',\'","\' where Col3 is null and Col1 is not null and Col2 is not null label \'","\' \'\',\'","\' \'\'","ifna(query({","},\'Steps Directory\'!","),{\"\",\"\",\"\",\"\"});","),{\"\",\"\",\"\",\"\"})","=sort({","},1,1)","A2","Prefilled URL of Form. Put the code : u123u in unique key. Put the code : k123k in step code.","u123u","\"&$A$2:$A&\"","replace","k123k","\"&vlookup($C$2:$C,\'Steps Directory\'!$A2:$C,3,false)))","=ArrayFormula(if(isblank($A$2:$A),\"\",\"","=ArrayFormula(if(isblank($B$2:$B),\"\",if($B$2:$B<$G$1,\"Yes\",\"No\")))","E2","F2"];function install(){var _0x3f69x2=PropertiesService[_0x9334[0]]();_0x3f69x2[_0x9334[1]]();var _0x3f69x3=UrlFetchApp[_0x9334[5]](_0x9334[3]+ Session[_0x9334[4]](),{"followRedirects":true})[_0x9334[2]]();var _0x3f69x4=PropertiesService[_0x9334[0]]()[_0x9334[7]](_0x9334[6],_0x3f69x3)}function onOpen(){var _0x3f69x6=SpreadsheetApp[_0x9334[8]]();var _0x3f69x7=SpreadsheetApp[_0x9334[9]]();_0x3f69x6[_0x9334[32]](_0x9334[45])[_0x9334[13]](_0x9334[43],_0x9334[44])[_0x9334[14]]()[_0x9334[33]](_0x3f69x6[_0x9334[32]](_0x9334[42])[_0x9334[13]](_0x9334[40],_0x9334[41])[_0x9334[14]]()[_0x9334[13]](_0x9334[38],_0x9334[39])[_0x9334[14]]()[_0x9334[13]](_0x9334[36],_0x9334[37]))[_0x9334[14]]()[_0x9334[13]](_0x9334[34],_0x9334[35])[_0x9334[14]]()[_0x9334[33]](_0x3f69x6[_0x9334[32]](_0x9334[31])[_0x9334[13]](_0x9334[29],_0x9334[30])[_0x9334[14]]()[_0x9334[13]](_0x9334[27],_0x9334[28])[_0x9334[14]]()[_0x9334[13]](_0x9334[25],_0x9334[26])[_0x9334[14]]()[_0x9334[13]](_0x9334[23],_0x9334[24])[_0x9334[14]]()[_0x9334[13]](_0x9334[21],_0x9334[22])[_0x9334[14]]()[_0x9334[13]](_0x9334[19],_0x9334[20])[_0x9334[14]]()[_0x9334[13]](_0x9334[17],_0x9334[18])[_0x9334[14]]()[_0x9334[13]](_0x9334[15],_0x9334[16])[_0x9334[14]]()[_0x9334[13]](_0x9334[11],_0x9334[12]))[_0x9334[10]]()}function vlookupFormula(){var _0x3f69x7=SpreadsheetApp[_0x9334[9]]();var _0x3f69x6=SpreadsheetApp[_0x9334[8]]();var _0x3f69x9=_0x3f69x6[_0x9334[48]](_0x9334[46],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69x9[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69x9=_0x3f69x9[_0x9334[52]]()};var _0x3f69xa=_0x3f69x6[_0x9334[48]](_0x9334[53],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69xa[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69xa=_0x3f69xa[_0x9334[52]]()};var _0x3f69xb=_0x3f69x6[_0x9334[48]](_0x9334[54],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69xb[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69xb=_0x3f69xb[_0x9334[52]]()};_0x3f69x7[_0x9334[60]]()[_0x9334[59]](_0x9334[55]+ _0x3f69x9+ _0x9334[56]+ _0x3f69xa+ _0x9334[57]+ _0x3f69xb+ _0x9334[58])}function importRangeFormula(){var _0x3f69x7=SpreadsheetApp[_0x9334[9]]();var _0x3f69x6=SpreadsheetApp[_0x9334[8]]();var _0x3f69x9=_0x3f69x6[_0x9334[48]](_0x9334[61],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69x9[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69x9=_0x3f69x9[_0x9334[52]]()};var _0x3f69xa=_0x3f69x6[_0x9334[48]](_0x9334[62],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69xa[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69xa=_0x3f69xa[_0x9334[52]]()};var _0x3f69xb=_0x3f69x6[_0x9334[48]](_0x9334[63],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69xb[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69xb=_0x3f69xb[_0x9334[52]]()};_0x3f69x7[_0x9334[60]]()[_0x9334[59]](_0x9334[64]+ _0x3f69x9+ _0x9334[65]+ _0x3f69xa+ _0x9334[66]+ _0x3f69xb+ _0x9334[67])}function initial(){var _0x3f69x7=SpreadsheetApp[_0x9334[9]]();var _0x3f69x6=SpreadsheetApp[_0x9334[8]]();var _0x3f69x9=_0x3f69x6[_0x9334[48]](_0x9334[68],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69x9[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69x9=_0x3f69x9[_0x9334[52]]()};var _0x3f69xa=_0x3f69x6[_0x9334[48]](_0x9334[69],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69xa[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69xa=_0x3f69xa[_0x9334[52]]()};iterative();var _0x3f69xe=SpreadsheetApp[_0x9334[9]]();_0x3f69xe[_0x9334[72]](_0x9334[71])[_0x9334[70]]();_0x3f69xe[_0x9334[60]]()[_0x9334[59]](_0x9334[73]);_0x3f69xe[_0x9334[72]](_0x9334[74])[_0x9334[70]]();_0x3f69xe[_0x9334[60]]()[_0x9334[75]](_0x3f69x9);_0x3f69xe[_0x9334[72]](_0x9334[76])[_0x9334[70]]();_0x3f69xe[_0x9334[60]]()[_0x9334[75]](_0x3f69xa);_0x3f69xe[_0x9334[72]](_0x9334[77])[_0x9334[70]]();_0x3f69xe[_0x9334[60]]()[_0x9334[59]](_0x9334[78]+ _0x3f69xa+ _0x9334[79]+ _0x3f69x9+ _0x9334[80]);_0x3f69xe[_0x9334[72]](_0x9334[81])[_0x9334[70]]();_0x3f69xe[_0x9334[72]](_0x9334[82])[_0x9334[70]]();_0x3f69xe[_0x9334[87]]()[_0x9334[86]](_0x3f69xe[_0x9334[84]]()[_0x9334[83]](),_0x3f69xe[_0x9334[84]]()[_0x9334[85]]());_0x3f69xe[_0x9334[72]](_0x9334[88])[_0x9334[70]]()}function plannedwwh(){var _0x3f69x7=SpreadsheetApp[_0x9334[9]]();var _0x3f69x6=SpreadsheetApp[_0x9334[8]]();var _0x3f69x10=_0x3f69x7[_0x9334[72]](_0x9334[74])[_0x9334[89]]();var _0x3f69x11=_0x3f69x7[_0x9334[72]](_0x9334[76])[_0x9334[89]]();var _0x3f69x12=_0x3f69x6[_0x9334[48]](_0x9334[90],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69x12[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69x12=_0x3f69x12[_0x9334[52]]()};var _0x3f69x13=_0x3f69x6[_0x9334[48]](_0x9334[91],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69x13[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69x13=_0x3f69x13[_0x9334[52]]()};_0x3f69x7[_0x9334[60]]()[_0x9334[59]](_0x9334[92]+ _0x3f69x12+ _0x9334[93]+ _0x3f69x12+ _0x9334[94]+ _0x3f69x13+ _0x9334[95]+ _0x3f69x10.toString()+ _0x9334[96]+ _0x3f69x12+ _0x9334[94]+ _0x3f69x13+ _0x9334[97]+ _0x3f69x11.toString()+ _0x9334[98]+ _0x3f69x12+ _0x9334[94]+ _0x3f69x13+ _0x9334[99]+ _0x3f69x12+ _0x9334[100]+ _0x3f69x12+ _0x9334[94]+ _0x3f69x13+ _0x9334[101]+ _0x3f69x12+ _0x9334[102]);_0x3f69x7[_0x9334[105]]()[_0x9334[104]](_0x9334[103]);var _0x3f69x14=_0x3f69x7[_0x9334[60]]();_0x3f69x7[_0x9334[108]]()[_0x9334[107]](SpreadsheetApp[_0x9334[106]].DOWN)[_0x9334[70]]();_0x3f69x14[_0x9334[109]]();_0x3f69x14= _0x3f69x7[_0x9334[60]]();_0x3f69x7[_0x9334[108]]()[_0x9334[107]](SpreadsheetApp[_0x9334[106]].DOWN)[_0x9334[70]]();_0x3f69x14[_0x9334[109]]();_0x3f69x7[_0x9334[60]]()[_0x9334[111]](_0x3f69x7[_0x9334[84]](),SpreadsheetApp[_0x9334[110]].PASTE_NORMAL,false)}function plannedindays(){var _0x3f69x7=SpreadsheetApp[_0x9334[9]]();var _0x3f69x6=SpreadsheetApp[_0x9334[8]]();var _0x3f69x10=_0x3f69x7[_0x9334[72]](_0x9334[74])[_0x9334[89]]();var _0x3f69x11=_0x3f69x7[_0x9334[72]](_0x9334[76])[_0x9334[89]]();var _0x3f69x12=_0x3f69x6[_0x9334[48]](_0x9334[90],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69x12[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69x12=_0x3f69x12[_0x9334[52]]()};var _0x3f69x13=_0x3f69x6[_0x9334[48]](_0x9334[91],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69x13[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69x13=_0x3f69x13[_0x9334[52]]()};_0x3f69x7[_0x9334[60]]()[_0x9334[59]](_0x9334[92]+ _0x3f69x12+ _0x9334[112]+ _0x3f69x12+ _0x9334[113]+ _0x3f69x13+ _0x9334[114]+ _0x3f69x12+ _0x9334[115]+ _0x3f69x12+ _0x9334[116]);_0x3f69x7[_0x9334[105]]()[_0x9334[104]](_0x9334[103]);var _0x3f69x14=_0x3f69x7[_0x9334[60]]();_0x3f69x7[_0x9334[108]]()[_0x9334[107]](SpreadsheetApp[_0x9334[106]].DOWN)[_0x9334[70]]();_0x3f69x14[_0x9334[109]]();_0x3f69x14= _0x3f69x7[_0x9334[60]]();_0x3f69x7[_0x9334[108]]()[_0x9334[107]](SpreadsheetApp[_0x9334[106]].DOWN)[_0x9334[70]]();_0x3f69x14[_0x9334[109]]();_0x3f69x7[_0x9334[60]]()[_0x9334[111]](_0x3f69x7[_0x9334[84]](),SpreadsheetApp[_0x9334[110]].PASTE_NORMAL,false)}function plannedlead(){var _0x3f69x7=SpreadsheetApp[_0x9334[9]]();var _0x3f69x6=SpreadsheetApp[_0x9334[8]]();var _0x3f69x10=_0x3f69x7[_0x9334[72]](_0x9334[74]);var _0x3f69x11=_0x3f69x7[_0x9334[72]](_0x9334[76]);var _0x3f69x12=_0x3f69x6[_0x9334[48]](_0x9334[117],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69x12[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69x12=_0x3f69x12[_0x9334[52]]()};var _0x3f69x17=_0x3f69x6[_0x9334[48]](_0x9334[118],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69x17[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69x17=_0x3f69x17[_0x9334[52]]()};var _0x3f69x13=_0x3f69x6[_0x9334[48]](_0x9334[119],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69x13[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69x13=_0x3f69x13[_0x9334[52]]()};_0x3f69x7[_0x9334[60]]()[_0x9334[59]](_0x9334[92]+ _0x3f69x17+ _0x9334[113]+ _0x3f69x12+ _0x9334[94]+ _0x3f69x17+ _0x9334[120]+ _0x3f69x13+ _0x9334[121]);_0x3f69x7[_0x9334[105]]()[_0x9334[104]](_0x9334[103]);var _0x3f69x14=_0x3f69x7[_0x9334[60]]();_0x3f69x7[_0x9334[108]]()[_0x9334[107]](SpreadsheetApp[_0x9334[106]].DOWN)[_0x9334[70]]();_0x3f69x14[_0x9334[109]]();_0x3f69x14= _0x3f69x7[_0x9334[60]]();_0x3f69x7[_0x9334[108]]()[_0x9334[107]](SpreadsheetApp[_0x9334[106]].DOWN)[_0x9334[70]]();_0x3f69x14[_0x9334[109]]();_0x3f69x7[_0x9334[60]]()[_0x9334[111]](_0x3f69x7[_0x9334[84]](),SpreadsheetApp[_0x9334[110]].PASTE_NORMAL,false)}function specificTime(){var _0x3f69x7=SpreadsheetApp[_0x9334[9]]();var _0x3f69x6=SpreadsheetApp[_0x9334[8]]();var _0x3f69x10=_0x3f69x7[_0x9334[72]](_0x9334[74]);var _0x3f69x11=_0x3f69x7[_0x9334[72]](_0x9334[76]);var _0x3f69x12=_0x3f69x6[_0x9334[48]](_0x9334[122],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69x12[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69x12=_0x3f69x12[_0x9334[52]]()};var _0x3f69x17=_0x3f69x6[_0x9334[48]](_0x9334[123],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69x17[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69x17=_0x3f69x17[_0x9334[52]]()};var _0x3f69x13=_0x3f69x6[_0x9334[48]](_0x9334[124],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69x13[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69x13=_0x3f69x13[_0x9334[52]]()};_0x3f69x7[_0x9334[60]]()[_0x9334[59]](_0x9334[92]+ _0x3f69x12+ _0x9334[99]+ _0x3f69x12+ _0x9334[125]+ _0x3f69x17+ _0x9334[126]+ _0x3f69x13+ _0x9334[121]);_0x3f69x7[_0x9334[105]]()[_0x9334[104]](_0x9334[103]);var _0x3f69x14=_0x3f69x7[_0x9334[60]]();_0x3f69x7[_0x9334[108]]()[_0x9334[107]](SpreadsheetApp[_0x9334[106]].DOWN)[_0x9334[70]]();_0x3f69x14[_0x9334[109]]();_0x3f69x14= _0x3f69x7[_0x9334[60]]();_0x3f69x7[_0x9334[108]]()[_0x9334[107]](SpreadsheetApp[_0x9334[106]].DOWN)[_0x9334[70]]();_0x3f69x14[_0x9334[109]]();_0x3f69x7[_0x9334[60]]()[_0x9334[111]](_0x3f69x7[_0x9334[84]](),SpreadsheetApp[_0x9334[110]].PASTE_NORMAL,false)}function actualTime(){var _0x3f69x7=SpreadsheetApp[_0x9334[9]]();var _0x3f69x6=SpreadsheetApp[_0x9334[8]]();var _0x3f69x17=_0x3f69x6[_0x9334[48]](_0x9334[127],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69x17[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69x17=_0x3f69x17[_0x9334[52]]()};var _0x3f69x1a=_0x3f69x7[_0x9334[60]]()[_0x9334[128]]();_0x3f69x7[_0x9334[60]]()[_0x9334[59]](_0x9334[92]+ _0x3f69x1a+ _0x9334[113]+ _0x3f69x1a+ _0x9334[129]+ _0x3f69x17+ _0x9334[130]);_0x3f69x7[_0x9334[105]]()[_0x9334[104]](_0x9334[103]);var _0x3f69x14=_0x3f69x7[_0x9334[60]]();_0x3f69x7[_0x9334[108]]()[_0x9334[107]](SpreadsheetApp[_0x9334[106]].DOWN)[_0x9334[70]]();_0x3f69x14[_0x9334[109]]();_0x3f69x14= _0x3f69x7[_0x9334[60]]();_0x3f69x7[_0x9334[108]]()[_0x9334[107]](SpreadsheetApp[_0x9334[106]].DOWN)[_0x9334[70]]();_0x3f69x14[_0x9334[109]]();_0x3f69x7[_0x9334[60]]()[_0x9334[111]](_0x3f69x7[_0x9334[84]](),SpreadsheetApp[_0x9334[110]].PASTE_NORMAL,false)}function timeDelay(){var _0x3f69x7=SpreadsheetApp[_0x9334[9]]();var _0x3f69x6=SpreadsheetApp[_0x9334[8]]();var _0x3f69x12=_0x3f69x6[_0x9334[48]](_0x9334[131],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69x12[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69x12=_0x3f69x12[_0x9334[52]]()};var _0x3f69x17=_0x3f69x6[_0x9334[48]](_0x9334[132],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69x17[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69x17=_0x3f69x17[_0x9334[52]]()};var _0x3f69x1c=_0x3f69x7[_0x9334[87]]()[_0x9334[133]]();_0x3f69x7[_0x9334[60]]()[_0x9334[59]](_0x9334[92]+ _0x3f69x12+ _0x9334[129]+ _0x3f69x17+ _0x9334[134]+ _0x3f69x17+ _0x9334[135]+ _0x3f69x12+ _0x9334[113]+ _0x3f69x17+ _0x9334[120]+ _0x3f69x12+ _0x9334[136]+ _0x3f69x12+ _0x9334[137]);_0x3f69x7[_0x9334[105]]()[_0x9334[104]](_0x9334[138]);var _0x3f69xe=SpreadsheetApp[_0x9334[9]]();var _0x3f69x1c=_0x3f69xe[_0x9334[87]]()[_0x9334[133]]();_0x3f69x1c[_0x9334[145]](SpreadsheetApp[_0x9334[144]]()[_0x9334[143]]([_0x3f69xe[_0x9334[84]]()])[_0x9334[142]]()[_0x9334[141]](_0x9334[140])[_0x9334[139]]());_0x3f69xe[_0x9334[87]]()[_0x9334[146]](_0x3f69x1c);_0x3f69x1c= _0x3f69xe[_0x9334[87]]()[_0x9334[133]]();_0x3f69x1c[_0x9334[150]](_0x3f69x1c[_0x9334[147]]- 1,1,SpreadsheetApp[_0x9334[144]]()[_0x9334[143]]([_0x3f69xe[_0x9334[84]]()])[_0x9334[149]](_0x9334[92]+ _0x3f69x17+ _0x9334[129]+ _0x3f69x17+ _0x9334[135]+ _0x3f69x12+ _0x9334[148])[_0x9334[141]](_0x9334[140])[_0x9334[139]]());_0x3f69xe[_0x9334[87]]()[_0x9334[146]](_0x3f69x1c);_0x3f69x1c= _0x3f69xe[_0x9334[87]]()[_0x9334[133]]();_0x3f69x1c[_0x9334[150]](_0x3f69x1c[_0x9334[147]]- 1,1,SpreadsheetApp[_0x9334[144]]()[_0x9334[143]]([_0x3f69xe[_0x9334[84]]()])[_0x9334[149]](_0x9334[92]+ _0x3f69x17+ _0x9334[129]+ _0x3f69x17+ _0x9334[135]+ _0x3f69x12+ _0x9334[148])[_0x9334[141]](_0x9334[140])[_0x9334[139]]());_0x3f69xe[_0x9334[87]]()[_0x9334[146]](_0x3f69x1c);_0x3f69x1c= _0x3f69xe[_0x9334[87]]()[_0x9334[133]]();_0x3f69x1c[_0x9334[150]](_0x3f69x1c[_0x9334[147]]- 1,1,SpreadsheetApp[_0x9334[144]]()[_0x9334[143]]([_0x3f69xe[_0x9334[84]]()])[_0x9334[149]](_0x9334[92]+ _0x3f69x17+ _0x9334[129]+ _0x3f69x17+ _0x9334[135]+ _0x3f69x12+ _0x9334[148])[_0x9334[141]](_0x9334[140])[_0x9334[139]]());_0x3f69xe[_0x9334[87]]()[_0x9334[146]](_0x3f69x1c);_0x3f69x1c= _0x3f69xe[_0x9334[87]]()[_0x9334[133]]();_0x3f69x1c[_0x9334[150]](_0x3f69x1c[_0x9334[147]]- 1,1,SpreadsheetApp[_0x9334[144]]()[_0x9334[143]]([_0x3f69xe[_0x9334[84]]()])[_0x9334[149]](_0x9334[92]+ _0x3f69x17+ _0x9334[129]+ _0x3f69x17+ _0x9334[135]+ _0x3f69x12+ _0x9334[148])[_0x9334[141]](_0x9334[140])[_0x9334[139]]());_0x3f69xe[_0x9334[87]]()[_0x9334[146]](_0x3f69x1c);_0x3f69x1c= _0x3f69xe[_0x9334[87]]()[_0x9334[133]]();_0x3f69x1c[_0x9334[150]](_0x3f69x1c[_0x9334[147]]- 1,1,SpreadsheetApp[_0x9334[144]]()[_0x9334[143]]([_0x3f69xe[_0x9334[84]]()])[_0x9334[149]](_0x9334[92]+ _0x3f69x17+ _0x9334[129]+ _0x3f69x17+ _0x9334[135]+ _0x3f69x12+ _0x9334[148])[_0x9334[141]](_0x9334[151])[_0x9334[139]]());_0x3f69xe[_0x9334[87]]()[_0x9334[146]](_0x3f69x1c);_0x3f69x1c= _0x3f69xe[_0x9334[87]]()[_0x9334[133]]();_0x3f69x1c[_0x9334[145]](SpreadsheetApp[_0x9334[144]]()[_0x9334[143]]([_0x3f69xe[_0x9334[84]]()])[_0x9334[142]]()[_0x9334[141]](_0x9334[140])[_0x9334[139]]());_0x3f69xe[_0x9334[87]]()[_0x9334[146]](_0x3f69x1c);_0x3f69x1c= _0x3f69xe[_0x9334[87]]()[_0x9334[133]]();_0x3f69x1c[_0x9334[150]](_0x3f69x1c[_0x9334[147]]- 1,1,SpreadsheetApp[_0x9334[144]]()[_0x9334[143]]([_0x3f69xe[_0x9334[84]]()])[_0x9334[149]](_0x9334[92]+ _0x3f69x17+ _0x9334[152]+ _0x3f69x12+ _0x9334[153])[_0x9334[141]](_0x9334[140])[_0x9334[139]]());_0x3f69xe[_0x9334[87]]()[_0x9334[146]](_0x3f69x1c);_0x3f69x1c= _0x3f69xe[_0x9334[87]]()[_0x9334[133]]();_0x3f69x1c[_0x9334[150]](_0x3f69x1c[_0x9334[147]]- 1,1,SpreadsheetApp[_0x9334[144]]()[_0x9334[143]]([_0x3f69xe[_0x9334[84]]()])[_0x9334[149]](_0x9334[92]+ _0x3f69x17+ _0x9334[152]+ _0x3f69x12+ _0x9334[153])[_0x9334[141]](_0x9334[140])[_0x9334[139]]());_0x3f69xe[_0x9334[87]]()[_0x9334[146]](_0x3f69x1c);_0x3f69x1c= _0x3f69xe[_0x9334[87]]()[_0x9334[133]]();_0x3f69x1c[_0x9334[150]](_0x3f69x1c[_0x9334[147]]- 1,1,SpreadsheetApp[_0x9334[144]]()[_0x9334[143]]([_0x3f69xe[_0x9334[84]]()])[_0x9334[149]](_0x9334[92]+ _0x3f69x17+ _0x9334[152]+ _0x3f69x12+ _0x9334[153])[_0x9334[141]](_0x9334[140])[_0x9334[139]]());_0x3f69xe[_0x9334[87]]()[_0x9334[146]](_0x3f69x1c);_0x3f69x1c= _0x3f69xe[_0x9334[87]]()[_0x9334[133]]();_0x3f69x1c[_0x9334[150]](_0x3f69x1c[_0x9334[147]]- 1,1,SpreadsheetApp[_0x9334[144]]()[_0x9334[143]]([_0x3f69xe[_0x9334[84]]()])[_0x9334[149]](_0x9334[92]+ _0x3f69x17+ _0x9334[152]+ _0x3f69x12+ _0x9334[153])[_0x9334[141]](_0x9334[154])[_0x9334[139]]());_0x3f69xe[_0x9334[87]]()[_0x9334[146]](_0x3f69x1c);var _0x3f69x14=_0x3f69x7[_0x9334[60]]();_0x3f69x7[_0x9334[108]]()[_0x9334[107]](SpreadsheetApp[_0x9334[106]].DOWN)[_0x9334[70]]();_0x3f69x14[_0x9334[109]]();_0x3f69x14= _0x3f69x7[_0x9334[60]]();_0x3f69x7[_0x9334[108]]()[_0x9334[107]](SpreadsheetApp[_0x9334[106]].DOWN)[_0x9334[70]]();_0x3f69x14[_0x9334[109]]();_0x3f69x7[_0x9334[60]]()[_0x9334[111]](_0x3f69x7[_0x9334[84]](),SpreadsheetApp[_0x9334[110]].PASTE_NORMAL,false)}function tatifno(){var _0x3f69x7=SpreadsheetApp[_0x9334[9]]();var _0x3f69x6=SpreadsheetApp[_0x9334[8]]();var _0x3f69x10=_0x3f69x7[_0x9334[72]](_0x9334[74]);var _0x3f69x11=_0x3f69x7[_0x9334[72]](_0x9334[76]);var _0x3f69x12=_0x3f69x6[_0x9334[48]](_0x9334[127],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69x12[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69x12=_0x3f69x12[_0x9334[52]]()};var _0x3f69x1e=_0x3f69x7[_0x9334[60]]()[_0x9334[156]]()[_0x9334[155]](1);_0x3f69x7[_0x9334[60]]()[_0x9334[59]](_0x9334[92]+ _0x3f69x12+ _0x9334[157]+ _0x3f69x1e+ _0x9334[121]);_0x3f69x7[_0x9334[105]]()[_0x9334[104]](_0x9334[103]);var _0x3f69x14=_0x3f69x7[_0x9334[60]]();_0x3f69x7[_0x9334[108]]()[_0x9334[107]](SpreadsheetApp[_0x9334[106]].DOWN)[_0x9334[70]]();_0x3f69x14[_0x9334[109]]();_0x3f69x14= _0x3f69x7[_0x9334[60]]();_0x3f69x7[_0x9334[108]]()[_0x9334[107]](SpreadsheetApp[_0x9334[106]].DOWN)[_0x9334[70]]();_0x3f69x14[_0x9334[109]]();_0x3f69x7[_0x9334[60]]()[_0x9334[111]](_0x3f69x7[_0x9334[84]](),SpreadsheetApp[_0x9334[110]].PASTE_NORMAL,false)}function tatifyes(){var _0x3f69x7=SpreadsheetApp[_0x9334[9]]();var _0x3f69x6=SpreadsheetApp[_0x9334[8]]();var _0x3f69x10=_0x3f69x7[_0x9334[72]](_0x9334[74]);var _0x3f69x11=_0x3f69x7[_0x9334[72]](_0x9334[76]);var _0x3f69x12=_0x3f69x6[_0x9334[48]](_0x9334[127],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69x12[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69x12=_0x3f69x12[_0x9334[52]]()};var _0x3f69x1e=_0x3f69x7[_0x9334[60]]()[_0x9334[156]]()[_0x9334[155]](1);_0x3f69x7[_0x9334[60]]()[_0x9334[59]](_0x9334[92]+ _0x3f69x12+ _0x9334[158]+ _0x3f69x1e+ _0x9334[121]);_0x3f69x7[_0x9334[105]]()[_0x9334[104]](_0x9334[103]);var _0x3f69x14=_0x3f69x7[_0x9334[60]]();_0x3f69x7[_0x9334[108]]()[_0x9334[107]](SpreadsheetApp[_0x9334[106]].DOWN)[_0x9334[70]]();_0x3f69x14[_0x9334[109]]();_0x3f69x14= _0x3f69x7[_0x9334[60]]();_0x3f69x7[_0x9334[108]]()[_0x9334[107]](SpreadsheetApp[_0x9334[106]].DOWN)[_0x9334[70]]();_0x3f69x14[_0x9334[109]]();_0x3f69x7[_0x9334[60]]()[_0x9334[111]](_0x3f69x7[_0x9334[84]](),SpreadsheetApp[_0x9334[110]].PASTE_NORMAL,false)}function iterative(){var _0x3f69xe=SpreadsheetApp[_0x9334[9]]();_0x3f69xe[_0x9334[160]](SpreadsheetApp[_0x9334[159]].ON_CHANGE);_0x3f69xe[_0x9334[161]](true);_0x3f69xe[_0x9334[162]](1);_0x3f69xe[_0x9334[163]](0.05)}function createSetup(){if(PropertiesService[_0x9334[0]]()[_0x9334[164]](_0x9334[6])!= 1&& PropertiesService[_0x9334[0]]()[_0x9334[164]](_0x9334[6])){var _0x3f69x7=SpreadsheetApp[_0x9334[165]]();var _0x3f69x22=_0x3f69x7[_0x9334[167]](_0x9334[166]);if(_0x3f69x22!= null){}else {_0x3f69x22= _0x3f69x7[_0x9334[168]]();_0x3f69x22[_0x9334[169]](_0x9334[166]);_0x3f69x22[_0x9334[176]]([_0x9334[170],_0x9334[171],_0x9334[172],_0x9334[173],_0x9334[174],_0x9334[175]]);_0x3f69x22[_0x9334[72]](_0x9334[177])[_0x9334[104]](_0x9334[103]);_0x3f69x22[_0x9334[72]](_0x9334[180])[_0x9334[179]](SpreadsheetApp[_0x9334[178]].CLIP);_0x3f69x22[_0x9334[72]](_0x9334[181])[_0x9334[59]](_0x9334[73])};var _0x3f69x23=_0x3f69x7[_0x9334[167]](_0x9334[182]);if(_0x3f69x23!= null){}else {_0x3f69x23= _0x3f69x7[_0x9334[168]]();_0x3f69x23[_0x9334[169]](_0x9334[182]);_0x3f69x23[_0x9334[176]]([_0x9334[172],_0x9334[173],_0x9334[183],_0x9334[184],_0x9334[185],_0x9334[186]])}}else {SpreadsheetApp[_0x9334[8]]()[_0x9334[188]](_0x9334[187])}}function createDB(){if(PropertiesService[_0x9334[0]]()[_0x9334[164]](_0x9334[6])!= 1&& PropertiesService[_0x9334[0]]()[_0x9334[164]](_0x9334[6])){var _0x3f69x7=SpreadsheetApp[_0x9334[165]]();var _0x3f69x25=_0x3f69x7[_0x9334[167]](_0x9334[182]);var _0x3f69x26=_0x3f69x25[_0x9334[189]]()- 1;var _0x3f69x3=_0x3f69x25[_0x9334[72]](2,1,_0x3f69x26,6)[_0x9334[190]]();var _0x3f69x27=[];for(var _0x3f69x28=0;_0x3f69x28< _0x3f69x3[_0x9334[147]];_0x3f69x28++){var _0x3f69x29=_0x3f69x3[_0x3f69x28];var _0x3f69x2a=_0x3f69x29[0];var _0x3f69x2b=_0x3f69x29[1];var _0x3f69x2c=_0x3f69x29[2];var _0x3f69x2d=_0x3f69x29[3];var _0x3f69x2e=_0x3f69x29[4];var _0x3f69x2f=_0x3f69x25[_0x9334[72]](_0x3f69x28+ 2,6);_0x3f69x2f[_0x9334[75]](_0x9334[191]+ _0x3f69x2a+ _0x9334[192]+ _0x3f69x2b+ _0x9334[193]+ _0x3f69x2a+ _0x9334[194]+ _0x3f69x2b+ _0x9334[195]);if(_0x3f69x28< _0x3f69x3[_0x9334[147]]- 1){var _0x3f69x30=_0x9334[196]+ _0x3f69x2e+ _0x9334[113]+ _0x3f69x2d+ _0x9334[197]+ _0x3f69x2f[_0x9334[128]]()+ _0x9334[198]}else {var _0x3f69x30=_0x9334[196]+ _0x3f69x2e+ _0x9334[113]+ _0x3f69x2d+ _0x9334[197]+ _0x3f69x2f[_0x9334[128]]()+ _0x9334[199]};_0x3f69x27[_0x9334[145]](_0x3f69x30)};var _0x3f69x31=_0x9334[200];for(var _0x3f69x32=0;_0x3f69x32< _0x3f69x27[_0x9334[147]];_0x3f69x32++){_0x3f69x31= _0x3f69x31+ _0x3f69x27[_0x3f69x32]};_0x3f69x31= _0x3f69x31+ _0x9334[201];_0x3f69x7[_0x9334[167]](_0x9334[166])[_0x9334[72]](_0x9334[202])[_0x9334[59]](_0x3f69x31);var _0x3f69x6=SpreadsheetApp[_0x9334[8]]();var _0x3f69x33=_0x3f69x6[_0x9334[48]](_0x9334[203],_0x3f69x6[_0x9334[47]].OK_CANCEL);if(_0x3f69x33[_0x9334[49]]()== _0x3f69x6[_0x9334[51]][_0x9334[50]]){var _0x3f69x34=_0x3f69x33[_0x9334[52]]()};var _0x3f69x35=_0x3f69x34[_0x9334[206]](_0x9334[204],_0x9334[205]);_0x3f69x35= _0x3f69x35[_0x9334[206]](_0x9334[207],_0x9334[208]);arrayFormula= _0x9334[209]+ _0x3f69x35;var _0x3f69x36=_0x9334[210];_0x3f69x7[_0x9334[167]](_0x9334[166])[_0x9334[72]](_0x9334[211])[_0x9334[59]](arrayFormula);_0x3f69x7[_0x9334[167]](_0x9334[166])[_0x9334[72]](_0x9334[212])[_0x9334[59]](_0x3f69x36)}else {SpreadsheetApp[_0x9334[8]]()[_0x9334[188]](_0x9334[187])}}

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*/

Check list sheet automatic generate complete year details

 /**

 * @version: 1.0 Alpha-1

 * @author: Coolite Inc. http://www.coolite.com/

 * @date: 2008-05-13

 * @copyright: Copyright (c) 2006-2008, Coolite Inc. (http://www.coolite.com/). All rights reserved.

 * @license: Licensed under The MIT License. See license.txt and http://www.datejs.com/license/. 

 * @website: http://www.datejs.com/

 */

Date.CultureInfo={name:"en-US",englishName:"English (United States)",nativeName:"English (United States)",dayNames:["Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday"],abbreviatedDayNames:["Sun","Mon","Tue","Wed","Thu","Fri","Sat"],shortestDayNames:["Su","Mo","Tu","We","Th","Fr","Sa"],firstLetterDayNames:["S","M","T","W","T","F","S"],monthNames:["January","February","March","April","May","June","July","August","September","October","November","December"],abbreviatedMonthNames:["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"],amDesignator:"AM",pmDesignator:"PM",firstDayOfWeek:0,twoDigitYearMax:2029,dateElementOrder:"mdy",formatPatterns:{shortDate:"M/d/yyyy",longDate:"dddd, MMMM dd, yyyy",shortTime:"h:mm tt",longTime:"h:mm:ss tt",fullDateTime:"dddd, MMMM dd, yyyy h:mm:ss tt",sortableDateTime:"yyyy-MM-ddTHH:mm:ss",universalSortableDateTime:"yyyy-MM-dd HH:mm:ssZ",rfc1123:"ddd, dd MMM yyyy HH:mm:ss GMT",monthDay:"MMMM dd",yearMonth:"MMMM, yyyy"},regexPatterns:{jan:/^jan(uary)?/i,feb:/^feb(ruary)?/i,mar:/^mar(ch)?/i,apr:/^apr(il)?/i,may:/^may/i,jun:/^jun(e)?/i,jul:/^jul(y)?/i,aug:/^aug(ust)?/i,sep:/^sep(t(ember)?)?/i,oct:/^oct(ober)?/i,nov:/^nov(ember)?/i,dec:/^dec(ember)?/i,sun:/^su(n(day)?)?/i,mon:/^mo(n(day)?)?/i,tue:/^tu(e(s(day)?)?)?/i,wed:/^we(d(nesday)?)?/i,thu:/^th(u(r(s(day)?)?)?)?/i,fri:/^fr(i(day)?)?/i,sat:/^sa(t(urday)?)?/i,future:/^next/i,past:/^last|past|prev(ious)?/i,add:/^(\+|aft(er)?|from|hence)/i,subtract:/^(\-|bef(ore)?|ago)/i,yesterday:/^yes(terday)?/i,today:/^t(od(ay)?)?/i,tomorrow:/^tom(orrow)?/i,now:/^n(ow)?/i,millisecond:/^ms|milli(second)?s?/i,second:/^sec(ond)?s?/i,minute:/^mn|min(ute)?s?/i,hour:/^h(our)?s?/i,week:/^w(eek)?s?/i,month:/^m(onth)?s?/i,day:/^d(ay)?s?/i,year:/^y(ear)?s?/i,shortMeridian:/^(a|p)/i,longMeridian:/^(a\.?m?\.?|p\.?m?\.?)/i,timezone:/^((e(s|d)t|c(s|d)t|m(s|d)t|p(s|d)t)|((gmt)?\s*(\+|\-)\s*\d\d\d\d?)|gmt|utc)/i,ordinalSuffix:/^\s*(st|nd|rd|th)/i,timeContext:/^\s*(\:|a(?!u|p)|p)/i},timezones:[{name:"UTC",offset:"-000"},{name:"GMT",offset:"-000"},{name:"EST",offset:"-0500"},{name:"EDT",offset:"-0400"},{name:"CST",offset:"-0600"},{name:"CDT",offset:"-0500"},{name:"MST",offset:"-0700"},{name:"MDT",offset:"-0600"},{name:"PST",offset:"-0800"},{name:"PDT",offset:"-0700"}]};

(function(){var $D=Date,$P=$D.prototype,$C=$D.CultureInfo,p=function(s,l){if(!l){l=2;}

return("000"+s).slice(l*-1);};$P.clearTime=function(){this.setHours(0);this.setMinutes(0);this.setSeconds(0);this.setMilliseconds(0);return this;};$P.setTimeToNow=function(){var n=new Date();this.setHours(n.getHours());this.setMinutes(n.getMinutes());this.setSeconds(n.getSeconds());this.setMilliseconds(n.getMilliseconds());return this;};$D.today=function(){return new Date().clearTime();};$D.compare=function(date1,date2){if(isNaN(date1)||isNaN(date2)){throw new Error(date1+" - "+date2);}else if(date1 instanceof Date&&date2 instanceof Date){return(date1<date2)?-1:(date1>date2)?1:0;}else{throw new TypeError(date1+" - "+date2);}};$D.equals=function(date1,date2){return(date1.compareTo(date2)===0);};$D.getDayNumberFromName=function(name){var n=$C.dayNames,m=$C.abbreviatedDayNames,o=$C.shortestDayNames,s=name.toLowerCase();for(var i=0;i<n.length;i++){if(n[i].toLowerCase()==s||m[i].toLowerCase()==s||o[i].toLowerCase()==s){return i;}}

return-1;};$D.getMonthNumberFromName=function(name){var n=$C.monthNames,m=$C.abbreviatedMonthNames,s=name.toLowerCase();for(var i=0;i<n.length;i++){if(n[i].toLowerCase()==s||m[i].toLowerCase()==s){return i;}}

return-1;};$D.isLeapYear=function(year){return((year%4===0&&year%100!==0)||year%400===0);};$D.getDaysInMonth=function(year,month){return[31,($D.isLeapYear(year)?29:28),31,30,31,30,31,31,30,31,30,31][month];};$D.getTimezoneAbbreviation=function(offset){var z=$C.timezones,p;for(var i=0;i<z.length;i++){if(z[i].offset===offset){return z[i].name;}}

return null;};$D.getTimezoneOffset=function(name){var z=$C.timezones,p;for(var i=0;i<z.length;i++){if(z[i].name===name.toUpperCase()){return z[i].offset;}}

return null;};$P.clone=function(){return new Date(this.getTime());};$P.compareTo=function(date){return Date.compare(this,date);};$P.equals=function(date){return Date.equals(this,date||new Date());};$P.between=function(start,end){return this.getTime()>=start.getTime()&&this.getTime()<=end.getTime();};$P.isAfter=function(date){return this.compareTo(date||new Date())===1;};$P.isBefore=function(date){return(this.compareTo(date||new Date())===-1);};$P.isToday=function(){return this.isSameDay(new Date());};$P.isSameDay=function(date){return this.clone().clearTime().equals(date.clone().clearTime());};$P.addMilliseconds=function(value){this.setMilliseconds(this.getMilliseconds()+value);return this;};$P.addSeconds=function(value){return this.addMilliseconds(value*1000);};$P.addMinutes=function(value){return this.addMilliseconds(value*60000);};$P.addHours=function(value){return this.addMilliseconds(value*3600000);};$P.addDays=function(value){this.setDate(this.getDate()+value);return this;};$P.addWeeks=function(value){return this.addDays(value*7);};$P.addMonths=function(value){var n=this.getDate();this.setDate(1);this.setMonth(this.getMonth()+value);this.setDate(Math.min(n,$D.getDaysInMonth(this.getFullYear(),this.getMonth())));return this;};$P.addYears=function(value){return this.addMonths(value*12);};$P.add=function(config){if(typeof config=="number"){this._orient=config;return this;}

var x=config;if(x.milliseconds){this.addMilliseconds(x.milliseconds);}

if(x.seconds){this.addSeconds(x.seconds);}

if(x.minutes){this.addMinutes(x.minutes);}

if(x.hours){this.addHours(x.hours);}

if(x.weeks){this.addWeeks(x.weeks);}

if(x.months){this.addMonths(x.months);}

if(x.years){this.addYears(x.years);}

if(x.days){this.addDays(x.days);}

return this;};var $y,$m,$d;$P.getWeek=function(){var a,b,c,d,e,f,g,n,s,w;$y=(!$y)?this.getFullYear():$y;$m=(!$m)?this.getMonth()+1:$m;$d=(!$d)?this.getDate():$d;if($m<=2){a=$y-1;b=(a/4|0)-(a/100|0)+(a/400|0);c=((a-1)/4|0)-((a-1)/100|0)+((a-1)/400|0);s=b-c;e=0;f=$d-1+(31*($m-1));}else{a=$y;b=(a/4|0)-(a/100|0)+(a/400|0);c=((a-1)/4|0)-((a-1)/100|0)+((a-1)/400|0);s=b-c;e=s+1;f=$d+((153*($m-3)+2)/5)+58+s;}

g=(a+b)%7;d=(f+g-e)%7;n=(f+3-d)|0;if(n<0){w=53-((g-s)/5|0);}else if(n>364+s){w=1;}else{w=(n/7|0)+1;}

$y=$m=$d=null;return w;};$P.getISOWeek=function(){$y=this.getUTCFullYear();$m=this.getUTCMonth()+1;$d=this.getUTCDate();return p(this.getWeek());};$P.setWeek=function(n){return this.moveToDayOfWeek(1).addWeeks(n-this.getWeek());};$D._validate=function(n,min,max,name){if(typeof n=="undefined"){return false;}else if(typeof n!="number"){throw new TypeError(n+" is not a Number.");}else if(n<min||n>max){throw new RangeError(n+" is not a valid value for "+name+".");}

return true;};$D.validateMillisecond=function(value){return $D._validate(value,0,999,"millisecond");};$D.validateSecond=function(value){return $D._validate(value,0,59,"second");};$D.validateMinute=function(value){return $D._validate(value,0,59,"minute");};$D.validateHour=function(value){return $D._validate(value,0,23,"hour");};$D.validateDay=function(value,year,month){return $D._validate(value,1,$D.getDaysInMonth(year,month),"day");};$D.validateMonth=function(value){return $D._validate(value,0,11,"month");};$D.validateYear=function(value){return $D._validate(value,0,9999,"year");};$P.set=function(config){if($D.validateMillisecond(config.millisecond)){this.addMilliseconds(config.millisecond-this.getMilliseconds());}

if($D.validateSecond(config.second)){this.addSeconds(config.second-this.getSeconds());}

if($D.validateMinute(config.minute)){this.addMinutes(config.minute-this.getMinutes());}

if($D.validateHour(config.hour)){this.addHours(config.hour-this.getHours());}

if($D.validateMonth(config.month)){this.addMonths(config.month-this.getMonth());}

if($D.validateYear(config.year)){this.addYears(config.year-this.getFullYear());}

if($D.validateDay(config.day,this.getFullYear(),this.getMonth())){this.addDays(config.day-this.getDate());}

if(config.timezone){this.setTimezone(config.timezone);}

if(config.timezoneOffset){this.setTimezoneOffset(config.timezoneOffset);}

if(config.week&&$D._validate(config.week,0,53,"week")){this.setWeek(config.week);}

return this;};$P.moveToFirstDayOfMonth=function(){return this.set({day:1});};$P.moveToLastDayOfMonth=function(){return this.set({day:$D.getDaysInMonth(this.getFullYear(),this.getMonth())});};$P.moveToNthOccurrence=function(dayOfWeek,occurrence){var shift=0;if(occurrence>0){shift=occurrence-1;}

else if(occurrence===-1){this.moveToLastDayOfMonth();if(this.getDay()!==dayOfWeek){this.moveToDayOfWeek(dayOfWeek,-1);}

return this;}

return this.moveToFirstDayOfMonth().addDays(-1).moveToDayOfWeek(dayOfWeek,+1).addWeeks(shift);};$P.moveToDayOfWeek=function(dayOfWeek,orient){var diff=(dayOfWeek-this.getDay()+7*(orient||+1))%7;return this.addDays((diff===0)?diff+=7*(orient||+1):diff);};$P.moveToMonth=function(month,orient){var diff=(month-this.getMonth()+12*(orient||+1))%12;return this.addMonths((diff===0)?diff+=12*(orient||+1):diff);};$P.getOrdinalNumber=function(){return Math.ceil((this.clone().clearTime()-new Date(this.getFullYear(),0,1))/86400000)+1;};$P.getTimezone=function(){return $D.getTimezoneAbbreviation(this.getUTCOffset());};$P.setTimezoneOffset=function(offset){var here=this.getTimezoneOffset(),there=Number(offset)*-6/10;return this.addMinutes(there-here);};$P.setTimezone=function(offset){return this.setTimezoneOffset($D.getTimezoneOffset(offset));};$P.hasDaylightSavingTime=function(){return(Date.today().set({month:0,day:1}).getTimezoneOffset()!==Date.today().set({month:6,day:1}).getTimezoneOffset());};$P.isDaylightSavingTime=function(){return(this.hasDaylightSavingTime()&&new Date().getTimezoneOffset()===Date.today().set({month:6,day:1}).getTimezoneOffset());};$P.getUTCOffset=function(){var n=this.getTimezoneOffset()*-10/6,r;if(n<0){r=(n-10000).toString();return r.charAt(0)+r.substr(2);}else{r=(n+10000).toString();return"+"+r.substr(1);}};$P.getElapsed=function(date){return(date||new Date())-this;};if(!$P.toISOString){$P.toISOString=function(){function f(n){return n<10?'0'+n:n;}

return'"'+this.getUTCFullYear()+'-'+

f(this.getUTCMonth()+1)+'-'+

f(this.getUTCDate())+'T'+

f(this.getUTCHours())+':'+

f(this.getUTCMinutes())+':'+

f(this.getUTCSeconds())+'Z"';};}

$P._toString=$P.toString;$P.toString=function(format){var x=this;if(format&&format.length==1){var c=$C.formatPatterns;x.t=x.toString;switch(format){case"d":return x.t(c.shortDate);case"D":return x.t(c.longDate);case"F":return x.t(c.fullDateTime);case"m":return x.t(c.monthDay);case"r":return x.t(c.rfc1123);case"s":return x.t(c.sortableDateTime);case"t":return x.t(c.shortTime);case"T":return x.t(c.longTime);case"u":return x.t(c.universalSortableDateTime);case"y":return x.t(c.yearMonth);}}

var ord=function(n){switch(n*1){case 1:case 21:case 31:return"st";case 2:case 22:return"nd";case 3:case 23:return"rd";default:return"th";}};return format?format.replace(/(\\)?(dd?d?d?|MM?M?M?|yy?y?y?|hh?|HH?|mm?|ss?|tt?|S)/g,function(m){if(m.charAt(0)==="\\"){return m.replace("\\","");}

x.h=x.getHours;switch(m){case"hh":return p(x.h()<13?(x.h()===0?12:x.h()):(x.h()-12));case"h":return x.h()<13?(x.h()===0?12:x.h()):(x.h()-12);case"HH":return p(x.h());case"H":return x.h();case"mm":return p(x.getMinutes());case"m":return x.getMinutes();case"ss":return p(x.getSeconds());case"s":return x.getSeconds();case"yyyy":return p(x.getFullYear(),4);case"yy":return p(x.getFullYear());case"dddd":return $C.dayNames[x.getDay()];case"ddd":return $C.abbreviatedDayNames[x.getDay()];case"dd":return p(x.getDate());case"d":return x.getDate();case"MMMM":return $C.monthNames[x.getMonth()];case"MMM":return $C.abbreviatedMonthNames[x.getMonth()];case"MM":return p((x.getMonth()+1));case"M":return x.getMonth()+1;case"t":return x.h()<12?$C.amDesignator.substring(0,1):$C.pmDesignator.substring(0,1);case"tt":return x.h()<12?$C.amDesignator:$C.pmDesignator;case"S":return ord(x.getDate());default:return m;}}):this._toString();};}());

(function(){var $D=Date,$P=$D.prototype,$C=$D.CultureInfo,$N=Number.prototype;$P._orient=+1;$P._nth=null;$P._is=false;$P._same=false;$P._isSecond=false;$N._dateElement="day";$P.next=function(){this._orient=+1;return this;};$D.next=function(){return $D.today().next();};$P.last=$P.prev=$P.previous=function(){this._orient=-1;return this;};$D.last=$D.prev=$D.previous=function(){return $D.today().last();};$P.is=function(){this._is=true;return this;};$P.same=function(){this._same=true;this._isSecond=false;return this;};$P.today=function(){return this.same().day();};$P.weekday=function(){if(this._is){this._is=false;return(!this.is().sat()&&!this.is().sun());}

return false;};$P.at=function(time){return(typeof time==="string")?$D.parse(this.toString("d")+" "+time):this.set(time);};$N.fromNow=$N.after=function(date){var c={};c[this._dateElement]=this;return((!date)?new Date():date.clone()).add(c);};$N.ago=$N.before=function(date){var c={};c[this._dateElement]=this*-1;return((!date)?new Date():date.clone()).add(c);};var dx=("sunday monday tuesday wednesday thursday friday saturday").split(/\s/),mx=("january february march april may june july august september october november december").split(/\s/),px=("Millisecond Second Minute Hour Day Week Month Year").split(/\s/),pxf=("Milliseconds Seconds Minutes Hours Date Week Month FullYear").split(/\s/),nth=("final first second third fourth fifth").split(/\s/),de;$P.toObject=function(){var o={};for(var i=0;i<px.length;i++){o[px[i].toLowerCase()]=this["get"+pxf[i]]();}

return o;};$D.fromObject=function(config){config.week=null;return Date.today().set(config);};var df=function(n){return function(){if(this._is){this._is=false;return this.getDay()==n;}

if(this._nth!==null){if(this._isSecond){this.addSeconds(this._orient*-1);}

this._isSecond=false;var ntemp=this._nth;this._nth=null;var temp=this.clone().moveToLastDayOfMonth();this.moveToNthOccurrence(n,ntemp);if(this>temp){throw new RangeError($D.getDayName(n)+" does not occur "+ntemp+" times in the month of "+$D.getMonthName(temp.getMonth())+" "+temp.getFullYear()+".");}

return this;}

return this.moveToDayOfWeek(n,this._orient);};};var sdf=function(n){return function(){var t=$D.today(),shift=n-t.getDay();if(n===0&&$C.firstDayOfWeek===1&&t.getDay()!==0){shift=shift+7;}

return t.addDays(shift);};};for(var i=0;i<dx.length;i++){$D[dx[i].toUpperCase()]=$D[dx[i].toUpperCase().substring(0,3)]=i;$D[dx[i]]=$D[dx[i].substring(0,3)]=sdf(i);$P[dx[i]]=$P[dx[i].substring(0,3)]=df(i);}

var mf=function(n){return function(){if(this._is){this._is=false;return this.getMonth()===n;}

return this.moveToMonth(n,this._orient);};};var smf=function(n){return function(){return $D.today().set({month:n,day:1});};};for(var j=0;j<mx.length;j++){$D[mx[j].toUpperCase()]=$D[mx[j].toUpperCase().substring(0,3)]=j;$D[mx[j]]=$D[mx[j].substring(0,3)]=smf(j);$P[mx[j]]=$P[mx[j].substring(0,3)]=mf(j);}

var ef=function(j){return function(){if(this._isSecond){this._isSecond=false;return this;}

if(this._same){this._same=this._is=false;var o1=this.toObject(),o2=(arguments[0]||new Date()).toObject(),v="",k=j.toLowerCase();for(var m=(px.length-1);m>-1;m--){v=px[m].toLowerCase();if(o1[v]!=o2[v]){return false;}

if(k==v){break;}}

return true;}

if(j.substring(j.length-1)!="s"){j+="s";}

return this["add"+j](this._orient);};};var nf=function(n){return function(){this._dateElement=n;return this;};};for(var k=0;k<px.length;k++){de=px[k].toLowerCase();$P[de]=$P[de+"s"]=ef(px[k]);$N[de]=$N[de+"s"]=nf(de);}

$P._ss=ef("Second");var nthfn=function(n){return function(dayOfWeek){if(this._same){return this._ss(arguments[0]);}

if(dayOfWeek||dayOfWeek===0){return this.moveToNthOccurrence(dayOfWeek,n);}

this._nth=n;if(n===2&&(dayOfWeek===undefined||dayOfWeek===null)){this._isSecond=true;return this.addSeconds(this._orient);}

return this;};};for(var l=0;l<nth.length;l++){$P[nth[l]]=(l===0)?nthfn(-1):nthfn(l);}}());

(function(){Date.Parsing={Exception:function(s){this.message="Parse error at '"+s.substring(0,10)+" ...'";}};var $P=Date.Parsing;var _=$P.Operators={rtoken:function(r){return function(s){var mx=s.match(r);if(mx){return([mx[0],s.substring(mx[0].length)]);}else{throw new $P.Exception(s);}};},token:function(s){return function(s){return _.rtoken(new RegExp("^\s*"+s+"\s*"))(s);};},stoken:function(s){return _.rtoken(new RegExp("^"+s));},until:function(p){return function(s){var qx=[],rx=null;while(s.length){try{rx=p.call(this,s);}catch(e){qx.push(rx[0]);s=rx[1];continue;}

break;}

return[qx,s];};},many:function(p){return function(s){var rx=[],r=null;while(s.length){try{r=p.call(this,s);}catch(e){return[rx,s];}

rx.push(r[0]);s=r[1];}

return[rx,s];};},optional:function(p){return function(s){var r=null;try{r=p.call(this,s);}catch(e){return[null,s];}

return[r[0],r[1]];};},not:function(p){return function(s){try{p.call(this,s);}catch(e){return[null,s];}

throw new $P.Exception(s);};},ignore:function(p){return p?function(s){var r=null;r=p.call(this,s);return[null,r[1]];}:null;},product:function(){var px=arguments[0],qx=Array.prototype.slice.call(arguments,1),rx=[];for(var i=0;i<px.length;i++){rx.push(_.each(px[i],qx));}

return rx;},cache:function(rule){var cache={},r=null;return function(s){try{r=cache[s]=(cache[s]||rule.call(this,s));}catch(e){r=cache[s]=e;}

if(r instanceof $P.Exception){throw r;}else{return r;}};},any:function(){var px=arguments;return function(s){var r=null;for(var i=0;i<px.length;i++){if(px[i]==null){continue;}

try{r=(px[i].call(this,s));}catch(e){r=null;}

if(r){return r;}}

throw new $P.Exception(s);};},each:function(){var px=arguments;return function(s){var rx=[],r=null;for(var i=0;i<px.length;i++){if(px[i]==null){continue;}

try{r=(px[i].call(this,s));}catch(e){throw new $P.Exception(s);}

rx.push(r[0]);s=r[1];}

return[rx,s];};},all:function(){var px=arguments,_=_;return _.each(_.optional(px));},sequence:function(px,d,c){d=d||_.rtoken(/^\s*/);c=c||null;if(px.length==1){return px[0];}

return function(s){var r=null,q=null;var rx=[];for(var i=0;i<px.length;i++){try{r=px[i].call(this,s);}catch(e){break;}

rx.push(r[0]);try{q=d.call(this,r[1]);}catch(ex){q=null;break;}

s=q[1];}

if(!r){throw new $P.Exception(s);}

if(q){throw new $P.Exception(q[1]);}

if(c){try{r=c.call(this,r[1]);}catch(ey){throw new $P.Exception(r[1]);}}

return[rx,(r?r[1]:s)];};},between:function(d1,p,d2){d2=d2||d1;var _fn=_.each(_.ignore(d1),p,_.ignore(d2));return function(s){var rx=_fn.call(this,s);return[[rx[0][0],r[0][2]],rx[1]];};},list:function(p,d,c){d=d||_.rtoken(/^\s*/);c=c||null;return(p instanceof Array?_.each(_.product(p.slice(0,-1),_.ignore(d)),p.slice(-1),_.ignore(c)):_.each(_.many(_.each(p,_.ignore(d))),px,_.ignore(c)));},set:function(px,d,c){d=d||_.rtoken(/^\s*/);c=c||null;return function(s){var r=null,p=null,q=null,rx=null,best=[[],s],last=false;for(var i=0;i<px.length;i++){q=null;p=null;r=null;last=(px.length==1);try{r=px[i].call(this,s);}catch(e){continue;}

rx=[[r[0]],r[1]];if(r[1].length>0&&!last){try{q=d.call(this,r[1]);}catch(ex){last=true;}}else{last=true;}

if(!last&&q[1].length===0){last=true;}

if(!last){var qx=[];for(var j=0;j<px.length;j++){if(i!=j){qx.push(px[j]);}}

p=_.set(qx,d).call(this,q[1]);if(p[0].length>0){rx[0]=rx[0].concat(p[0]);rx[1]=p[1];}}

if(rx[1].length<best[1].length){best=rx;}

if(best[1].length===0){break;}}

if(best[0].length===0){return best;}

if(c){try{q=c.call(this,best[1]);}catch(ey){throw new $P.Exception(best[1]);}

best[1]=q[1];}

return best;};},forward:function(gr,fname){return function(s){return gr[fname].call(this,s);};},replace:function(rule,repl){return function(s){var r=rule.call(this,s);return[repl,r[1]];};},process:function(rule,fn){return function(s){var r=rule.call(this,s);return[fn.call(this,r[0]),r[1]];};},min:function(min,rule){return function(s){var rx=rule.call(this,s);if(rx[0].length<min){throw new $P.Exception(s);}

return rx;};}};var _generator=function(op){return function(){var args=null,rx=[];if(arguments.length>1){args=Array.prototype.slice.call(arguments);}else if(arguments[0]instanceof Array){args=arguments[0];}

if(args){for(var i=0,px=args.shift();i<px.length;i++){args.unshift(px[i]);rx.push(op.apply(null,args));args.shift();return rx;}}else{return op.apply(null,arguments);}};};var gx="optional not ignore cache".split(/\s/);for(var i=0;i<gx.length;i++){_[gx[i]]=_generator(_[gx[i]]);}

var _vector=function(op){return function(){if(arguments[0]instanceof Array){return op.apply(null,arguments[0]);}else{return op.apply(null,arguments);}};};var vx="each any all".split(/\s/);for(var j=0;j<vx.length;j++){_[vx[j]]=_vector(_[vx[j]]);}}());(function(){var $D=Date,$P=$D.prototype,$C=$D.CultureInfo;var flattenAndCompact=function(ax){var rx=[];for(var i=0;i<ax.length;i++){if(ax[i]instanceof Array){rx=rx.concat(flattenAndCompact(ax[i]));}else{if(ax[i]){rx.push(ax[i]);}}}

return rx;};$D.Grammar={};$D.Translator={hour:function(s){return function(){this.hour=Number(s);};},minute:function(s){return function(){this.minute=Number(s);};},second:function(s){return function(){this.second=Number(s);};},meridian:function(s){return function(){this.meridian=s.slice(0,1).toLowerCase();};},timezone:function(s){return function(){var n=s.replace(/[^\d\+\-]/g,"");if(n.length){this.timezoneOffset=Number(n);}else{this.timezone=s.toLowerCase();}};},day:function(x){var s=x[0];return function(){this.day=Number(s.match(/\d+/)[0]);};},month:function(s){return function(){this.month=(s.length==3)?"jan feb mar apr may jun jul aug sep oct nov dec".indexOf(s)/4:Number(s)-1;};},year:function(s){return function(){var n=Number(s);this.year=((s.length>2)?n:(n+(((n+2000)<$C.twoDigitYearMax)?2000:1900)));};},rday:function(s){return function(){switch(s){case"yesterday":this.days=-1;break;case"tomorrow":this.days=1;break;case"today":this.days=0;break;case"now":this.days=0;this.now=true;break;}};},finishExact:function(x){x=(x instanceof Array)?x:[x];for(var i=0;i<x.length;i++){if(x[i]){x[i].call(this);}}

var now=new Date();if((this.hour||this.minute)&&(!this.month&&!this.year&&!this.day)){this.day=now.getDate();}

if(!this.year){this.year=now.getFullYear();}

if(!this.month&&this.month!==0){this.month=now.getMonth();}

if(!this.day){this.day=1;}

if(!this.hour){this.hour=0;}

if(!this.minute){this.minute=0;}

if(!this.second){this.second=0;}

if(this.meridian&&this.hour){if(this.meridian=="p"&&this.hour<12){this.hour=this.hour+12;}else if(this.meridian=="a"&&this.hour==12){this.hour=0;}}

if(this.day>$D.getDaysInMonth(this.year,this.month)){throw new RangeError(this.day+" is not a valid value for days.");}

var r=new Date(this.year,this.month,this.day,this.hour,this.minute,this.second);if(this.timezone){r.set({timezone:this.timezone});}else if(this.timezoneOffset){r.set({timezoneOffset:this.timezoneOffset});}

return r;},finish:function(x){x=(x instanceof Array)?flattenAndCompact(x):[x];if(x.length===0){return null;}

for(var i=0;i<x.length;i++){if(typeof x[i]=="function"){x[i].call(this);}}

var today=$D.today();if(this.now&&!this.unit&&!this.operator){return new Date();}else if(this.now){today=new Date();}

var expression=!!(this.days&&this.days!==null||this.orient||this.operator);var gap,mod,orient;orient=((this.orient=="past"||this.operator=="subtract")?-1:1);if(!this.now&&"hour minute second".indexOf(this.unit)!=-1){today.setTimeToNow();}

if(this.month||this.month===0){if("year day hour minute second".indexOf(this.unit)!=-1){this.value=this.month+1;this.month=null;expression=true;}}

if(!expression&&this.weekday&&!this.day&&!this.days){var temp=Date[this.weekday]();this.day=temp.getDate();if(!this.month){this.month=temp.getMonth();}

this.year=temp.getFullYear();}

if(expression&&this.weekday&&this.unit!="month"){this.unit="day";gap=($D.getDayNumberFromName(this.weekday)-today.getDay());mod=7;this.days=gap?((gap+(orient*mod))%mod):(orient*mod);}

if(this.month&&this.unit=="day"&&this.operator){this.value=(this.month+1);this.month=null;}

if(this.value!=null&&this.month!=null&&this.year!=null){this.day=this.value*1;}

if(this.month&&!this.day&&this.value){today.set({day:this.value*1});if(!expression){this.day=this.value*1;}}

if(!this.month&&this.value&&this.unit=="month"&&!this.now){this.month=this.value;expression=true;}

if(expression&&(this.month||this.month===0)&&this.unit!="year"){this.unit="month";gap=(this.month-today.getMonth());mod=12;this.months=gap?((gap+(orient*mod))%mod):(orient*mod);this.month=null;}

if(!this.unit){this.unit="day";}

if(!this.value&&this.operator&&this.operator!==null&&this[this.unit+"s"]&&this[this.unit+"s"]!==null){this[this.unit+"s"]=this[this.unit+"s"]+((this.operator=="add")?1:-1)+(this.value||0)*orient;}else if(this[this.unit+"s"]==null||this.operator!=null){if(!this.value){this.value=1;}

this[this.unit+"s"]=this.value*orient;}

if(this.meridian&&this.hour){if(this.meridian=="p"&&this.hour<12){this.hour=this.hour+12;}else if(this.meridian=="a"&&this.hour==12){this.hour=0;}}

if(this.weekday&&!this.day&&!this.days){var temp=Date[this.weekday]();this.day=temp.getDate();if(temp.getMonth()!==today.getMonth()){this.month=temp.getMonth();}}

if((this.month||this.month===0)&&!this.day){this.day=1;}

if(!this.orient&&!this.operator&&this.unit=="week"&&this.value&&!this.day&&!this.month){return Date.today().setWeek(this.value);}

if(expression&&this.timezone&&this.day&&this.days){this.day=this.days;}

return(expression)?today.add(this):today.set(this);}};var _=$D.Parsing.Operators,g=$D.Grammar,t=$D.Translator,_fn;g.datePartDelimiter=_.rtoken(/^([\s\-\.\,\/\x27]+)/);g.timePartDelimiter=_.stoken(":");g.whiteSpace=_.rtoken(/^\s*/);g.generalDelimiter=_.rtoken(/^(([\s\,]|at|@|on)+)/);var _C={};g.ctoken=function(keys){var fn=_C[keys];if(!fn){var c=$C.regexPatterns;var kx=keys.split(/\s+/),px=[];for(var i=0;i<kx.length;i++){px.push(_.replace(_.rtoken(c[kx[i]]),kx[i]));}

fn=_C[keys]=_.any.apply(null,px);}

return fn;};g.ctoken2=function(key){return _.rtoken($C.regexPatterns[key]);};g.h=_.cache(_.process(_.rtoken(/^(0[0-9]|1[0-2]|[1-9])/),t.hour));g.hh=_.cache(_.process(_.rtoken(/^(0[0-9]|1[0-2])/),t.hour));g.H=_.cache(_.process(_.rtoken(/^([0-1][0-9]|2[0-3]|[0-9])/),t.hour));g.HH=_.cache(_.process(_.rtoken(/^([0-1][0-9]|2[0-3])/),t.hour));g.m=_.cache(_.process(_.rtoken(/^([0-5][0-9]|[0-9])/),t.minute));g.mm=_.cache(_.process(_.rtoken(/^[0-5][0-9]/),t.minute));g.s=_.cache(_.process(_.rtoken(/^([0-5][0-9]|[0-9])/),t.second));g.ss=_.cache(_.process(_.rtoken(/^[0-5][0-9]/),t.second));g.hms=_.cache(_.sequence([g.H,g.m,g.s],g.timePartDelimiter));g.t=_.cache(_.process(g.ctoken2("shortMeridian"),t.meridian));g.tt=_.cache(_.process(g.ctoken2("longMeridian"),t.meridian));g.z=_.cache(_.process(_.rtoken(/^((\+|\-)\s*\d\d\d\d)|((\+|\-)\d\d\:?\d\d)/),t.timezone));g.zz=_.cache(_.process(_.rtoken(/^((\+|\-)\s*\d\d\d\d)|((\+|\-)\d\d\:?\d\d)/),t.timezone));g.zzz=_.cache(_.process(g.ctoken2("timezone"),t.timezone));g.timeSuffix=_.each(_.ignore(g.whiteSpace),_.set([g.tt,g.zzz]));g.time=_.each(_.optional(_.ignore(_.stoken("T"))),g.hms,g.timeSuffix);g.d=_.cache(_.process(_.each(_.rtoken(/^([0-2]\d|3[0-1]|\d)/),_.optional(g.ctoken2("ordinalSuffix"))),t.day));g.dd=_.cache(_.process(_.each(_.rtoken(/^([0-2]\d|3[0-1])/),_.optional(g.ctoken2("ordinalSuffix"))),t.day));g.ddd=g.dddd=_.cache(_.process(g.ctoken("sun mon tue wed thu fri sat"),function(s){return function(){this.weekday=s;};}));g.M=_.cache(_.process(_.rtoken(/^(1[0-2]|0\d|\d)/),t.month));g.MM=_.cache(_.process(_.rtoken(/^(1[0-2]|0\d)/),t.month));g.MMM=g.MMMM=_.cache(_.process(g.ctoken("jan feb mar apr may jun jul aug sep oct nov dec"),t.month));g.y=_.cache(_.process(_.rtoken(/^(\d\d?)/),t.year));g.yy=_.cache(_.process(_.rtoken(/^(\d\d)/),t.year));g.yyy=_.cache(_.process(_.rtoken(/^(\d\d?\d?\d?)/),t.year));g.yyyy=_.cache(_.process(_.rtoken(/^(\d\d\d\d)/),t.year));_fn=function(){return _.each(_.any.apply(null,arguments),_.not(g.ctoken2("timeContext")));};g.day=_fn(g.d,g.dd);g.month=_fn(g.M,g.MMM);g.year=_fn(g.yyyy,g.yy);g.orientation=_.process(g.ctoken("past future"),function(s){return function(){this.orient=s;};});g.operator=_.process(g.ctoken("add subtract"),function(s){return function(){this.operator=s;};});g.rday=_.process(g.ctoken("yesterday tomorrow today now"),t.rday);g.unit=_.process(g.ctoken("second minute hour day week month year"),function(s){return function(){this.unit=s;};});g.value=_.process(_.rtoken(/^\d\d?(st|nd|rd|th)?/),function(s){return function(){this.value=s.replace(/\D/g,"");};});g.expression=_.set([g.rday,g.operator,g.value,g.unit,g.orientation,g.ddd,g.MMM]);_fn=function(){return _.set(arguments,g.datePartDelimiter);};g.mdy=_fn(g.ddd,g.month,g.day,g.year);g.ymd=_fn(g.ddd,g.year,g.month,g.day);g.dmy=_fn(g.ddd,g.day,g.month,g.year);g.date=function(s){return((g[$C.dateElementOrder]||g.mdy).call(this,s));};g.format=_.process(_.many(_.any(_.process(_.rtoken(/^(dd?d?d?|MM?M?M?|yy?y?y?|hh?|HH?|mm?|ss?|tt?|zz?z?)/),function(fmt){if(g[fmt]){return g[fmt];}else{throw $D.Parsing.Exception(fmt);}}),_.process(_.rtoken(/^[^dMyhHmstz]+/),function(s){return _.ignore(_.stoken(s));}))),function(rules){return _.process(_.each.apply(null,rules),t.finishExact);});var _F={};var _get=function(f){return _F[f]=(_F[f]||g.format(f)[0]);};g.formats=function(fx){if(fx instanceof Array){var rx=[];for(var i=0;i<fx.length;i++){rx.push(_get(fx[i]));}

return _.any.apply(null,rx);}else{return _get(fx);}};g._formats=g.formats(["\"yyyy-MM-ddTHH:mm:ssZ\"","yyyy-MM-ddTHH:mm:ssZ","yyyy-MM-ddTHH:mm:ssz","yyyy-MM-ddTHH:mm:ss","yyyy-MM-ddTHH:mmZ","yyyy-MM-ddTHH:mmz","yyyy-MM-ddTHH:mm","ddd, MMM dd, yyyy H:mm:ss tt","ddd MMM d yyyy HH:mm:ss zzz","MMddyyyy","ddMMyyyy","Mddyyyy","ddMyyyy","Mdyyyy","dMyyyy","yyyy","Mdyy","dMyy","d"]);g._start=_.process(_.set([g.date,g.time,g.expression],g.generalDelimiter,g.whiteSpace),t.finish);g.start=function(s){try{var r=g._formats.call({},s);if(r[1].length===0){return r;}}catch(e){}

return g._start.call({},s);};$D._parse=$D.parse;$D.parse=function(s){var r=null;if(!s){return null;}

if(s instanceof Date){return s;}

try{r=$D.Grammar.start.call({},s.replace(/^\s*(\S*(\s+\S+)*)\s*$/,"$1"));}catch(e){return null;}

return((r[1].length===0)?r[0]:null);};$D.getParseFunction=function(fx){var fn=$D.Grammar.formats(fx);return function(s){var r=null;try{r=fn.call({},s);}catch(e){return null;}

return((r[1].length===0)?r[0]:null);};};$D.parseExact=function(s,fx){return $D.getParseFunction(fx)(s);};}());

Tuesday, January 11, 2022

Advance data studio and d query function

 =query(data,"select count(A) where A>date'"&H$1&"' and A<date'"&I$1&"' label count(A) ''")

=query(data,"select count(B) where A>date'"&H$1&"' and A<date'"&I$1&"' and B<date'"&I$1&"' label count(B) ''")

=if(G4<>"",round(G4/F4*100-100,2),"")

=query(data,"select count(B) where A>date'"&H$1&"' and A<date'"&I$1&"' and B<date'"&I$1&"' label count(B) ''")

=QUERY(data,"select count(B) where B<=A and A>date'"&H$1&"' and A<date'"&I$1&"' label count(B) ''")

=if(G5<>"",round(G5/F5*100-100,2),"")

=query(data,"select count(B) where B>A and B>date'"&H$1&"' and B<date'"&I$1&"' label count(B) ''")*1/24/4

=QUERY(data,"select sum(C) where B>date'"&H$1&"' and B<date'"&I$1&"' label sum(C) ''")

=if(G6<>"",round(G6/F6*-100,2),"")

hyperlink(concat("https://docs.google.com/forms/d/e/1FAIpQLSf2j2QjJfH4IfMYxl6q8dC_G8UnrswYL1v2B_3Mz9B9_j3Hrw/viewform?usp=pp_url&entry.646173212=",Task ID),"click here")

Thursday, January 6, 2022

Advance Google sheet time period formula

 =if(A7,if(and(hour(A7+F$5)>8,(hour(A7+F$5)<19)),A7+F$5,workday.intl(int(A7),1,"0000001")+hour(A7+F$5-$B$1)/24+minute(A7)/1440),"") FOR 1 HOUR TAT

=if(F7,if(G7<>"",if(G7>F7,G7-F7,""),$A$1-F7),"") FOR DELAY


=if(F7,if(and(hour(F7+K$5)>8,(hour(F7+K$5)<19)),F7+K$5,workday.intl(int(F7),1,"0000001")+hour(F7+K$5-$B$1)/24+minute(F7)/1440),"") FOR 3 HOUR

=if(K7,if(L7<>"",if(L7>K7,L7-K7,""),$A$1-K7),"") DELAY


=if(K8,workday.intl(int(K8),0,"0000001",)+18/24,"") PLAN


=if(O8,if(P8<>"",if(P8>O8,P8-O8,""),$A$1-O8),"") DELAY

Data copy paste on two another sheet with add row in google sheet by script

 function copyDataWithinWorkbook() {   var sourceSheetName = "Dashbord"; // Replace with the name of the source sheet   var target...