Google Sheets API - how can I get data from my private sheet?

1

I need to access some data from a private Google Sheets document that only my google account has access to – it is not shared with anyone else.

From here: Configure the OAuth consent screen  |  Google Workspace  |  Google Developers

When your application requests private data, the request must be authorized by an authenticated user who has access to that data.

Again, that user would be me – the application developer. The users of my application will not have these sheets shared with them.

So I would like my meteor server to be “logged in”, so to speak, with my Google account. The users of my app, on the other hand, will be logged in with their own GitHub accounts.

I would then like to pull data from my private sheets and display it to the users of my application.

From what I’m reading in the Google API docs, I’m not sure this is possible – but it seems to me like it should be.

Can anyone help me out?

1344×716 129 KB

2

It’s possible no worries :slight_smile:

Have a look at this repo where I created a simple app fetching translation keys from spreadsheets.

For the authentication setup check this one:

3

Thanks much, ddh. I have cloned your app and am running it locally.

Unfortunately, however, I can’t figure out where in the code the connection to the Google Sheets API via offline token is occurring. Can you point me to the correct lines?

Thanks.

4

I’m not totally sure what you mean exactly. But the whole login / authentication process is done via the meteor-google-spreadsheet package.

You need to follow the setup process (2nd link) and generate the private/google-key.pem then you just need to call this Meteor method:

Meteor.call("spreadsheet/fetch2", spreadsheetId, options.worksheetIndex, {email: serviceEmail, isSpreadsheetId: true});

see here: https://github.com/ddresch/meteor-i18n-monitor/blob/master/server/methods/jobs.js

5

Ahh, thanks – it looks like I got it working. (Amazing!) Now I just hope it continues to work after I deploy it to my production server.

6

Nice :slight_smile: will work I guess!

7

Well, I guess I got over the hump of not being able to fetch the data. Now my problem is that the data in the spreadsheet is returned as formulas, not as results. So instead of getting “10” returned for a cell that contains a formula resulting in “10”, I get back something like “(A5 - B3) / F4”, etc. Needless to say, this is useless to me, since the whole point of using a spreadsheet in the first place is for the calculations it performs.

8

Ok, so the spreadsheet/fetch2 method in ongoworks/meteor-google-spreadsheet has this code:

EditGoogleSpreadsheet.load(
	loadOptions,
	function sheetReady(err, spreadsheet) {
	if (err) {
		console.log(err);
		fut.return(false);
		return;
	}
	spreadsheet.receive(function(err, rows, info) {
		if (err) {
			console.log(err);
			fut.return(false);
		} else {
			fut.return({rows: rows, info: info});
		}
	});
});

When I replace it with this code, then I get values instead of formulas (note the use of {getValues:true}):

EditGoogleSpreadsheet.load(
	loadOptions,
	function sheetReady(err, spreadsheet) {
	if (err) {
		console.log(err);
		fut.return(false);
		return;
	}
	spreadsheet.receive({getValues:true}, function(err, rows, info) {
		if (err) {
			console.log(err);
			fut.return(false);
		} else {
			fut.return({rows: rows, info: info});
		}
	});
});
1 Like
9

Nice work, good to know! I used it in my case just for simple text columns.

Can you do a pull request for this one?

The maintainer was really fast the last time I sent one.