What's the method to retrieve data from Google Sheets using Apps Script?

Other Questions Google drive
1

I’m trying to extract specific cell data from a Google Sheets document through Google Apps Script and send it back as a response when my web page calls the script.

I’ve got the writing part working fine:

function handleRequest(request) {
  var ui = UiApp.createApplication();
  
  SpreadsheetApp.openById(SHEET_ID)
    .getSheetByName('UserData')
    .appendRow([request.parameter.userEmail, 'token456']);
  
  ui.add(ui.createLabel("Data saved. Email: " + request.parameter.userEmail));
  
  return ui;
}

But I’m stuck on how to read values from the sheet and send them back to my website that’s making the call with an AJAX request. Can anyone help me figure out the reading part?

2

hey there! you’ll need to use getRange() and getValue() methods to pull data from cells. try something like var cellData = sheet.getRange('A1').getValue(); then return it as json or text response instead of that UiApp stuff which is pretty outdated now btw

3

You need to switch from UiApp to using doGet() or doPost() functions for proper web app responses. For reading data, use getValues() method on your range. Here’s what worked for me:

function doGet(e) {
  var sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName('UserData');
  var data = sheet.getRange('A:B').getValues();
  
  // Filter for specific user email
  var userEmail = e.parameter.email;
  var result = data.find(row => row[0] === userEmail);
  
  return ContentService
    .createTextOutput(JSON.stringify({data: result}))
    .setMimeType(ContentService.MimeType.JSON);
}

Make sure to deploy as web app with execute permissions set correctly. The ContentService approach handles AJAX requests much better than UiApp which Google deprecated years ago.

4

The main issue with your current approach is that UiApp won’t work for AJAX calls from external websites. You need to restructure your function to handle HTTP requests properly. For reading data, the key method is getRange().getValues() which returns a 2D array of cell values. Here’s a working example that handles both reading and writing:

function doPost(e) {
  var sheet = SpreadsheetApp.openById(SHEET_ID).getSheetByName('UserData');
  var action = e.parameter.action;
  
  if (action === 'read') {
    var email = e.parameter.userEmail;
    var dataRange = sheet.getDataRange();
    var values = dataRange.getValues();
    
    for (var i = 0; i < values.length; i++) {
      if (values[i][0] === email) {
        return ContentService.createTextOutput(JSON.stringify({
          success: true,
          data: values[i]
        })).setMimeType(ContentService.MimeType.JSON);
      }
    }
    
    return ContentService.createTextOutput(JSON.stringify({
      success: false,
      message: 'User not found'
    })).setMimeType(ContentService.MimeType.JSON);
  }
  
  // Your existing write logic here
}

Remember to redeploy your web app after making changes and ensure it’s set to execute as you with access to anyone.