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

Question

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);
     }
  }
}

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.

What new things can I try?

Century Tuna · Accepted Answer

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:

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

Answers (2)

Related Questions