Using .indexOf() to define .getRange()

Viewed 82

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 + '")')
1 Answers

yet I am unsure if the ".0" is messing with the next code

No, that's not a problem

Only here I don't get it to work

We can't see your error, or description what goes wrong.

--- Debug Steps: ---

  1. Replace .setFormula with .setValue("Test") to see if you are targeting the right cell (column)

  2. If that works, then the problem is in your formula

Related