I am currently trying to use the .indexOf of a .getValues() array value, to define the cell I want a hyperlink copied in (as proof of concept).
Code looks as follows:
var descriptionColumn = headerArray[0].indexOf("Description")
sheet.getRange(sheet.getLastRow() + 1, descriptionColumn + 1).setFormula('=HYPERLINK("www.google.com"; "Test")')
Logger.log(descriptionColumn)
The headerArray is filled as follows:
var headerArray = sheet.getRange(1, 1, 1, lastColumn).getValues();
Now the log is showing me, that the descriptionColumn is being returned as "3.0". The position 3 is correct, yet I am unsure if the ".0" is messing with the next code. Because whenever I replace descriptionColumn in the .setFormula area, my code works. Any idea what I'm doing wrong and how to fix it? I am using .indexOf() in other places to refer to the index of another array without issue. Only here I don't get it to work..
EDIT: Reproducible Example and findings. Thanks a lot for all your help so far, and sorry for coming back so late!!
I have in the meantime found out, that one of the issues was the "+ 1" after sheet.getLastRow() in
sheet.getRange(sheet.getLastRow() + 1, descriptionColumn + 1).setFormula('=HYPERLINK("www.google.com"; "Test")')
I honestly don't fully understand why this is the case yet, but by positioning this at the very end of my code (so that it is indeed the lastRow when inserted, I was able to fix this (saving it in a separate variable such as with "descriptionColumn" also didn't help..). I understood .getLastRow returns an Integer, so the + 1 operation in theory shouldn't be an issue, right? Happy to learn if I got something wrong.
So finally with below code I was able to insert the hyperlink as planned. Indeed starting with .setValue instead of .setFormula did help me a lot, so thanks!! :)
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var headerArray = sheet.getRange(1, 1, 1, lastColumn).getValues();
var projectRef = "TEST123"
var folderID = DriveApp.getFolderById("SOME FOLDERID").createFolder(projectRef).getId()
var descriptionColumn = parseInt(headerArray[0].indexOf("Description"))
sheet.getRange(sheet.getLastRow(), descriptionColumn + 1).setFormula('=HYPERLINK("https://drive.google.com/drive/folders/' + folderID + '"; "' + projectRef + '")')
parseInt(descriptionColumn + 1)to your sefFormula range?