Skip to main content
Solved

Connecting Google API to Airtable

T

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.

Best answer by TheTimeSavingCo

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

View original
Did this topic help you find an answer to your question?

3 replies

T
  • tusharbjobs
  • Author
  • Participating Frequently
  • 5 replies
  • June 22, 2024

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
Forum|alt.badge.img+18

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

Adam, Consultant @ The Time Saving Co
T
  • tusharbjobs
  • Author
  • Participating Frequently
  • 5 replies
  • June 24, 2024
TheTimeSavingCo wrote:

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


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

TheTimeSavingCo
1 person likes this
  • TheTimeSavingCo
    TheTimeSavingCo

Reply


Most helpful members this week

Terms & Conditions