How to Create Billing app in Google Sheets using AppScript in Hindi ?

 How to Create Billing app in Google Sheets using AppScript in Hindi ?

दोस्तों आज इस आर्टिकल में मैं आपको बताऊंगा कि आप किस तरीके से गूगल सीट में बिलिंग एप (How to Create Billing app in Google Sheets using AppScript in Hindi) क्रिएट कर सकते हैं AppScript की मदद से |

let sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Billingg App');

function selectVendor() {
  let makeDropdownCell = sheet.getRange('D2');
  let rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(["AB Corporation", "MD Brothers", "MJ Corporation"])
    .build();
  makeDropdownCell.setDataValidation(rule)



}

function transporterName() {
  let makeDropdownCell = sheet.getRange('D4');
  let rule = SpreadsheetApp.newDataValidation()
    .requireValueInList(["AB Transport", "MK Travells", "MJ Company"])
    .build();
  makeDropdownCell.setDataValidation(rule)

}

function createBillDate() {
  let nDate = new Date();
  let cDate = nDate.toDateString();
  sheet.getRange('A4').setValue(cDate)
}

function addItemCode() {
  let dataSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data List');
  let makeDropdownCell = sheet.getRange('C6:C12');
  let dropdownList = dataSheet.getRange('B2:B6')
  let rule = SpreadsheetApp.newDataValidation()
    .requireValueInRange(dropdownList)
    .build();
  makeDropdownCell.setDataValidation(rule)
}

function generateSlNo(e) {
  let row = e.range.getRow();
  let col = e.range.getColumn();
  let mySheet = e.source.getActiveSheet().getName();
  if (row >= 6 && col == 3 && mySheet == 'Billingg App' && sheet.getActiveCell().getValue() !== "") {
    sheet.getActiveCell().offset(0, -2).setValue(row - 5)
  } else if(row >= 6 && col == 3 && mySheet == 'Billingg App' && sheet.getActiveCell().getValue() == ""){
    sheet.getActiveCell().offset(0, -2).clearContent();
    sheet.getActiveCell().offset(0, -1).clearContent();
  }

}


function getSheetValue() {
  let targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data List');
  let dataRange = targetSheet.getDataRange().getValues();
  let lookupValue = sheet.getActiveCell().getValue();
  dataRange.forEach(function (cellValue, i) {
    if (cellValue[1] == lookupValue) {
      
      let itemName = targetSheet.getRange(i + 1, 1).getValue();
      Logger.log(itemName)
      let rate = targetSheet.getRange(i + 1, 3).getValue();
      Logger.log(rate)
      sheet.getActiveCell().offset(0, -1).setValue(itemName)
      sheet.getActiveCell().offset(0, 2).setValue(rate);
      sheet.getRange(6, 6, 7, 1).setFormula("=D6*E6")
    }


  })

}

function transferData() {
  let targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("BackUP Data");
  let billNo = sheet.getRange("A2").getValue();
  let billDate = sheet.getRange("A4").getValue();
  let vendorName = sheet.getRange("D2").getValue();
  let biltyNo = sheet.getRange("D3").getValue();
  let transportName = sheet.getRange("D4").getValue();
  let amt = sheet.getRange("F13").getValue();
  let gst = sheet.getRange("F14").getValue();
  let totalAmt = sheet.getRange("F15").getValue();
  let lastrow = sheet.getRange("C5").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  let values = sheet.getRange("B6:F" + lastrow).getValues();
  Logger.log(values)
  
  
   for (var i = 0; i < values.length; i++) {
   let lrow= targetSheet.getLastRow()+1
    targetSheet.getRange(lrow, 1).setValue(billNo);
    targetSheet.getRange(lrow, 2).setValue(billDate)
    targetSheet.getRange(lrow, 3).setValue(vendorName)
    targetSheet.getRange(lrow, 4).setValue(biltyNo)
    targetSheet.getRange(lrow, 5).setValue(transportName)
    targetSheet.getRange(lrow, 6).setValue(values[i][0])
    targetSheet.getRange(lrow, 7).setValue(values[i][1])
    targetSheet.getRange(lrow, 8).setValue(values[i][2])
    targetSheet.getRange(lrow, 9).setValue(values[i][3])
    targetSheet.getRange(lrow, 10).setValue(values[i][4])
    targetSheet.getRange(lrow, 11).setValue(gst)
    targetSheet.getRange(lrow, 12).setValue(totalAmt)
  }
  //clear Content
  sheet.getRange("A2").clearContent();
  sheet.getRange("A4").clearContent();
  sheet.getRange("D2").clearContent();
  sheet.getRange("D3").clearContent();
  sheet.getRange("D4").clearContent();
 
  sheet.getRange("A6:E" + lastrow).clearContent();
   let lr= targetSheet.getLastRow()
  let newBillNo =targetSheet.getRange(lr,1).getValue();
  sheet.getRange("A2").setValue(newBillNo+1);
  createBillDate();
}



function searchData(){
let targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("BackUP Data");
let lrow= targetSheet.getLastRow()-1
let targetValue= targetSheet.getRange("A2:L"+lrow).getDisplayValues();
let searchBillNo= sheet.getRange('F1').getValue();
 let result =targetValue.filter(function(dataArray){
 return dataArray[0]==searchBillNo
});
let output =result.map(function(data){
let billNo = data[0];
let billDate = data[1];
let cName = data[2];
let builtyNo = data[3];
let tname = data[4];
sheet.getRange('A2').setValue(billNo)
sheet.getRange('A4').setValue(billDate)
sheet.getRange('D2').setValue(cName)
sheet.getRange('D3').setValue(builtyNo)
sheet.getRange('D4').setValue(tname)
return [data[5],data[6],data[7],data[8],data[9]]
})
sheet.getRange("B6:F12").clearContent();
sheet.getRange(6,2,output.length,output[0].length).setValues(output)
sheet.getRange("F6:F11").setFormula("=D6*E6")



}


Download File For Practice