0

I wrote a Google sheet app script that requires some API calls. When I run the script within App Script editor, it runs fine.

But when I call the formula from within Google Sheets, then it throws an error saying "invalid API key" while I know that API is correct.

    const api_key = "";

    const response = UrlFetchApp.fetch("https://serpapi.com/search.json?q=" + keyword 
 +"&hl="+"&gl="+location+"&num=100"+ "&engine = Google" + 
"&google_domain=google.co.uk"+"&api_key="+api_key);

Here is the whole script

    function GetRank(keyword, domain, location) {

  

  

const response = UrlFetchApp.fetch("https://serpapi.com/search.json?q=" + keyword +"&hl="+"&gl="+location+"&num=100"+ "&engine = Google" + "&google_domain=google.co.uk"+"&api_key=xxx-xxx-xxx");

 

  const json = response.getContentText();
  const results = JSON.parse(json);
  const final = results.organic_results;
  for(let i = 0; i< final.length; i++){
   rank = 'Not in Top 100';
   links = final[i].link;
   if(links.includes(domain)){
     rank = final[i].position;
     show = final[i].link;
     break;
     
   }

  }
  console.log(rank, show);
  return [rank, show];

}
Arhaan
  • 1
  • 1
  • Does this answer your question? [How to add an API Key to a UrlFetchApp in Google Apps Scripts](https://stackoverflow.com/questions/51907871/how-to-add-an-api-key-to-a-urlfetchapp-in-google-apps-scripts) – jasie Apr 11 '22 at 11:58
  • In order to correctly understand your situation, can you provide your whole script by removing your API key? And also, can you provide the formula for calling the custom function for replicating `when I call the formula from within Google Sheets`? – Tanaike Apr 11 '22 at 12:00
  • @Tanaike I have edited the code, I have pasted entire code. – Arhaan Apr 12 '22 at 04:30
  • So It turns out that Google sheet does not allow app scripts that require user permission. I tried same script but this time using a custom drawing and attaching it to the script. Now It works! – Arhaan Apr 12 '22 at 06:07
  • Thank you for replying. I'm glad your issue was resolved. – Tanaike Apr 12 '22 at 08:10

0 Answers0