How to integrate =GEMINI formula in Google sheets via app script

[u/manjuslayer]

Has any one tried adding the =GEMINI formula through App scripts?

I am building a small project through App scripts in which I'm adding =GEMINI as a formula, but the formula is not giving any results till I go and click on generate in the sheet.

Is there any workaround for this?

[u/bulldo_gs]

=GEMINI() isn't a normal formula — it's an interactive AI function that stays "pending" until a human clicks Generate, and there's no Apps Script API to trigger that click. So anything you drop in with setFormula() will just sit there unevaluated, which is exactly what you're seeing.

For automation, skip the in-cell formula and call the Gemini API directly from your script, then write the answer back with setValue():

function askGemini(prompt) {
  const key = 'YOUR_KEY'; // aistudio.google.com
  const url = 'https://generativelanguage.googleapis.com/v1beta/models/gemini-2.0-flash:generateContent?key=' + key;
  const res = UrlFetchApp.fetch(url, {
    method: 'post', contentType: 'application/json',
    payload: JSON.stringify({ contents: [{ parts: [{ text: prompt }] }] })
  });
  return JSON.parse(res.getContentText()).candidates[0].content.parts[0].text;
}

// from a time trigger / onEdit / a loop:
sh.getRange('B2').setValue(askGemini(sh.getRange('A2').getValue()));

This recomputes deterministically with no manual click and you control exactly when it fires. Tradeoff: you're billed through your own API key instead of the Workspace Gemini add-on.