Autoincrementing multiple string-number values used for Serial Number on Google Sheets-CodePudding

Good day! I would like to share the code I currently have for this.

const DRH = ("Davao Regional Hospital");
const SPMC = ("Southern Philippines Medical Center");
const KDH = ("Kidapawan Doctors Hospital");
const MSMH = ("Mlang Specialist Medical Hospital");
const MDHCC = ("Mindanao Doctors Hospital and Cancer Center");
const KPH = ("Kabacan Polymedic Hospital");
const DAPH = ("Dr. Aturo Pinggoy Hospital");
const SCH = ("Socsargen County Hospital");
const DDSPH = ("Davao del Sur Provincial Hospital");
const DOPMC = ("Davao Oriental Provincial Medical Center");

const davaoRH = "DVODRH-0";
const davaoSPMC = "DVOSPMC-0";
const davaoKDH = "DVOKDH-0";
const davaoMSMH = "DVOMSMH-0";
const davaoMDHCC = "DVOMDHCC-0";
const davaoKPH = "DVOKPH-0";
const davaoDAPH = "DVODAPH-0";
const davaoSCH = "DVOSCH-0";
const davaoDDSPH = "DVODDSPH-0";
const davaoDOPMC = "DVODOPMC-0";

var numberDRH = 0;
var numberSPMC = 0;
var numberKDH = 0;
var numberMSMH = 0;
var numberMDHCC = 0;
var numberKPH = 0;
var numberDAPH = 0;
var numberSCH = 0;
var numberDDSPH = 0;
var numberDOPMC = 0;


function hospitalTag(){
  const ss=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hospitals");
   var lastrow = ss.getLastRow();
   var columnC = ["C"]
   var hospital = ss.getRange(columnC   lastrow);
   var triggerCancel = hospital.getValue();
  
  for (var i = 0; i < 1; i  ) {
   if (triggerCancel == DRH){
       numberDRH
     ss.getRange(ss.getLastRow(), 19, 1, 1).setValue(davaoRH   numberDRH);
     Logger.log(numberDRH)
     } else if (triggerCancel == SPMC){
       numberSPMC;
     ss.getRange(ss.getLastRow(), 19, 1, 1).setValue(davaoSPMC   numberSPMC);
     } else if (triggerCancel == KDH){
       numberKDH;
     ss.getRange(ss.getLastRow(), 19, 1, 1).setValue(davaoKDH   numberKDH);
     } else if (triggerCancel == MSMH){
       numberMSMH;
     ss.getRange(ss.getLastRow(), 19, 1, 1).setValue(davaoMSMH   numberMSMH);
     } else if (triggerCancel == MDHCC){
       numberMDHCC;
     ss.getRange(ss.getLastRow(), 19, 1, 1).setValue(davaoMDHCC   numberMDHCC);
     } else if (triggerCancel == KPH){
       numberKPH;
     ss.getRange(ss.getLastRow(), 19, 1, 1).setValue(davaoKPH   numberKPH);
     } else if (triggerCancel == DAPH){
       numberDAPH;
     ss.getRange(ss.getLastRow(), 19, 1, 1).setValue(davaoDAPH   numberDAPH);  
     } else if (triggerCancel == SCH){
       numberSCH;
     ss.getRange(ss.getLastRow(), 19, 1, 1).setValue(davaoSCH   numberSCH);
     } else if (triggerCancel == DDSPH){
       numberDDSPH;
     ss.getRange(ss.getLastRow(), 19, 1, 1).setValue(davaoDDSPH   numberDDSPH);
     } else if (triggerCancel == DOPMC){
       numberDOPMC;
     ss.getRange(ss.getLastRow(), 19, 1, 1).setValue(davaoDOPMC   numberDOPMC);
     }
  }
}

Yes I am aware that it is not at all even close to being efficient, which is why I am here.

First of all,

I would like to learn how to keep the value of the number incrementing per run for the ID.
I would like to learn a more efficient way in doing this because runtime is taking super long.

This is currently the output of the code above. It classifies the code to be input depending on the input placed at Column C and uses this to call on the Serial Number for it. But it remains to DVODRH-01 all the time when I want it to be incrementing like DVODRH-01, DVODRH-02, etc.

Things to Note:

There are around 10 Different hospitals which is equal to 10 Codes
I am running it through OnFormSubmit as a Trigger set in Apps Script.

I've been researching for a way for a long time now and haven't gotten a solution for it. A bit of patience and understanding would be much appreciated, Thanks.

CodePudding user response：

SUGGESTION:

Instead of using the native for loop, I tried using forEach() instead and had to refactor the entire script. You may edit this as you like.

function myFunction() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName('Hospitals'); 
  var range = sheet.getRange(1,3, sheet.getLastRow(), 1); 
  var values = range.getValues();

  var drhid = 1; 
  var kphid = 1; 
  var spmcid = 1;
  var kdhid = 1; 
  var msmhid = 1; 
  var mdhccid = 1;
  var daphid = 1; 
  var schid = 1; 
  var ddsphid = 1;
  var dopmcid = 1;

    values.forEach(x => {
    if(x == "Davao Regional Hospital"){
      sheet.getRange(values.indexOf(x)   1,19).setValue("DVODRH-0"   drhid);
      drhid  ;
      console.log(values.indexOf(x)   1);
    } else if(x == "Kabacan Polymedic Hospital"){
      sheet.getRange(values.indexOf(x)   1,19).setValue("DVOKPH-0"   kphid);
      kphid  ;
    } else if(x == "Southern Philippines Medical Center"){
      sheet.getRange(values.indexOf(x)   1,19).setValue("DVOSPMC-0"   spmcid);
      spmcid  ;
    } else if(x == "Kidapawan Doctors Hospital"){
      sheet.getRange(values.indexOf(x)   1,19).setValue("DVOKDH-0"   kdhid);
      kdhid  ;
    } else if(x == "Mlang Specialist Medical Hospital"){
      sheet.getRange(values.indexOf(x)   1,19).setValue("DVOMSMH-0"   msmhid);
      msmhid  ;
    } else if(x == "Mindanao Doctors Hospital and Cancer Center"){
      sheet.getRange(values.indexOf(x)   1,19).setValue("DVOMDHCC-0"   mdhccid);
      mdhccid  ;
    } else if(x == "Dr. Aturo Pinggoy Hospital"){
      sheet.getRange(values.indexOf(x)   1,19).setValue("DVODAPH-0"   daphid);
      daphid  ;
    } else if(x == "Socsargen County Hospital"){
      sheet.getRange(values.indexOf(x)   1,19).setValue("DVOSCH-0"   schid);
      schid  ;
    } else if(x == "Davao del Sur Provincial Hospital"){
      sheet.getRange(values.indexOf(x)   1,19).setValue("DVODDSPH-0"   ddsphid);
      ddsphid  ;
    } else if(x == "Davao Oriental Provincial Medical Center"){
      sheet.getRange(values.indexOf(x)   1,19).setValue("DVODOPMC-0"   dopmcid);
      dopmcid  ;
     }
  });

}

Screenshot of the output:

This output contains 100 rows of data.

Execution duration:

CodePudding user response：

You could improve this code by:

Removing the for loop. It's not doing anything.
Saving ss.getLastRow() to a variable instead of invoking it multiple times.
Use a switch statement instead of a long list of if ... else ...

You can "keep the value of the number incrementing per run for the ID" by reading the index # from the "Control Number".

Here's an example:

const davaoRH = 'DVODRH-0'
const davaoSPMC = 'DVOSPMC-0'
const davaoKDH = 'DVOKDH-0'
const davaoMSMH = 'DVOMSMH-0'
const davaoMDHCC = 'DVOMDHCC-0'
const davaoKPH = 'DVOKPH-0'
const davaoDAPH = 'DVODAPH-0'
const davaoSCH = 'DVOSCH-0'
const davaoDDSPH = 'DVODDSPH-0'
const davaoDOPMC = 'DVODOPMC-0'

function hospitalTag() {
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Hospitals')
  const lastrow = ss.getLastRow()
  const columnC = 3
  const hospital = ss.getRange(lastrow, columnC)
  const triggerCancel = hospital.getValue()

  const controlNumberColumn = 19

  let numberDRH = ss.getRange(<<PUT_ROW_NUMBER_HERE>> controlNumberColumn).getValue()
  let numberSPMC = ss.getRange(<<PUT_ROW_NUMBER_HERE>> controlNumberColumn).getValue()
  let numberKDH = ss.getRange(<<PUT_ROW_NUMBER_HERE>> controlNumberColumn).getValue()
  let numberMSMH = ss.getRange(<<PUT_ROW_NUMBER_HERE>> controlNumberColumn).getValue()
  let numberMDHCC = ss.getRange(<<PUT_ROW_NUMBER_HERE>> controlNumberColumn).getValue()
  let numberKPH = ss.getRange(<<PUT_ROW_NUMBER_HERE>> controlNumberColumn).getValue()
  let numberDAPH = ss.getRange(<<PUT_ROW_NUMBER_HERE>> controlNumberColumn).getValue()
  let numberSCH = ss.getRange(<<PUT_ROW_NUMBER_HERE>> controlNumberColumn).getValue()
  let numberDDSPH = ss.getRange(<<PUT_ROW_NUMBER_HERE>> controlNumberColumn).getValue()
  let numberDOPMC = ss.getRange(<<PUT_ROW_NUMBER_HERE>> controlNumberColumn).getValue()


  switch (triggerCancel) {
    case davaoRH:
        numberDRH
      ss.getRange(lastrow, 19, 1, 1).setValue(davaoRH   numberDRH)
      break
    case davaoSPMC:
        numberSPMC
      ss.getRange(lastrow, 19, 1, 1).setValue(davaoSPMC   numberSPMC)
      break
    case davaoKDH:
        numberKDH
      ss.getRange(lastrow, 19, 1, 1).setValue(davaoKDH   numberKDH)
      break
    case davaoMSMH:
        numberMSMH
      ss.getRange(lastrow, 19, 1, 1).setValue(davaoMSMH   numberMSMH)
      break
    case davaoMDHCC:
        numberMDHCC
      ss.getRange(lastrow, 19, 1, 1).setValue(davaoMDHCC   numberMDHCC)
      break
    case davaoKPH:
        numberKPH
      ss.getRange(lastrow, 19, 1, 1).setValue(davaoKPH   numberKPH)
      break
    case davaoDAPH:
        numberDAPH
      ss.getRange(lastrow, 19, 1, 1).setValue(davaoDAPH   numberDAPH)
      break
    case davaoSCH:
        numberSCH
      ss.getRange(lastrow, 19, 1, 1).setValue(davaoSCH   numberSCH)
      break
    case davaoDDSPH:
        numberDDSPH
      ss.getRange(lastrow, 19, 1, 1).setValue(davaoDDSPH   numberDDSPH)
      break
    case davaoDOPMC:
        numberDOPMC
      ss.getRange(lastrow, 19, 1, 1).setValue(davaoDOPMC   numberDOPMC)
    default: return
  }
}