Google Apps Script Spreadsheet Auto Increment

Question

In Spreadsheet, If I select ST on 'Trial' column C0001-ST1 will appear in 'Code' column then if I select SP in 'Trial' column C0002-SP1 will appear in 'Code' column then if I again select ST in 'Trial' column C0003-ST2 will appear in 'Code' column.

Step 1:

Step 2

This is the code that I've now to achieve this.

function incTrialCode(sectionCode) { 
    if (sectionCode == ""){ return null; } 

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var range = sheet.getDataRange();
    var values = range.getValues();
    for (i=1; i<values.length; i++)
    { 
        trial = values[i][1];
    } 
    return sectionCode + values;
 }

@Reyman: I'm assuming what you explained above is your desired behaviour. What have you already done to achieve this? Where are you getting stuck? — sudo bangbang
@sudobangbang: Yes, that is what I want to behave. First I want to create a script that will increment C0001, C0002, C0003 and so on. Then create another script to know how many duplicates in 'Trial' column. Then put the two scripts together. — Reynan
@Reyman: Could you share some code? These might come in handy developers.google.com/apps-script/… support.google.com/docs/table/25273?hl=en — sudo bangbang
@sudobangbang: I'm a beginner so i only have this. function incTrialCode(sectionCode) { if (sectionCode == ""){ return null; } var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var range = sheet.getDataRange(); var values = range.getValues(); for (i=1; i<values.length; i++){ trial = values[i][1]; } return sectionCode + values; } — Reynan

Adelin Adelin · Accepted Answer · 2016-03-07T11:07:04

You don't need a script.

You can put this formula in column A ="C"&text(counta(B$2:B2),"0000")&"-"&B2&countif(B$2:B2,B2)

Start at row 2 and drag it down to incorporate all cells. See it in action here.

If you need it explained, let me know.

Google Apps Script Spreadsheet Auto Increment

1 Answers