Google Sheets - Formula of Formulas

You forgot a formula? Don’t worry, we have a formula for that.

Here, we will create a custom function, using Google Apps Script, that we can use in our Google Sheets.

In Google Sheets, click Extensions > Apps Script.

You will see a screen like this. This is a Code.gs file where we can put Apps Script codes.

Copy this Code.gs file and paste it to replace everything in your Code.gs.

Your Code.gs file will now look something like this.

The formula name is KLFORMULA. You can change it however you want.

Click save.

Groq API

Open a new tab and go to console.groq.com

Login however you want.

Once logged in, go to API Keys.

Click Create API Key.

Enter any name you want then click Submit.

Click Copy and paste it somewhere because as indicated, it will not be displayed again. Thus, it cannot be copied later.

Go back to Apps Script. On the left side, go to Project Settings.

At the bottom, click Add script property.

In the Property field, enter apiKey. In the Value field, enter the copied API key. Then click Save script properties.

Back to Google Sheets, you can now use the function KLFORMULA (or whatever function name you changed it to).

This function accepts a cell or range of cells as its first argument. The second argument is the query you want to ask or to get from your data. Here are some examples:

Take note that in the last example, the result we want is separated by two pipes ||. Thus, we can use the SPLIT formula to extract them.

If you are not yet aware, we are using an LLM (AI) in this formula. Thus, the result is not deterministic. Meaning, you can get different answers at different times. But we are not here for a perfect solution. We are here to use the power of AI to our advantage. I hope that you can see its usefulness and use it in your own use case.

You can check the prompt in the Apps Script code and modify it however you want to suit your needs.

In addition, because we are using an API, and it’s free, there’s, of course, a limit. Check the API usage limit here. In this example, we used the llama3-70b-8192 model. And because there’s an API limit, the problem we have here is the “recalculation” of values in Google Sheets. If the formula recalculates, that means a request to the API will be made. And thus, will consume our API usage limit. And so, I recommend that once you’ve used the formula, paste the result as values so that the cell won’t contain the formula anymore and thus, won’t recalculate. Or if you have any better solution for a cell not to recalculate (specifically for this formula that we created), do it, and also, let me know. :)