CODEWITHSUNDEEP
javascriptnode.jsgoogle-apps-script
jonobr1
jonobr1

Reputation: 1093

Pass Google Sheet Data to Local Node.js Server via Google App Script

I went through this guide: https://developers.google.com/apps-script/guides/rest/quickstart/target-script to create a quick start target for Google Apps Script. At the end of this section I followed the Node.js tutorial to execute this script from a local node.js server: https://developers.google.com/apps-script/guides/rest/quickstart/nodejs

It all worked!

But, then I replaced the default code in Google Apps Script from this:

function getFoldersUnderRoot() {
  var root = DriveApp.getRootFolder();
  var folders = root.getFolders();
  var folderSet = {};
  while (folders.hasNext()) {
    var folder = folders.next();
    folderSet[folder.getId()] = folder.getName();
  }
  return folderSet;
}

to this:

function getPressInfo() {

  var spreadsheet = SpreadsheetApp.openById("MY_SHEET_ID");
  var sheets = spreadsheet.getSheets();
  var activeSheet = null;

  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    var name = sheet.getName();
    if (/published/i.test(name)) {
      activeSheet = sheet;
      break;
    }
  }

  if (!sheet) {
    return null;
  }

  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();

  return sheet.getSheetValues(1, 1, lastRow, lastCol);

}

I updated my target version and renamed my function resource to getPressInfo in my node.js script. Now I get an authorization error... I can't tell if this is in reference to the Google Sheet (Set to Publicly Visible), the Google Apps Script (Set Access to Anyone), or something entirely different. Error reads:

The API returned an error:  { [Error: ScriptError]
  code: 401,
  errors: 
   [ { message: 'ScriptError',
       domain: 'global',
       reason: 'unauthorized' } ] }

Anyone else run into this issue? I don't think it's the Google Apps Script, because when I roll back to the target with the default example it still works. If it helps I can recreate with dummy data.., but I suspect there's something simple in my code that is actually triggering the error.

Upvotes: 1

Views: 941

Answers (1)

jonobr1
jonobr1

Reputation: 1093

Okay, I was totally over thinking the task to begin with. Google Sheets has a GET request for particular formats. I used tsv, but they also accept csv. This was my node.js script — no need for Google Apps Script whatsoever:

var https = require('https');
var path = require('path');
var fs = require('fs');

var format = 'tsv';
var id = 'ID_OF_GOOGLE_SHEET';

https.get('https://docs.google.com/spreadsheets/d/' + id + '/export?format=' + format + '&id=' + id, function(resp) {

  var body = '';

  resp
    .on('data', function(data) {

      body += ab2str(data);

    })
    .on('end', function() {

      var json = [];
      var rows = body.split(/\r\n/i);

      for (var i = 0; i < rows.length; i++) {
        json.push(rows[i].split(/\t/i));
      }

      fs.writeFileSync(path.resolve(__dirname, './sheet.json'), JSON.stringify(json));
      console.log('Generated sheet.json');

    });

});

function ab2str(buf) {
  return String.fromCharCode.apply(null, new Uint16Array(buf));
}

Most notably this requires your Google Sheet to be publicly viewable.

Upvotes: 1

Related Questions