Google Sheets – Generate Sequential Numbers Based on Another Column

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

I'm having a problem implementing a spreadsheet with several sheets that use data registered continuously on the first.

I found a solution with this script on the forum but I don't know how to edit, I'm a newbie using scripts. It makes a counter in cell T1, every time I edit something he assigns a fixed, sequential number in column A, A2:A1001 called ID. It works for all the cells I edit on every sheets of the spreadsheet. I want it to only work on the first sheet, the numbers appearing in column A, A2:A1001 depending on when I check "OK" in the corresponding cell in column C, C2:C1001, in any order: I mark "OK" in C3, it puts 1 in A3 , mark "OK" in C129, he puts 2 in A129.

Sorry for my English, is not my native language.

function onEdit(e) 

{
   var sheet = SpreadsheetApp.getActiveSheet();
   var row =  SpreadsheetApp.getActiveSheet().getActiveCell().getRow();

   var bugCount = sheet.getRange("T1").getValue();
   bugCount++;

   if (sheet.getRange(row, 1).getValue() == "") {
      sheet.getRange(row,1).setValue(bugCount);
      sheet.getRange("T1").setValue(bugCount);    
   }

}

Best Answer

You should spend some time learning the JavaScript pretty basics like literals, primitives, objects variables, operators, functions among other basic stuff.

Regarding the specifics of Google Apps Script, onEdit is a simple trigger and i has a related event object. In your code the on edit event object is assigned to the variable e. This event object has the property range having assigned a Class Range object corresponding to the edited range. Using the event object instead of methods like getActiveCell() helps to prevent some know issues.

Considering the above, instead of

var sheet = SpreadsheetApp.getActiveSheet();

use

var sheet = e.range.getSheet();

instead of

var row =  SpreadsheetApp.getActiveSheet().getActiveCell().getRow();

use

var row = e.range.rowStart;

To know if the sheet of the edited range is the first sheet, you might use

if(sheet.getName() === e.source.getSheets()[0].getName()){
  // do something
} else {
  return; // only needed if there are more code lines for this function below this line
}

Resources

Related Topic