Shortcut to formula

[João Paul]

Hello.

I have no idea if this is possible but I'm using a lot (and I mean a lot) of times the same formula but for different data entries.

The formula is like this:

=INDEX(Recipes!G:G,MATCH(A16,Recipes!C:C,0))

Unfortunately it's a chore to repeat it so many times.

Is there any way to create a "shortcut" for a formula?

something like this would give me the same result

=myShortcut("Recipes", A16))

this is an example as the formulas are bigger and with a few more parameters changing but if it is possible for this example should be able to apply it to a bigger formula.

Thanks in advance

[Tanaike]

I believe your goal is as follows.

- You want to convert a formula of "=INDEX(Recipes!G:G,MATCH(A16,Recipes!C:C,0))" to the custom function of Google Apps Script like "=myShortcut("Recipes", A16))".

In this case, how about the following sample script? Please copy and paste the following script to the script editor of Spreadsheet and save the script.

function myShortcut(sheetName, value) {
  const range = SpreadsheetApp.getActiveSpreadsheet().getRange(`'${sheetName}'!C:C`).createTextFinder(value).findNext();
  return range ? range.offset(0, 4).getValue() : "No value";
}

When you use this script, please put a custom function "=myShortcut("Recipes", A16))" to a cell. By this, the value is returned.

If the custom function is not run, please reopen the Spreadsheet and test it again.

[João Paul]

First of all thanks for the reply.

While my goal is indeed that I was hoping for an inbuilt way of doing it instead of relying on a custom function.

Like I said I'm using a lot of these and in more complex formulas so using a custom function makes me scared for the performance as the values are updated from an API on a relatively small timer.

After all, I'm only trying to make my life easier and I'll avoid creating something that would underperform to avoid some extra work.

I will try to do it like this and see if I notice any performance issues but if anyone know of a way to do it without a custom function let me know please :)

[Tanaike]

Thank you for replying. I deeply apologize I misunderstood your question. This is due to my poor English skill. I would be grateful if you can forgive my poor English skill.