Auto-incrementing numbers

Question

I am having trouble with auto incrementing numbers in my googlesheet. Here is what I'm trying to do.

I created a googlesheet for issue tracking. The columns are Issue Status, Group, Issue Number, Priority, etc.... Issue Status is a drop down list with values OPEN and CLOSED. When I select OPEN from the drop-down list I want the ISSUE NUMBER to automatically display in the Issue Number column for that row.

For Example..... I have an empty issue log. I report the first issue by selecting OPEN in the ISSUE STATUS column. The number 1 should automatically populate in the Issue Number column for the first issue.

I report the 2nd issue by selecting OPEN on the next row. The number 2 should automatically populate in the Issue Number column for the second issue.

I report the 3rd issue by selecting OPEN on the next row. The number 3 should automatically populate in the Issue Number column for the second issue.

Currently I am manually entering the number. I want it to automatically populate when selecting OPEN from the list.

Auto Increment Issue Number

The line of code that I'm trying to use for this does not work as I want it to. In my case statement for OPEN, I added the following line

SpreadsheetApp.getActiveSheet().getRange('C5').setValue(SpreadsheetApp.getActiveSheet().getRange('C5').getValue() + 1);

Here's my code:

    function onEdit(event)
{
  var ColA = 1;   // Column Number of "A"
  var ColN = 14;  // Column Number of "N"
  var ColO = 15;  // Column Number of "O"
  var ColP = 16;  // Column Number of "P"
  var changedRange = event.source.getActiveRange();
  if (changedRange.getColumn() == ColA && changedRange.getSheet().getSheetName() == "Issues")
   {
    // An edit has occurred in Column A
    var state = changedRange.getValue();
    var user = Session.getActiveUser().getEmail().substring(0, 2).toUpperCase();
    var DateOpened = event.source.getActiveSheet().getRange(changedRange.getRow(),ColA+9);
    var DateClosed = event.source.getActiveSheet().getRange(changedRange.getRow(),ColA+10);
    var Iteration1 = event.source.getActiveSheet().getRange(changedRange.getRow(),ColA+13);  
    var Iteration2 = event.source.getActiveSheet().getRange(changedRange.getRow(),ColA+14); 
    var Iteration3 = event.source.getActiveSheet().getRange(changedRange.getRow(),ColA+15); 
    var d = new Date(); 
    var timestamp = user + "_" + d.dateNow() + "_" + d.timeNow() + "\n";   
    // Determine the state of Col A
    switch (state)
    {
      case "OPEN":
        SpreadsheetApp.getActiveSheet().getRange('C5').setValue(SpreadsheetApp.getActiveSheet().getRange('C5').getValue() + 1);
        // Write timestamp in Date Opened column
        DateOpened.setValue(timestamp);
        Iteration1.setValue("NOT EXECUTED");
        Iteration2.setValue("NOT EXECUTED");
        Iteration3.setValue("NOT EXECUTED"); 
        break;
      case "CLOSED":
        // Write timestamp in Date Closed column
        DateClosed.setValue(timestamp);
        break
      
    }
  }

Any suggestions?

Answer 1

You could use PropertiesService in order to store and keep track of the issue number. You would have to:

• Retrieve the corresponding script property at the beginning of your code (and set the property to 0 if no issue was created before, with getProperty(key) and setProperty(key, value).
• Get the cell to edit (edited row, column C).
• Increment the script property by 1 and write it to the cell retrieved in last step.

It could be something along the following lines (I deleted the parts that are not directly related to this issue). Please check inline comments for more details:

function onEdit(event){
  // Retrieve previously stored script properties.
  // If not property called 'numIssues' exist, set it to 0:
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.getProperties();
  if(!scriptProperties.getProperty("numIssues")) scriptProperties.setProperty("numIssues", 0);
  // Get event data:
  var ColA = 1;   // Column Number of "A"
  var ColC = 3;   // Column Number of "C"
  var firstIssueRow = 5; // Row at which issues start (please change if necessary)
  var changedRange = event.source.getActiveRange();
  var changedRow = changedRange.getRow();
  var sheet = changedRange.getSheet();
  // Check that edited column, row and sheet are correct:
  if (changedRange.getColumn() == ColA && sheet.getSheetName() == "Issues" && changedRow >= firstIssueRow) {
    var state = changedRange.getValue(); 
    switch (state) {
      case "OPEN":
        var colCRange = sheet.getRange(changedRow, ColC); // Get range to edit
        if (colCRange.getValue() === "") { // Check that cell is empty (otherwise do nothing)
          scriptProperties.setProperty("numIssues", Number(scriptProperties.getProperty("numIssues")) + 1); // Increment 1 to property 'numIssues'
          sheet.getRange(changedRow, ColC).setValue(scriptProperties.getProperty("numIssues")); // Write new property value to cell
        }
        // Write timestamp in Date Opened column
        break;
      case "CLOSED":
        // Write timestamp in Date Closed column
        break      
    }
  }
}

Reference:

• PropertiesService