jjones312 jjones312 - 16 days ago 6
Javascript Question

Unique ID counter

I've posted a couple questions over the last few days regarding a Google Apps Script Web App I'm working on. So far Serge on here has been super helpful. The post on that script is here.

I'm looking to add a column in that script to generate a uniqueID counter, so that when a new form is submitted, the counter will see previous uniqueID and increment by 1. Idea would be blank spreadsheet with a header row. The script runs on form submit, see A2 is blank and inserts 1. Next submitted form will see 2 in A2 add 1 and put 3 in A3 and so on.

Right now I'm working on just the counter add 1 to the number in the cell. I know as I develop this piece I'll need to grab last row, then find cell value and add to, but beacuse I'm new at GAS, i'm teaching myself as I go, so I"m trying to build slowly to, well you get the idea.

Here is a basic start and I wanted to get some feedback if this is a right direction to start..

function counter() {
//Grabs the range and pinpoint cell
var ssRange = SpreadsheetApp.getActiveSheet().getRange(1,1,1,1);
//Grab the cell value of A1
var value = ssRange.getValue();
//if statement to check value in A1 and if it is null, undefined, false, 0, NAN
if (!value) {
//if the cell is null, undefined, false, 0, NAN sets value to 1
var setCell = ssRange.setValue(1);
}
//if not updates the cell by getting the value and adding 1 to it
else {
var updateCell = ssRange.setValue(value + 1);
}
}


EDIT (updated code to use Phil Bozak tip of .setProperty & .getProperty

I'm still testing on how this code handles if the MYID that inserted into the spreadsheet gets manually changed as you mentioned. I need to ensure the MYID stay unique. Have to think on that.. Here is the code.. Any feedback is appreciated!

function counter2() {
//Get the spreadsheet, range, and value of first cell in range
var sSheet = SpreadsheetApp.getActiveSheet();
var ssRange = sSheet.getRange(1,1,1,1);
var ssValue = ssRange.getValue();
var setUniqueID = ScriptProperties.setProperty("MYID",1);
var getUniqueID = ScriptProperties.getProperty("MYID");

//will set value of first cell to 1 if null, undefined, false, 0, NAN etc.. (runs in if //statement)
var setOne = ssRange.setValue(getUniqueID);

if (!ssValue) {
setOne;
}
else {

//This goes back and get the range, lastrow of range, and value of first cell in range of last row & adds 1 to that value

var setLast = sSheet.getRange(lastRow+1,1,1,1).setValue(getUniqueID + 1);
setLast;
}
}

Answer

Another way to be sure to get a value that is always the last count (the highest value) as counter in a column is like that : (comments in code)

I had this idea when you said that values in column A could possibly be modified manually... in this case it is the only approach that avoids possible duplicates (it does not prevent having unused values though... (but this could be implemented too if necessary)

function OnForm(){
var sh = SpreadsheetApp.getActiveSheet()
var startcell = sh.getRange('A1').getValue();
if(! startcell){sh.getRange('A1').setValue(1);return}; // this is only to handle initial situation when A1 is empty.
var colValues = sh.getRange('A1:A').getValues();// get all the values in column A in an array
var max=0;// define the max variable to a minimal value
  for(var r in colValues){ // iterate the array
    if(Number(colValues[r][0]>max)){max=colValues[r][0]};// get the highest numeric value in th column, no matter what happens in the column... this runs at array level so it is very fast
    }
    max++ ; // increment to be 1 above max value
sh.getRange(sh.getLastRow()+1, 1).setValue(max);// and write it back to sheet's last row.
}
Comments