Google spreadsheet app script - searchGoogle

searchGoogle Readme.md

Goal

Create a Google spreadsheet app script to enable Google search inside the table. I think that's not available in the app script store. If you like you can add it.

Column A contains the seachterm and column B will show the Google search result title, link & snippet.

Result will look like:

Search term	Result
ChatGPT	ChatGPT: Optimizing Language Models for Dialogue - https://openai.com/blog/chatgpt/: Nov 30, 2022 ... We've trained a model called ChatGPT which interacts in a conversational way. The dialogue format makes it possible for ChatGPT to answer ...
OpenAI	OpenAI - https://openai.com/: OpenAI is an AI research and deployment company. Our mission is to ensure that artificial general intelligence benefits all of humanity.
Open Source chat bot	13 Best Open Source Chatbot Platforms to Use in 2022

Required steps

1. Create a Custom search JSON API add Google and obtain the key and context id - https://developers.google.com/custom-search/v1/introduction - For the setup and the context ID have a look here
2. Insert the tokens key & cx in the script
3. Create a spreadsheet with the following content - extend to as many lines as you need:

   Search term	Result
   Anything you like...	=searchGoogle(A2)

App script code

1. Click extensions/Apps Script to open the code editor
2. Copy the following code to a script e.g. name it GoogleSearch Script:

function searchGoogle(searchTerm) {
  const key = "your-api-key-here" // TODO: Is it save to add here? Is there a better place?
  const cx = "your-custom-search-id"
  searchTerm = searchTerm || "Hello world example"; // you could remove the default string here but it's making testing easier.
  var response = UrlFetchApp.fetch(`https://www.googleapis.com/customsearch/v1?key=${key}&cx=${cx}&q=${searchTerm}`);
  var result = JSON.parse(response.getContentText());
  if (result.items) {
    var item = result.items[0];    
    // item.snippet could be used to load some content.
    //return `=HYPERLINK("${item.link}")` // hyperlink can't be returned here :(
    return `${item.title} - ${item.link}: ${item.snippet}`
  } else {
    return "No results found.";
  }
}

3. Publish - make your script available. I think publishing as Library is OK.
4. Go to your spread sheet. Maybe you have to save the sheet but it should update automatically.
5. Your custom app script is ready now.

License

MIT ©2022 A.Wolf