CODEWITHSUNDEEP
csvgoogle-apps-scriptgoogle-sheetsgoogle-sheets-apihttp-get
Joseph Casey
Joseph Casey

Reputation: 1303

Exporting a specific sheet with Google Spreadsheet's Direct Download URL to Dropbox folder

I have a spreadsheet names 'spreadsheet' and it has three sheets named 'a','b', and 'c' respectively. I currently published this spreadsheet with this url format

<!-- This script uses the super simple Dropbox Drop-In API -->

<script type="text/javascript" src="https://www.dropbox.com/static/api/2/dropins.js" id="dropboxjs" data-app-key="ex4mpl3K3y"></script>

<!-- This anchor establishes the dropbox button which determines the file to download to your dropbox folder. --> 

<a href="https://docs.google.com/a/account/spreadsheets/d/r4Nd0MSpR34d5h33t/export?format=csv&id=r4Nd0MSpR34d5h33t" data-filename="file-name.csv" class="dropbox-saver"></a>

This url format allows anyone to download a csv version of the file, but it downloads sheets a,b, and c. How would I modify this url to download only sheet b or sheet c?

Upvotes: 1

Views: 1571

Answers (1)

Joseph Casey
Joseph Casey

Reputation: 1303

How to Export Google Spreadsheet to Dropbox

Setting up Dropbox

  1. Create a Dropbox account at www.dropbox.com
  2. Create a Dropbox app using the API Console at https://www.dropbox.com/developers/apps make sure to give it a Drop-ins permission type.
  3. In the App Console tab, go to your new app's settings and add the following domains

docs.google.com || 127.0.0.1 || localhost || googleusercontent.com

  1. Note the app key
  2. Add this script to use the Dropbox Drop-in API's Saver method: <script type="text/javascript" src="https://www.dropbox.com/static/api/2/dropins.js" id="dropboxjs" data-app-key="YOURAPPKEYRECEIVEDFROMAPICONSOLE"></script>

Setting up Google Spreadsheet

  1. Set up a google account
  2. Create a spreadsheet
  3. Securely publish the sheet to the web
  4. On the spreadsheet toolbar go to Tools > Script Editor. This will open a new window where you will see an empty javascript file called code.gs
  5. Add one of the scripts below to your code.js
  6. The sheetId is the random series of characters that follow the /d/ in the url. The gid can by found by going to the publish sheet to the web option and selecting a specific sheet to publish, copying the url given, and finding the value inside the http query parameter labeled &gid=

Functional Macro Magic

The code you will see is a very simple script that adds a menu option to your spreadsheet 

    function onOpen() {
          var menuItems = [
            {name: 'Dropbox', functionName: 'dropbox'}
          ];
          SpreadsheetApp.getActive().addMenu('The Make Life Easy Button', menuItems);
    }
    function dropbox(){

     var dropboxKey = 'string';
     var sheetid = 'string';
     var gid = 'string';
     var fileName = 'string';
     var testResults = '<script type="text/javascript" src="https://www.dropbox.com/static/api/2/dropins.js" id="dropboxjs" data-app-key="'+ dropboxKey +'"></script>';
  testResults += '<h2>Store Content Sheet</h2><a href="https://docs.google.com/a/' + account + '/spreadsheets/d/' + sheetid + '/pub?gid=' + gid + '&single=true&output=csv" data-filename="' + fileName + '" class="dropbox-saver"></a>'; 

    // Show a dialog with the test results.
       var htmlApp = HtmlService
         .createHtmlOutput(testResults)
         .setSandboxMode(HtmlService.SandboxMode.IFRAME)
         .setTitle('Test Results')
         .setWidth(600)
         .setHeight(400);
      SpreadsheetApp.getActiveSpreadsheet().show(htmlApp);
    }

Note to Future Readers

You will need to give permission to both google script and dropbox.

Please make sure to leave comments on any steps that need further clarification, and I will make sure to fill in the gaps. If content is out-of-date, leaving comments on updated material would be very much appreciated.

Upvotes: 1

Related Questions