Automating Google Sheets to Dropbox Uploads using Google Apps Script

[Eli]

This post details two efficient methods for automating the upload of Google Sheets data as CSV files to Dropbox, leveraging Google Apps Script. Directly retrieving data from Google Sheets using Python and a service account has proven slow; utilizing Dropbox as an intermediary significantly speeds up programmatic data access for calculations and analysis via tools like Python's urllib.request. This approach involves first pushing the Google Sheet to Dropbox using Google Apps Script, which has proven more efficient than direct retrieval. Dropbox no longer provides long-lived access tokens, necessitating the use of refresh tokens for sustained access.

Method 1: Automating Google Sheets to Dropbox Uploads using Short-Lived Access Tokens

This method uses a short-lived Dropbox access token, which needs to be generated and replaced in the script each time it expires. While simpler to set up, it requires more frequent updates to the access token within the script.

1. Dropbox App Setup

1. Sign in to Dropbox.
2. Go to the Dropbox App Console
3. Click "Create app".
4. Set the app type to "App folder".
5. Fill in the app details. Crucially, note the App key, App secret, and OAuth 2 Redirect URIs. Generate a short-lived access token. Securely store these credentials.
6. Under "Permissions", select account_info.write, files.metadata.write, files.content.write, and files.content.read under Account Info and Files and Folders. Under Connect, select Profile and Email. Click "Submit". Add optional branding information and click "Save changes".

2. Google Apps Script Implementation

After setting up your Dropbox app, open your Google Sheet in Google Drive. Go to "Extensions" -> "Apps Script". Replace the placeholders in the following code with your actual credentials:

Code: [Select all] [Expand/Collapse]

1. function onEdit(e) {
2.   // Configuration (Use Script Properties for production)
3.   const ACCESS_TOKEN = PropertiesService.getScriptProperties().getProperty('DROPBOX_ACCESS_TOKEN'); // Replace with your Dropbox access token from Script Properties
4.   const DROPBOX_FOLDER_PATH = '/home/Google_CSV_Destination/';
5.   const FILE_NAME = 'data.csv';
6.   const SHEET_NAME = 'Form Responses 1'; // Change to your sheet name
7.  
8.   // Get the spreadsheet and specific sheet (by name)
9.   const ss = SpreadsheetApp.getActiveSpreadsheet();
10.   const sheet = ss.getSheetByName(SHEET_NAME);
11.  
12.  
13.   if (!sheet) {
14.     Logger.log('Sheet not found: ' + SHEET_NAME);
15.     return; // Exit if the sheet isn't found
16.   }
17.  
18.   // Get the sheet data as a 2D array
19.   const data = sheet.getDataRange().getValues();
20.  
21.   // Convert the 2D array to CSV format
22.   const csvData = data.map(row => row.map(cell => `"${cell}"`).join(',')).join('\n');
23.  
24.   // Dropbox API endpoint and options
25.   const options = {
26.     'method': 'post',
27.     'contentType': 'application/octet-stream',
28.     'headers': {
29.       'Authorization': 'Bearer ' + ACCESS_TOKEN,
30.       'Dropbox-API-Arg': JSON.stringify({
31.         'path': DROPBOX_FOLDER_PATH + FILE_NAME,
32.         'mode': 'overwrite', // or 'add' if you want to create a new file each time
33.         'autorename': false,
34.         'mute': true, // Don't get notifications from Dropbox
35.       }),
36.     },
37.     'payload': csvData,
38.   };
39.  
40.   // Send data to Dropbox
41.   try {
42.     UrlFetchApp.fetch('https://content.dropboxapi.com/2/files/upload', options);
43.     Logger.log('File uploaded to Dropbox successfully!');
44.   } catch (error) {
45.     Logger.log('Error uploading file to Dropbox: ' + error);
46.   }
47. }

3. Securely Storing Credentials

For enhanced security, store the ACCESS_TOKEN in the Script Properties (Script Editor -> Project Settings -> Script properties). Set the property DROPBOX_ACCESS_TOKEN to your Dropbox access token. This avoids hardcoding sensitive information directly in the script.

4. Deploy the app as a web app

Go to "Deploy" > "New deployment" > choose "Web app". Set the "Who has access to the app" to "Anyone, even anonymous". In this case, choose "Only Myself".

5. Setting up Event Triggers

Go to "Edit" -> "Current project's triggers". Click "+ Add Trigger". Configure the trigger to run the onEdit function "on edit" of your spreadsheet. This will automatically upload the data whenever the spreadsheet is modified, configure more triggers as desired. You can also configure time-driven triggers for scheduled uploads.

Check the Executions page - Editor -> Executions to see the deployment status.

Method 2: Automating Google Sheets to Dropbox Uploads using Refresh Tokens

Here is the quick summary of Method 2:

• Copy and paste the code into Editor.
• Enable the OAuth2 Library.
• Go to Project Settings and set the App key and the App secret under the Script Properties.
• Deploy the script as the web app, choose "Only Myself".
• Authorize the app with Gmail account if needed, you may be prompted when you run either the printRedirectURI or the authorize function or upon deploying the script as the web app.
• Authorize with the authorize function. Copy the URL printed and paste it on the browser to authorizen - do this only once.
• Run printRedirectURI function to get the redirect URI, add it to DX under Redirect URIs.
• Set Triggers and test the code.
• You're done.

This method uses a refresh token to obtain new access tokens as needed, eliminating the need for manual updates. It's more secure and requires less maintenance.

1. Dropbox App Setup: Same as Method 1, but this time you need to add the redirect URI:
   
   This method requires a properly set up of the redirect URI. To obtain the redirect URI:
   
   • Copy the printRedirectUri() function below to the start of the script:
     
     
     Code: [Select all] [Expand/Collapse]

     1. function printRedirectUri() {
     2.   const redirectUri = ScriptApp.getService().getUrl();
     3.  
     4.   // Log the redirect URI to the Apps Script logs
     5.   Logger.log("Your Redirect URI is: " + redirectUri);
     6. }

   • Run the function to log the redirect URI to the console.
   • Copy and paste the URI to add it into the Redirect URIs section in the Dropbox app.
   • Remove the printRedirectUri() function from the script.
2. Google Apps Script Implementation:
   
   This method requires a more complex OAuth2 flow. Add the OAuth2 library (Script ID: 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF). Replace placeholders with your actual Dropbox app credentials (DO NOT HARDCODE IN PRODUCTION; USE PropertiesService AS SHOWN IN THE COMMENTS).
   
   
   Code: [Select all] [Expand/Collapse]

   1. function authorize() {
   2.   const CLIENT_ID = PropertiesService.getScriptProperties().getProperty('DROPBOX_APP_KEY'); // Replace with your actual key from Script Properties
   3.   const CLIENT_SECRET = PropertiesService.getScriptProperties().getProperty('DROPBOX_APP_SECRET'); // Replace with your actual secret from Script Properties
   4.   const REDIRECT_URI = ScriptApp.getService().getUrl();
   5.   const authorizeUrl = `https://www.dropbox.com/oauth2/authorize?response_type=code&client_id=${CLIENT_ID}&redirect_uri=\${encodeURIComponent(REDIRECT_URI)}&token_access_type=offline`;
   6.   Logger.log("Authorize Dropbox app: " + authorizeUrl);
   7.   SpreadsheetApp.getActiveSpreadsheet().toast('Copy and paste the URL in the log into your browser to authorize the app','Dropbox Authorization', -1);
   8. }
   9.  
   10. function doGet(e) {
   11.   if (e && e.parameter && e.parameter.code) {
   12.     const code = e.parameter.code;
   13.     const CLIENT_ID = PropertiesService.getScriptProperties().getProperty('DROPBOX_APP_KEY');
   14.     const CLIENT_SECRET = PropertiesService.getScriptProperties().getProperty('DROPBOX_APP_SECRET');
   15.     const REDIRECT_URI = ScriptApp.getService().getUrl();
   16.  
   17.     const options = {
   18.       'method': 'post',
   19.       'contentType': 'application/x-www-form-urlencoded',
   20.       'payload': `grant_type=authorization_code&code=${code}&client_id=${CLIENT_ID}&client_secret=${CLIENT_SECRET}&redirect_uri=${encodeURIComponent(REDIRECT_URI)}`
   21.     };
   22.  
   23.     const response = UrlFetchApp.fetch('https://api.dropboxapi.com/oauth2/token', options);
   24.     const data = JSON.parse(response.getContentText());
   25.  
   26.     if (data.access_token && data.refresh_token) {
   27.       PropertiesService.getScriptProperties().setProperty('dropboxRefreshToken', data.refresh_token);
   28.       Logger.log('Refresh token stored successfully!');
   29.       return HtmlService.createHtmlOutput('Authorization successful. Refresh token stored.');
   30.     } else {
   31.       Logger.log('Error obtaining tokens: ' + JSON.stringify(data));
   32.       return HtmlService.createHtmlOutput('Authorization failed. Check the logs.');
   33.     }
   34.   } else {
   35.     return HtmlService.createHtmlOutput('Authorization failed. Invalid redirect.');
   36.   }
   37. }
   38.  
   39. function getDropboxRefreshToken() {
   40.   return PropertiesService.getScriptProperties().getProperty('dropboxRefreshToken');
   41. }
   42.  
   43. function getAccessToken() {
   44.   const refreshToken = getDropboxRefreshToken();
   45.   if (!refreshToken) {
   46.     Logger.log('Refresh token not found. Run authorize().');
   47.     return null;
   48.   }
   49.   const CLIENT_ID = PropertiesService.getScriptProperties().getProperty('DROPBOX_APP_KEY');
   50.   const CLIENT_SECRET = PropertiesService.getScriptProperties().getProperty('DROPBOX_APP_SECRET');
   51.  
   52.   const options = {
   53.     'method': 'post',
   54.     'contentType': 'application/x-www-form-urlencoded',
   55.     'payload': `grant_type=refresh_token&refresh_token=${refreshToken}&client_id=${CLIENT_ID}&client_secret=${CLIENT_SECRET}`
   56.   };
   57.  
   58.   try {
   59.     const response = UrlFetchApp.fetch('https://api.dropboxapi.com/oauth2/token', options);
   60.     const data = JSON.parse(response.getContentText());
   61.     return data.access_token;
   62.   } catch (error) {
   63.     Logger.log('Error refreshing access token: ' + error);
   64.     return null;
   65.   }
   66. }
   67.  
   68. function onEdit(e) {
   69.   const DROPBOX_FOLDER_PATH = '/home/Google_CSV_Destination/';
   70.   const FILE_NAME = 'data.csv';
   71.   const SHEET_NAME = 'Form Responses 1';
   72.  
   73.   const ss = SpreadsheetApp.getActiveSpreadsheet();
   74.   const sheet = ss.getSheetByName(SHEET_NAME);
   75.  
   76.   if (!sheet) {
   77.     Logger.log('Sheet not found: ' + SHEET_NAME);
   78.     return;
   79.   }
   80.  
   81.   const data = sheet.getDataRange().getValues();
   82.   const csvData = data.map(row => row.map(cell => `"${cell}"`).join(',')).join('\n');
   83.  
   84.   const accessToken = getAccessToken();
   85.  
   86.   if (!accessToken) {
   87.     Logger.log('Failed to obtain access token.');
   88.     return;
   89.   }
   90.  
   91.   const options = {
   92.     'method': 'post',
   93.     'contentType': 'application/octet-stream',
   94.     'headers': {
   95.       'Authorization': 'Bearer ' + accessToken,
   96.       'Dropbox-API-Arg': JSON.stringify({
   97.         'path': DROPBOX_FOLDER_PATH + FILE_NAME,
   98.         'mode': 'overwrite',
   99.         'autorename': false,
   100.         'mute': true,
   101.       }),
   102.     },
   103.     'payload': csvData,
   104.   };
   105.  
   106.   try {
   107.     UrlFetchApp.fetch('https://content.dropboxapi.com/2/files/upload', options);
   108.     Logger.log('File uploaded to Dropbox successfully!');
   109.   } catch (error) {
   110.     Logger.log('Error uploading file to Dropbox: ' + error);
   111.   }
   112. }

3. Securely Storing Credentials
   
   Store DROPBOX_APP_KEY and DROPBOX_APP_SECRET in Script Properties. The refresh token will be stored automatically in Script Properties after successful authorization.
   
   \(\\ \)
4. Authorization with Gmail to use the Apps Script, choose the appropriate Gmail account and Continue - there will be some warnings about safety, ignore them, choose Advanced and proceed.
5. Next Authorization:
   • Run the authorize() function. This will generate an authorization URL.
   • Copy and paste the URL into your browser and authorize the app.
   • The doGet function will handle the redirect from Dropbox and store the refresh token: Dropbox's OAuth 2.0 flow necessitates a designated endpoint within the Google Apps Script web app to process the authorization redirect. This is achieved by a doGet function, which intercepts the Dropbox redirect, retrieves the authorization code, exchanges it for access and refresh tokens, and securely stores the refresh token.
6. Setting up Event Triggers: (Same as Method 1)

Remember to replace placeholder values with your actual credentials and adjust file paths and sheet names as needed. Both methods use the overwrite mode; change to add if you need to create a new file each time. The refresh token method is strongly recommended for its superior security and reduced maintenance. Always prioritize secure credential management.