How to build a HTML search interface in Google Apps Script that fetches data from Google Sheets?

Other Questions Google Sheets
1

I’m having trouble with my Google Apps Script project. The search function in the backend is working fine and returning ‘results’. But the frontend ‘displayFunction’ isn’t getting these ‘results’. This means the search page isn’t working right.

I’ve tried logging the values. The backend search function correctly outputs the data as an array. But the frontend function isn’t getting the right value.

Here’s a simple example of what I’m trying to do:

// Backend
function search(query) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  const data = sheet.getDataRange().getValues();
  const results = data.filter(row => row[0].toLowerCase().includes(query.toLowerCase()));
  console.log(results);
  return results;
}

// Frontend
function handleSearch() {
  const query = document.getElementById('search-input').value;
  google.script.run.withSuccessHandler(displayResults).search(query);
}

function displayResults(results) {
  console.log(results);
  // Code to display results
}

Any ideas on why the frontend might not be getting the results? Or how I can debug this issue?

2

Hey there, JackWolf69! I’ve dealt with similar issues in my GAS projects. One thing that’s helped me is to double-check the permissions and authorization for your script. Sometimes, the frontend can’t access the backend data due to insufficient permissions.

Another trick I’ve found useful is to add a small delay before calling the search function. This can help if there’s any asynchronous loading happening. You could try something like:

function handleSearch() {
  const query = document.getElementById('search-input').value;
  setTimeout(() => {
    google.script.run.withSuccessHandler(displayResults).search(query);
  }, 100);
}

Also, make sure your HTML file includes the necessary Google Apps Script client-side libraries. If all else fails, you might want to consider using the Logger.log() function in your backend code and checking the logs in the Apps Script editor for any clues. Hope this helps!

3

I’ve encountered similar issues before. One potential problem could be the data types of your results. Google Apps Script sometimes struggles with passing complex data structures between server and client-side code. Try stringify-ing your results before returning them from the search function, then parse them in the displayResults function. Like this:

return JSON.stringify(results);

And in displayResults:

const parsedResults = JSON.parse(results);

Also, ensure your HTML file has the correct function calls in the script tags. If that doesn’t work, you might want to add more detailed error logging or use the Chrome developer tools to inspect the network requests and responses between your client and server code.

4

hey jackwolf, sounds lik ur havin a tough time. have u tried using the debugger in the script editor? it can help u see whats goin on step by step. also, make sure ur returning the results properly from the search function. sometimes forgetting a return statement can mess things up. good luck!