Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-apitabletop.js

Using private sheets with tabletop.js

I've used tabletop.js [1] in the past and is amazing! You can simply do anything you want seriously.

The only problem I saw is that you need to publish your spreadsheets to the web, which of course is really risky if you are working with sensitive data.

I'm in need now of using it in a project with sensitive data, so I was hoping someone can guide me on how to use it with spreadsheets that are not published to the web.

I've been searching for this for a long time without any success but seems that tabletop.js does support private sheets (here's the pull request that added this option [2]).

In fact, looking at the documentation they included it [1]:

authkey
authkey is the authorization key for private sheet support.

ASK: How am I suppose to use the authkey? can someone provide me with an example so I can try?

Thanks in advance!

[1] https://github.com/jsoma/tabletop [2] https://github.com/jsoma/tabletop/pull/64

Solution

  • How about this answer?

    Issue and workaround:

    At "tabletop.js", from the endpoint (https://spreadsheets.google.com/feeds/list/###/###/private/values?alt=json) of request, it seems that "tabletop.js" uses Sheets API v3. And when authkey is used, oauth_token=authkey is added to the query parameter. In this case, unfortunately, it seems that the private Spreadsheet cannot be accessed with it. From this situation, unfortunately, I thought that in the current stage, "tabletop.js" might not be able to use the private Spreadsheet. But I'm not sure whether this might be resolved in the future update. Of course, it seems that the web-published Spreadsheet can be accessed using this library.

    So, in this answer, I would like to propose the workaround for retrieving the values from Spreadsheet as the JSON object.

    Pattern 1:

    In this pattern, Google Apps Script is used. With Google Apps Script, the private Spreadsheet can be easily accessed.

    Sample script:

    When you use this script, please copy and paste it to the script editor and run the function myFunction.

    function myFunction() {
  const spreadsheetId = "###";  // Please set the Spreadsheet ID.
  const sheetName = "Sheet1";  // Please set the sheet name.

  const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
  const values = sheet.getDataRange().getValues();
  const header = values.shift();
  const object = values.map(r => r.reduce((o, c, j) => Object.assign(o, {[header[j]]: c}), {}));

  console.log(object)  // Here, you can see the JSON object from Spreadsheet.
}
    • I thought that this might be the simple way.

    Pattern 2:

    In this pattern, the Web Apps created by Google Apps Script is used. When the Web Apps is used, the private Spreadsheet can be easily accessed. Because the Web Apps is created with Google Apps Script. In this case, you can access to the Web Apps from outside by logging in to Google account. And, the JSON object can be retrieved in HTML and Javascript.

    Usage:

    Please do the following flow.

    1. Create new project of Google Apps Script.

    Sample script of Web Apps is a Google Apps Script. So please create a project of Google Apps Script. In order to use Document service, in this case, Web Apps is used as the wrapper.

    If you want to directly create it, please access to https://script.new/. In this case, if you are not logged in Google, the log in screen is opened. So please log in to Google. By this, the script editor of Google Apps Script is opened.

    2. Prepare script.

    Please copy and paste the following script (Google Apps Script) to the script editor. This script is for the Web Apps.

    Google Apps Script side: Code.gs

    function doGet() {
  return HtmlService.createHtmlOutputFromFile("index");
}

function getObjectFromSpreadsheet(spreadsheetId, sheetName) {
  const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
  const values = sheet.getDataRange().getValues();
  const header = values.shift();
  const object = values.map(r => r.reduce((o, c, j) => Object.assign(o, {[header[j]]: c}), {}));
  return object;
}

    HTML&Javascript side: index.html

    <script>
const spreadsheetId = "###";  // Please set the Spreadsheet ID.
const sheetName = "Sheet1";  // Please set the sheet name.

google.script.run.withSuccessHandler(sample).getObjectFromSpreadsheet(spreadsheetId, sheetName);

function sample(object) {
  console.log(object);
}
</script>
    • spreadsheetId and sheetName are given from Javascript side to Google Apps Script side. From this situation, in this case, getObjectFromSpreadsheet might be instead of "tabletop.js".

    3. Deploy Web Apps.

    1. On the script editor, Open a dialog box by "Publish" -> "Deploy as web app".
    2. Select "Me" for "Execute the app as:".
      • By this, the script is run as the owner.
    3. Select "Only myself" for "Who has access to the app:".
      • In this case, in order to access to the Web Apps, it is required to login to Google account. From your situation, I thought that this might be useful.
    4. Click "Deploy" button as new "Project version".
    5. Automatically open a dialog box of "Authorization required".
      1. Click "Review Permissions".
      2. Select own account.
      3. Click "Advanced" at "This app isn't verified".
      4. Click "Go to ### project name ###(unsafe)"
      5. Click "Allow" button.
    6. Click "OK".
    7. Copy the URL of Web Apps. It's like https://script.google.com/macros/s/###/exec.
      • When you modified the Google Apps Script, please redeploy as new version. By this, the modified script is reflected to Web Apps. Please be careful this.

    4. Run the function using Web Apps.

    You can test above scripts as follows.

    1. Login to Google account.
    2. Access to the URL of Web Apps like https://script.google.com/macros/s/###/exec using your browser.

    By this, you can see the retrieved JSON object at the console.

    Note:

    • When you modified the script of Web Apps, please redeploy the Web Apps as new version. By this, the latest script is reflected to the Web Apps. Please be careful this.

    References: