Getting a Google API refresh token running on a non-local host

Question

While testing my application at http://localhost:3000/ I was able to set up refresh_token and access_token generation. And my files attached in the form to send data are successfully sent to google drive.

Here is all my code

import formidable from 'formidable';
import fs from 'fs';
import { google } from 'googleapis'
const stream = require('stream');

const CLIENT_ID = process.env.CLIENT_ID
const CLIENT_SECRET = process.env.CLIENT_SECRET
const REDIRECT_URI = "urn:ietf:wg:oauth:2.0:oob"
let REFRESH_TOKEN = process.env.REFRESH_TOKEN
let ACCESS_TOKEN = process.env.ACCESS_TOKEN

let bodyContent = `client_id=${CLIENT_ID}&client_secret=${CLIENT_SECRET}&grant_type=refresh_token&refresh_token=${REFRESH_TOKEN}`;

const getTokens = () => {
    fetch("https://oauth2.googleapis.com/token", {
        method: 'POST',
        headers: {
            'Content-Type': 'application/x-www-form-urlencoded'
        },
        body: bodyContent,
    })
        .then(response => {
            if (response.ok) {
                console.log(response)
                return response.json();
            } else {
                throw new Error('Ошибка при выполнении запроса');
            }
        })
        .then(data => {
            console.log(data.access_token)
            ACCESS_TOKEN = data.access_token;
        })
        .catch(error => {
            console.error('Произошла ошибка:', error);
        });
}

getTokens()

const auth = new google.auth.OAuth2({
    CLIENT_ID,
    CLIENT_SECRET,
    REDIRECT_URI,
})

auth.setCredentials({ refresh_token: REFRESH_TOKEN, access_token: ACCESS_TOKEN })

const drive = google.drive({ version: 'v3', auth });

async function uploadFiles(file) {
    const fileContents = fs.readFileSync(`public/${file.name}`)
    const bufferStream = new stream.PassThrough()
    bufferStream.end(fileContents)

    try {
        const response = await drive.files.create({
            media: {
                mimeType: 'application/pdf',
                body: bufferStream
            },
            requestBody: {
                name: file.name,
                parents: ["1WZj-6PV6sschMMBUFZax-O8ImCqTE7uC"]
            },
            fields: 'id,name'

        })
        console.log(response.data)
        return response.data.id;

    } catch (error) {
        console.error(error);
    }
}

async function sendDataToGoogleSheets(body, url) {
    const data = JSON.parse(body.formValues);

    const sheets = google.sheets({
        version: 'v4', auth
    })

    const urlLink = url ? `https://drive.google.com/file/d/${url}/view` : "Нет приложенного файла"
    try {
        const response = await sheets.spreadsheets.values.append({
            spreadsheetId: "1AR32ZlUSU03QHPwRV64LyPmwhIlH5vGmLg60Ouq5mhI",
            range: "A1:F1",
            valueInputOption: "USER_ENTERED",
            requestBody: {
                values: [
                    [data.name, data.email, data.phoneNumber, data.comment, data.resumeLink, urlLink]
                ]
            }
        })

        return response.data

    } catch (error) {
        console.log(error.message)
    }
}

const post = async (req, res) => {
    const form = new formidable.IncomingForm();

    form.parse(req, async function (err, fields, files) {
        let file = files ? files.file : null;
        try {
            if (file) {
                await saveFile(file).catch(console.error);
                const url = await uploadFiles(file).catch(console.error);
                await sendDataToGoogleSheets(fields, url).catch(console.error);

                await removeFile(file.name).catch(console.error);
            } else {
                await sendDataToGoogleSheets(fields).catch(console.error);
            }
        } catch (error) {
            console.log('Failed to do shit', error.message);
            res.status(500).end('Unexpected error');
        }
        if (err) {
            res.status(err.statusCode || 500).send(err.message);
        } else {
            res.status(201).send('');
        }
    });
};

const saveFile = async (file) => {
    const data = fs.readFileSync(file.path);
    fs.writeFileSync(`./public/${file.name}`, data, console.log('file saved'));
    await fs.unlinkSync(file.path);
    return;
};

const removeFile = async (fileName) => {
    await fs.unlink(`./public/${fileName}`, () => {
        console.log(`file ${fileName} removed`);
    });
    return;
};

const reqRes = (req, res) => {
    req.method === 'POST'
        ? post(req, res)
        : req.method === 'PUT'
            ? console.log('PUT')
            : req.method === 'DELETE'
                ? console.log('DELETE')
                : req.method === 'GET'
                    ? console.log('GET')
                    : res.status(404).send('');
};
export default reqRes;
export { config };

Then I deployed my test application to https://vercel.com/ and the files stopped being sent to google drive.

My google spreadsheets screenshot

The file is not sent to the Google spreadsheet, but the Google spreadsheet itself is filled

I tried to generate new refresh_token and access_token for the domain that vercel gave me. But in response I get only access_token. { "access_token": "ХХХХХ", "expires_in": 3599, "scope": "https://www.googleapis.com/auth/drive", "token_type": "Bearer" }

In my code, to send a file to google drive, it is necessary to set the refresh_token and access_token, and this is how I pass them in the code auth.setCredentials({ refresh_token: REFRESH_TOKEN, access_token: AСCESS_TOKEN })

But I don't receive a refresh_token at all for an application running on https://vercel.com/.

Who can tell me how to get all the necessary tokens correctly so that I can use it not only on the local host

Answer 1

I am not really sure how your code is written or in which framework. But I have tried to implement minimal working example of your code in Express.js using .mjs extension to enable ES6 import statements. Here is my server.mjs which is run using node server.mjs command:

import express from 'express';
const app = express();
import fs from 'fs';
import { google } from 'googleapis'
import stream from 'stream';
import fetch from 'node-fetch';
import formidable from 'formidable';

const CLIENT_ID = process.env.CLIENT_ID
const CLIENT_SECRET = process.env.CLIENT_SECRET
const REDIRECT_URI = "urn:ietf:wg:oauth:2.0:oob"
let REFRESH_TOKEN = process.env.REFRESH_TOKEN
let ACCESS_TOKEN = process.env.ACCESS_TOKEN

let bodyContent = `client_id=${CLIENT_ID}&client_secret=${CLIENT_SECRET}&grant_type=refresh_token&refresh_token=${REFRESH_TOKEN}`;

const getTokens = () => {
    fetch("https://oauth2.googleapis.com/token", {
        method: 'POST',
        headers: {
            'Content-Type': 'application/x-www-form-urlencoded'
        },
        body: bodyContent,
    })
        .then(response => {
            if (response.ok) {
                // console.log(response)
                return response.json();
            } else {
                // console.log(response.status);
                // response.json().then(z => console.log(z));
                throw new Error('Ошибка при выполнении запроса');
            }
        })
        .then(data => {
            console.log(data.access_token)
            ACCESS_TOKEN = data.access_token;
        })
        .catch(error => {
            console.error('Произошла ошибка:', error);
        });
}

getTokens()

console.log(`CLIENT_ID: ${CLIENT_ID}`);
console.log(`CLIENT_SECRET: ${CLIENT_SECRET}`);
console.log(`REDIRECT_URI: ${REDIRECT_URI}`);

const auth = new google.auth.OAuth2({
    clientId: CLIENT_ID,
    clientSecret: CLIENT_SECRET,
    redirectUri: REDIRECT_URI
})

auth.setCredentials({ refresh_token: REFRESH_TOKEN, access_token: ACCESS_TOKEN })

const drive = google.drive({ version: 'v3', auth });

async function uploadFiles(file) {
    const fileContents = fs.readFileSync(`public/${file.name}`)
    const bufferStream = new stream.PassThrough()
    bufferStream.end(fileContents)

    try {
        const response = await drive.files.create({
            media: {
                mimeType: 'application/pdf',
                body: bufferStream
            },
            requestBody: {
                name: file.name,
                parents: ["1WZj-6PV6sschMMBUFZax-O8ImCqTE7uC"]
            },
            fields: 'id,name'

        })
        console.log(response.data)
        return response.data.id;

    } catch (error) {
        console.error(error);
    }
}

async function sendDataToGoogleSheets(body, url) {
    const data = JSON.parse(body.formValues);
    console.log(data);
    console.log(url);

    const sheets = google.sheets({
        version: 'v4', auth
    })

    const urlLink = url ? `https://drive.google.com/file/d/${url}/view` : "Нет приложенного файла"
    try {
        const response = await sheets.spreadsheets.values.append({
            spreadsheetId: "1AR32ZlUSU03QHPwRV64LyPmwhIlH5vGmLg60Ouq5mhI",
            range: "A1:F1",
            valueInputOption: "USER_ENTERED",
            requestBody: {
                values: [
                    [data.name, data.email, data.phoneNumber, data.comment, data.resumeLink, urlLink]
                ]
            }
        })

        return response.data

    } catch (error) {
        console.log(error);
        console.log(error.message)
    }
}

const post = async (req, res) => {
    const form = formidable();

    form.parse(req, async function (err, fields, files) {
        console.log(fields);
        let file = files ? files.file : null;
        try {
            if (file) {
                await saveFile(file).catch(console.error);
                const url = await uploadFiles(file).catch(console.error);
                await sendDataToGoogleSheets(fields, url).catch(console.error);

                await removeFile(file.name).catch(console.error);
            } else {
                await sendDataToGoogleSheets(fields).catch(console.error);
            }
        } catch (error) {
            console.log('Failed to do shit', error.message);
            res.status(500).end('Unexpected error');
        }
        if (err) {
            res.status(err.statusCode || 500).send(err.message);
        } else {
            res.status(201).send('');
        }
    });
};

const saveFile = async (file) => {
    console.log(file.path, file.name);
    const data = fs.readFileSync(file.path);
    fs.writeFileSync(`./public/${file.name}`, data, console.log('file saved'));
    await fs.unlinkSync(file.path);
    return;
};

const removeFile = async (fileName) => {
    await fs.unlink(`./public/${fileName}`, () => {
        console.log(`file ${fileName} removed`);
    });
    return;
};

const reqRes = (req, res) => {
    req.method === 'POST'
        ? post(req, res)
        : req.method === 'PUT'
            ? console.log('PUT')
            : req.method === 'DELETE'
                ? console.log('DELETE')
                : req.method === 'GET'
                    ? console.log('GET')
                    : res.status(404).send('');
};
// export default reqRes;
// export { config };


app.use('/api', reqRes);

const port = process.env.PORT || 4000;
app.listen(port, () => {
    console.log(`Node server is listening on port ${port}`);
});

The above code is a local working example, you need to provide:

• CLIENT_ID, CLIENT_SECRET (taken both from google console)
• ACCESS_TOKEN, REFRESH_TOKEN (taken both from OAuth playground by setting Redirect Url as https://developers.google.com/oauthplayground on google console and providing clientId and clientSecret by clicking gear icon on playground)
• spreadsheetId (taken from google sheet url after creating a sheet on your drive).

After running the above code, One can call the API from postman (or any client of choice) with form-data with key as formValues and value as stringified JSON. The following is sample cURL to call the API:

curl --location --request POST 'http://localhost:4000/api' \
--form 'formValues="{\"name\":\"KK\",\"email\":\"KK@KK.com\",\"phoneNumber\":\"5467586795\",\"comment\":\"my comment\",\"resumeLink\":\"http://myresume.com\"}"'

---

Possible Solutions

Pass clientId, clientSecret, redirectUri properly

I would say I have changed following line to make it work locally:

const auth = new google.auth.OAuth2({
    clientId: CLIENT_ID,
    clientSecret: CLIENT_SECRET,
    redirectUri: REDIRECT_URI
})

Because the clientId, clientSecret and redirectUri was not being passed to const drive = google.drive({ version: 'v3', auth }); drive object.

Pass environment variables to vercel deployment

Another reason this might not be working if you are not passing CLIENT_ID, CLIENT_SECRET, ACCESS_TOKEN, and REFRESH_TOKEN via environment variable since your code expects this information via environment variables, which you might need to provide in vercel deployment. You can check here about passing environment variables.

Check logs on vercel deployment and while the deployment is running

Also one observation is that the code is not written in a way to catch all the errors and conditions properly, you will get 201 even if it fails in the background, you might need to check vercel logs, I believe you can follow this and this to get logs and understand the issue better.

Refresh tokens are not generated each time

Refresh tokens are not really generated each time but generated first time only. So it is the expected response from google.