如何在远程服务器上通过Node.js实现Google Sheets API用户认证？

Hey there! I’ve been in your exact situation before—getting the Google Sheets API working locally is straightforward, but deploying to Heroku (or any remote server) breaks the authorization flow because there’s no interactive browser to approve access. Let’s break down two solid solutions: adapting the official quickstart for offline OAuth2, or integrating the service account approach from that codelab.

Method 1: Adapt the Official Quickstart for Offline OAuth2 #

This approach uses your existing local authorization flow but adds offline access to get a refresh token, which lets your remote server automatically refresh access tokens without user input.

Step 1: Update the OAuth2 Configuration in the Quickstart Code #

In the official quickstart, the authorization URL doesn’t request offline access. Modify the code to add these critical parameters:

const { google } = require('googleapis');
const fs = require('fs');
const readline = require('readline');

const SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'];
const TOKEN_PATH = 'token.json';

// Load client secrets from a local file.
fs.readFile('credentials.json', (err, content) => {
  if (err) return console.log('Error loading client secret file:', err);
  // Authorize a client with credentials, then call the Google Sheets API.
  authorize(JSON.parse(content), listMajors);
});

/**
 * Create an OAuth2 client with the given credentials, and then execute the
 * given callback function.
 * @param {Object} credentials The authorization client credentials.
 * @param {function} callback The callback to call with the authorized client.
 */
function authorize(credentials, callback) {
  const {client_secret, client_id, redirect_uris} = credentials.installed;
  const oAuth2Client = new google.auth.OAuth2(
      client_id, client_secret, redirect_uris[0]);

  // Check if we have previously stored a token.
  fs.readFile(TOKEN_PATH, (err, token) => {
    if (err) return getNewToken(oAuth2Client, callback);
    oAuth2Client.setCredentials(JSON.parse(token));
    callback(oAuth2Client);
  });
}

/**
 * Get and store new token after prompting for user authorization, and then
 * execute the given callback with the authorized OAuth2 client.
 * @param {google.auth.OAuth2} oAuth2Client The OAuth2 client to get token for.
 * @param {getEventsCallback} callback The callback for the authorized client.
 */
function getNewToken(oAuth2Client, callback) {
  // MODIFY THIS LINE TO ADD OFFLINE ACCESS
  const authUrl = oAuth2Client.generateAuthUrl({
    access_type: 'offline', // Critical: Gets a refresh token
    prompt: 'consent', // Forces refresh token to be issued even if previously authorized
    scope: SCOPES,
  });
  console.log('Authorize this app by visiting this url:', authUrl);
  const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout,
  });
  rl.question('Enter the code from that page here: ', (code) => {
    rl.close();
    oAuth2Client.getToken(code, (err, token) => {
      if (err) return console.error('Error while trying to retrieve access token', err);
      oAuth2Client.setCredentials(token);
      // Store the token to disk for later program executions
      fs.writeFile(TOKEN_PATH, JSON.stringify(token), (err) => {
        if (err) return console.error(err);
        console.log('Token stored to', TOKEN_PATH);
      });
      callback(oAuth2Client);
    });
  });
}

The key changes are the access_type: 'offline' and prompt: 'consent' parameters—these ensure you get a refresh_token in your token.json file.

Step 2: Generate the Refresh Token Locally #

Run the modified quickstart code locally again. Complete the authorization flow as before, and now your token.json will include a refresh_token field. This token lets your remote server get new access tokens automatically when the old one expires.

Step 3: Deploy the Token to Heroku #

Never commit token.json to a public repo! Instead, store its content in a Heroku environment variable:

1. Copy the entire JSON content of token.json
2. In your Heroku dashboard, go to your app → Settings → Config Vars
3. Add a new var named GOOGLE_TOKEN and paste the JSON content as the value

Then update your code to read the token from the environment variable instead of the file:

function authorize(credentials, callback) {
  const {client_secret, client_id, redirect_uris} = credentials.installed;
  const oAuth2Client = new google.auth.OAuth2(
      client_id, client_secret, redirect_uris[0]);

  // Check for token in environment variable first
  let token;
  if (process.env.GOOGLE_TOKEN) {
    token = JSON.parse(process.env.GOOGLE_TOKEN);
  } else {
    // Fallback to local file for development
    try {
      token = JSON.parse(fs.readFileSync(TOKEN_PATH));
    } catch (err) {
      return getNewToken(oAuth2Client, callback);
    }
  }

  oAuth2Client.setCredentials(token);
  callback(oAuth2Client);
}

Method 2: Use a Service Account (From the Codelab) #

This is the cleaner approach for server-side apps—no user authorization required. The service account acts as a separate Google identity that you grant access to your Sheet.

Step 1: Create a Service Account and Download the Key #

1. Go to the Google Cloud Console for your project
2. Navigate to IAM & Admin → Service Accounts
3. Create a new service account, then click "Add Key" → "Create New Key" → JSON
4. Save the downloaded JSON file (keep it secure—never commit it to repo!)

Step 2: Share Your Google Sheet with the Service Account #

Open your Google Sheet, click "Share", and paste the service account’s email (found in the JSON key under client_email). Grant it the appropriate permissions (view/edit).

Step 3: Replace the Quickstart Authorization with Service Account Auth #

Rewrite the quickstart code to use the service account instead of OAuth2:

const { google } = require('googleapis');

// Load service account credentials from environment variable
const credentials = JSON.parse(process.env.GOOGLE_SERVICE_ACCOUNT_KEY);

// Create JWT auth client
const auth = new google.auth.JWT(
  credentials.client_email,
  null,
  credentials.private_key,
  ['https://www.googleapis.com/auth/spreadsheets'], // Adjust scope as needed
);

// Initialize Sheets API
const sheets = google.sheets({ version: 'v4', auth });

// Example function (same as quickstart)
async function listMajors() {
  const res = await sheets.spreadsheets.values.get({
    spreadsheetId: 'YOUR_SPREADSHEET_ID',
    range: 'Class Data!A2:E',
  });
  const rows = res.data.values;
  if (!rows || rows.length === 0) {
    console.log('No data found.');
    return;
  }
  console.log('Name, Major:');
  rows.forEach((row) => {
    console.log(`${row[0]}, ${row[4]}`);
  });
}

listMajors().catch(console.error);

Step 4: Deploy to Heroku #

1. In your Heroku app’s Config Vars, add GOOGLE_SERVICE_ACCOUNT_KEY and paste the entire content of your service account JSON key as the value
2. Push your updated code to Heroku—no more authorization prompts!

Which Method Should You Choose? #

• Offline OAuth2: Use this if your app needs to access Sheets owned by individual users (you’ll need each user to authorize once locally to get their refresh token)
• Service Account: Use this if your app accesses a single Sheet (or set of Sheets) owned by your project/organization—no user interaction required, perfect for server-side apps

内容的提问来源于stack exchange，提问作者Suyash Doneria