Connecting Google API to Airtable

[tusharbjobs]

I am trying  to connect Google API to access Google Drive and Google Docs inside Airtable automation script.

I setup a project with credentials and redirect URI on Google Cloud console and I am able to successfully get authorize code and access token.

However I realized that access token might not work in case automation as it expires after 1 hour and I will need refresh token to refresh the access token after it is expired.

So i am trying to change my code where I am storing the refresh token, access token and expiry time in a table on Airtable Base. But I seems to have issues while updating records on this table using Airtable Automation Run script Block.

I will send my Code at the end of this write up. 

The problem is highlighted below -

In the code search for following comment "// Check if the access token is expired and refresh it if necessary"

Next block to that you will find the line having the issue.

Here is ow my TokenData Table looks like

Has someone connected Google Api with Airtable earlier and was successful? Please help.

[tusharbjobs]

Here is my code

// Function to make HTTP POST request
async function makePostRequest(url, headers, body) {
    try {
        const response = await fetch(url, {
            method: 'POST',
            headers: headers,
            body: JSON.stringify(body),
        });
        if (!response.ok) {
            const errorText = await response.text();
            throw new Error(`HTTP error! Status: ${response.status} - ${errorText}`);
        }
        return await response.json();
    } catch (error) {
        console.error('Error making POST request:', error);
        throw error;
    }
}

// Function to get the stored token data from Airtable
async function getTokenData() {
    const tokenDataTable = base.getTable('TokenData');
    const query = await tokenDataTable.selectRecordsAsync();
    if (query.records.length > 0) {
        return query.records[0];
    }
    return null;
}

// Function to save token data to Airtable
async function saveTokenData(accessToken, refreshToken, expiryTime) {
    const tokenDataTable = base.getTable('TokenData');
    const query = await tokenDataTable.selectRecordsAsync();
    if (query.records.length > 0) {
        await tokenDataTable.updateRecordAsync(query.records[0].id, {
            "access_token": accessToken,
            "refresh_token": refreshToken,
            "expiry_time": expiryTime,
        });
    } else {
        await tokenDataTable.createRecordAsync({
            "access_token": accessToken,
            "refresh_token": refreshToken,
            "expiry_time": expiryTime,
        });
    }
}

// Function to obtain a new access token using the refresh token
async function refreshAccessToken(refreshToken, clientId, clientSecret) {
    const tokenUrl = 'https://oauth2.googleapis.com/token';
    const tokenBody = {
        refresh_token: refreshToken,
        client_id: clientId,
        client_secret: clientSecret,
        grant_type: 'refresh_token'
    };
    const tokenHeaders = {
        'Content-Type': 'application/json'
    };

    const tokenResponse = await makePostRequest(tokenUrl, tokenHeaders, tokenBody);
    return tokenResponse.access_token;
}

// Function to check if the access token is expired
function isAccessTokenExpired(expiryTime) {
    const now = Math.floor(Date.now() / 1000);
    return expiryTime <= now;
}

// Main function to create invoice using Google APIs
async function createInvoice() {
    const clientId = 'YOUR_CLIENT_ID'; // Replace with your OAuth2 client ID
    const clientSecret = 'YOUR_CLIENT_SECRET'; // Replace with your OAuth2 client secret
    const redirectUri = 'YOUR_REDIRECT_URI'; // Replace with your redirect URI

    // Get token data from Airtable
    let tokenRecord = await getTokenData();

    // If no token data is found, perform the initial authorization
    if (!tokenRecord) {
        // Step 1: Obtain authorization code manually
        const authUrl = `https://accounts.google.com/o/oauth2/v2/auth?response_type=code&client_id=${clientId}&redirect_uri=${encodeURIComponent(redirectUri)}&scope=https://www.googleapis.com/auth/drive%20https://www.googleapis.com/auth/documents`;
        console.log('Authorize this app by visiting:', authUrl);

        // Step 2: Manually obtain authorization code and paste it here
        const authCode = 'PASTE_AUTHORIZATION_CODE_HERE'; // Replace with the authorization code obtained manually

        // Step 3: Exchange authorization code for access and refresh tokens
        const tokenUrl = 'https://oauth2.googleapis.com/token';
        const tokenBody = {
            code: authCode,
            client_id: clientId,
            client_secret: clientSecret,
            redirect_uri: redirectUri,
            grant_type: 'authorization_code'
        };
        const tokenHeaders = {
            'Content-Type': 'application/json'
        };

        try {
            const tokenData = await makePostRequest(tokenUrl, tokenHeaders, tokenBody);
            // Calculate and store the expiry time of the access token
            const expiryTime = Math.floor(Date.now() / 1000) + tokenData.expires_in;
            await saveTokenData(tokenData.access_token, tokenData.refresh_token, expiryTime);
            tokenRecord = await getTokenData(); // Fetch the updated token record
        } catch (error) {
            console.error('Error obtaining tokens:', error);
            return;
        }
    }

    // Check if tokenRecord is null (just to be safe)
    if (!tokenRecord) {
        console.error('Token record is null.');
        return;
    }

    // Extract fields directly from tokenRecord
    const accessToken = tokenRecord.getCellValueAsString("access_token");
    const refreshToken = tokenRecord.getCellValueAsString("refresh_token");
    const expiryTime = tokenRecord.getCellValue("expiry_time");

    // Check if the access token is expired and refresh it if necessary
    if (isAccessTokenExpired(expiryTime)) {
        try {
            const newAccessToken = await refreshAccessToken(refreshToken, clientId, clientSecret);
            const newExpiryTime = Math.floor(Date.now() / 1000) + 3600; // Assuming the new token is valid for 1 hour
            await saveTokenData(newAccessToken, refreshToken, newExpiryTime);
            tokenRecord = await getTokenData(); // Fetch the updated token record
            //tokenRecord = base.getTable("TokenData").selectRecordAsync("newAccessToken")
            // Update tokenRecord with new values using updateRecordAsync
            await base.getTable('TokenData').updateRecordAsync(tokenRecord.id, {
                "access_token": newAccessToken,
                "refresh_token": refreshToken,
                "expiry_time": newExpiryTime
            });
        } catch (error) {
            console.error('Error refreshing access token:', error);
            return;
        }
    }

    console.log('Access Token:', accessToken);

    // Now use the accessToken to make authorized API requests to Google Drive and Google Docs
}

createInvoice().catch(console.error);

[TheTimeSavingCo · Accepted solution]

What error do you get when you try to run that code?  While that message is worth noting it doesn't usually indicate that your code doesn't work at all, only that you might want to add some checks in

[tusharbjobs]

I didn't realize that. i thought if it shows error, it doesn't work. Apologies for the confusion. It worked well. thanks mate.