2

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 + '")')
2
  • 1
    Please add a minimal reproducible example. Commented Mar 29, 2021 at 17:14
  • 1
    What is the error code? have you tried using parseInt(descriptionColumn + 1) to your sefFormula range? Commented Mar 29, 2021 at 18:46

1 Answer 1

1

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

Sign up to request clarification or add additional context in comments.

1 Comment

That did indeed help me a lot, although I still had to get the other issue out of the way first :). THANKS!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.