I am very new at writing code and have a limited understanding so please be gentle! I have been trying to expand on the code made on this question.
I have had limited success and am now stuck, I was hoping someone would be kind enough to point me in the right direction or tell me what I am doing wrong.
So, the scenario: A need to have an auto-incrementing "Job Reference" for booking in netbook repairs to the IT dept I work for. This booking is made via a Google Form and I can get the code in the link to work perfectly but! I was hoping to have a little more than a simple count - 1,2,3,4,5 and so on. Ideally the job ref would be displayed as JR0001, JR0002 and so on.
So, my attempt at code!
The code which user: oneself submitted which works perfectly.
function onFormSubmit(e) {
// Get the active sheet
var sheet = SpreadsheetApp.getActiveSheet();
// Get the active row
var row = sheet.getActiveCell().getRowIndex();
// Get the next ID value. NOTE: This cell should be set to: =MAX(A:A)+1
var id = sheet.getRange("P1").getValue();
// Check of ID column is empty
if (sheet.getRange(row, 1).getValue() == "") {
// Set new ID value
sheet.getRange(row, 1).setValue(id);
}
}
The first thing I tried was to simply add another variable and add it to the .setValue
function onFormSubmit(e) {
// Get the active sheet
var sheet = SpreadsheetApp.getActiveSheet();
// Get the active row
var row = sheet.getActiveCell().getRowIndex();
// Get the next ID value. NOTE: This cell should be set to: =MAX(A:A)+1
var id = sheet.getRange("X1").getValue();
// Set Job Reference
var jobref = "JR";
// Check of ID column is empty
if (sheet.getRange(row, 1).getValue() == "") {
// Set new ID value
sheet.getRange(row, 1).setValue(jobref+id);
}
}
This worked as far as getting "JR1" instead of 1 but the auto-increment stopped working so for every form submitted I still had "JR1" - not really auto-increment!
I then tried setting up another .getValue from the sheet as the Job Ref
function onFormSubmit(e) {
// Get the active sheet
var sheet = SpreadsheetApp.getActiveSheet();
// Get the active row
var row = sheet.getActiveCell().getRowIndex();
// Get the next ID value. NOTE: This cell should be set to: =MAX(A:A)+1
var id = sheet.getRange("X1").getValue();
// Set Job Reference
var jobref = sheet.getRange("X2").getValue();
// Check of ID column is empty
if (sheet.getRange(row, 1).getValue() == "") {
// Set new ID value
sheet.getRange(row, 1).setValue(jobref+id);
}
}
Same result - a non incrementing "JR1"
I then tried concatenating the working incrementing number with my job ref cell and then calling that in the script.
function onFormSubmit(e) {
// Get the active sheet
var sheet = SpreadsheetApp.getActiveSheet();
// Set Job Reference
var jobref = sheet.getRange("X2").getValue();
// Get the active row
var row = sheet.getActiveCell().getRowIndex();
// Get the next ID value. NOTE: This cell should be set to: =MAX(A:A)+1
var id = sheet.getRange("X1").getValue();
// Check of ID column is empty
if (sheet.getRange(row, 1).getValue() == "") {
// Set new ID value
sheet.getRange(row, 1).setValue(jobref);
}
}
Same result the value doesn't increment
I don't understand why the number stops incrementing if I add other variables and don't understand how to add the leading zeros to the incrementing number. I get the feeling that I am trying to over complicate things!
So to sum up is there a way of getting an auto-incrementing ref that is 6 characters long - in this scenario first form JR0001 second submit JR0002 and so on.
I would really like some pointers on where I am going wrong if possible, I do want to learn but I am obviously missing some key principles.