Generate sequential numbers in first column based on value in other column

Question

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

}

Answer 1

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

• https://developers.google.com/apps-script/guides/sheets
• https://developers.google.com/apps-script/guides/triggers

Answer 2

If you want to use scripts like this, follow Rubén's advice and learn about scripting before you start copy-pasting code.

Some of the best resources for learning Google Apps Script include the Beginner's Guide, the New Apps Script Editor guide, the Fundamentals of Apps Script with Google Sheets codelab, the Extending Google Sheets page, javascript.info and Mozilla Developer Network.

Here is an example of how to detect the sheet, column and value.

function onEdit(e) {
  const sheetsToWatch = /^(Sheet1|Sheet2|Sheet3)$/i;
  const valueToWatch = /^(OK)$/i;
  const columnToWatch = 3; // column C
  const columnToStamp = 1; // column A
  const sheet = e.range.getSheet();
  const idNumberCell = sheet.getRange('T1');
  if (e.range.columnStart !== columnToWatch
    || !e.value.match(valueToWatch)
    || !sheet.getName().match(sheetsToWatch)
    || sheet.getRange(e.range.rowStart, columnToStamp).getDisplayValue()) {
    return;
  }
  const idNumber = (Number(idNumberCell.getValue()) || 0) + 1;
  idNumberCell.setValue(idNumber);
  sheet.getRange(e.range.rowStart, columnToStamp).setValue(idNumber);
}