Google Sheets data retrieval issue in Firebase: 'Billing account not configured' error despite using Google API

Other Questions Google Sheets
1

I’m having trouble getting data from a Google spreadsheet in my Firebase project. The pricing info says Cloud Functions can use Google services for free. But when I try to fetch the data, I get this error:

Error: getaddrinfo ENOTFOUND spreadsheets.google.com spreadsheets.google.com:443
at errnoException (dns.js:28:10)
at GetAddrInfoReqWrap.onlookup [as oncomplete] (dns.js:76:26)

Here’s the code I’m using:

export const pullSpreadsheetData = functions.https.onRequest((req, res) => { 
  const spreadsheetUrl = 'https://spreadsheets.google.com/feeds/list/1234567890/od6/public/values?alt=json'
  https.get(spreadsheetUrl, response => {
    console.log(response)
  })
})

I’m not calling a non-Google API, so why is it blocking me? Is there another way to do this? Any help would be great!

2

I’ve encountered this issue before, and it’s often related to network configuration rather than billing. Firebase functions run in a restricted environment, which can sometimes cause DNS resolution problems.

A workaround I’ve found effective is to use the Google Sheets API with a service account. First, enable the Sheets API in your Google Cloud Console. Then, create a service account and download its JSON key.

In your Firebase function, use the googleapis library to authenticate and fetch data. Here’s a simplified example:

const { google } = require('googleapis');
const sheets = google.sheets('v4');

const serviceAccount = require('./path/to/service-account.json');
const jwtClient = new google.auth.JWT(
  serviceAccount.client_email,
  null,
  serviceAccount.private_key,
  ['https://www.googleapis.com/auth/spreadsheets.readonly']
);

// Use jwtClient for authentication when calling the Sheets API

This approach, in my experience, has been reliable and should resolve the DNS-related error you’re encountering.

3

hey man, i had same issue. try using a service account with google auth jwt. setup in cloud console and enable sheets api.

hope it helps!

4

Hey there, I’ve run into similar issues before when working with Google Sheets in Firebase. From my experience, the error you’re seeing might not actually be related to billing, but rather to how you’re authenticating your request.

Have you tried using the Google Sheets API directly instead of the feed URL? I found that to be more reliable. You’ll need to enable the Sheets API in your Google Cloud Console and set up a service account with the necessary permissions.

Here’s a quick snippet that worked for me:

const { google } = require('googleapis');
const sheets = google.sheets({ version: 'v4' });

// Load your service account key JSON file
const serviceAccount = require('./path/to/your-service-account-key.json');

// Authorize using the service account
const jwtClient = new google.auth.JWT(
  serviceAccount.client_email,
  null,
  serviceAccount.private_key,
  ['https://www.googleapis.com/auth/spreadsheets.readonly']
);

// Use the authorized client to make requests
sheets.spreadsheets.values.get({
  auth: jwtClient,
  spreadsheetId: 'your-spreadsheet-id',
  range: 'Sheet1!A1:B10',
}, (err, response) => {
  if (err) {
    console.error('The API returned an error:', err);
    return;
  }
  const rows = response.data.values;
  console.log('Data:', rows);
});

This approach has been much more stable for me. Hope it helps!