Feature request - extend authentication system to allow restricting what columns a user can access

[DylanSp]

Would it be possible to, as the title says, extend the current authentication system to restrict what columns a given user can read/write to? The use case I'm thinking of is a simple inventory tracking spreadsheet that has name, quantity, and isInStock columns, where isInStock just uses a spreadsheet formula to check if quantity != 0. It would be nice to scope access to just the name and isInStock data, without returning the raw quantity in the API response.

Thanks for making this very nice script, by the way! I came here from Hacker News when searching for info about using Google Sheets as a simple backend.

[ziolko]

Hi @DylanSp,

Sorry for the late reply - we have a few national holiday days in Poland now :) What you're describing is definitely possible. Let me take a look at this during the weekend and I will get back to you on Monday.

Cheers, Mateusz

[DylanSp]

@ziolko Thanks for taking a look at this. No worries about the timeframe, I totally understand! I'm glad you think this might be a useful addition to this project.

I've thought a bit about the API for this - one option would be adding the column names as an optional 4th option to the User() function, along these lines:

User("anonymous", UNSAFE(""),
  { transactions: POST, summary: GET },
  { transactions: ["col1", "col3"], summary: ALL_COLUMNS
);

Which would allow anonymous users POST access to col1 and col3 on the transactions sheet, and GET access to all columns on the summary sheet. One disadvantage of this API is that you couldn't have different permissions for different columns on the same sheet - you couldn't have POST permissions for one column, but only GET permissions for another - but allowing that might be more complexity than you want to add.

[ziolko]

Hi @DylanSp,

What about using two sheets instead of introducing the column-level permissions? I've shared an example here: https://docs.google.com/spreadsheets/d/17RUcZXdASWT_M2o--J7h03QuaUQ-plg243669ixpgS4/edit?usp=sharing.

The example uses two sheets - "Data" contains raw data with quantity while "API" is based on "Data" with computed column "IsInStock". You can now provide access to the "API" sheet through SpreadAPI and this way you should be able to achieve your goal.

Please let me know how it works for you, Mateusz

[DylanSp]

@ziolko - That's a workaround I've been experimenting with. After playing with it some, for this use case, I think it's probably a better way to go than adding column-level permissions. Unless you can think of another good use case, you can probably close this out for now.

[DylanSp]

Incidentally, Google Sheets' array notation makes it easy to copy a whole column's values without having to copy the formula down across every shell. ={Data!A2:A} will grab everything in the Data sheet's A column from A2 down, and ARRAYFORMULA can be used with more complex formulas. This spreadsheet has an example on the API sheet.

[ziolko]

I didn't know the array notation. Thank you for sharing the tip!

I am for keeping the API simple so for now I am closing this issue.