Solved: Column Security Filters?

Ferret · 03-29-2021 07:42 AM

Security filters are helpful in many ways, one of which being a security feature that ensures that only the ROWS you want are given to the user’s app.

My question is, is there a way to do a similar thing but with COLUMNS?

To be clear, I know slices exist and I am kinda looking for that, but only for it to happen in the way security filters do. In other words, I want a slice without having to load the entire table into the app first.

Use case: I have a table of jobs containing sensitive information. In one app, only a list of job names (labels) is needed, not any other columns. Instead of loading the entire set of the table’s columns into the app and then only ever use/show the much smaller slice version, I want to just make the data loaded into the app small to begin with.

Side-note: It would be really cool if an app can determine that, if a table is never used directly but a slice is, then only loads into the app the set of data according to the slice and not the full table.

1minManager

So if it is sensitive data, consider creating another spreadsheet table and then using a spreadsheet ARRAYFORMULA() or QUERY() equation to pull just that data from the main table. Then you could use a security filter to blank out the first table for some users

View solution in original post

Steve

Nope.

1minManager

So if it is sensitive data, consider creating another spreadsheet table and then using a spreadsheet ARRAYFORMULA() or QUERY() equation to pull just that data from the main table. Then you could use a security filter to blank out the first table for some users

Ferret

Excellent, that is exactly what I am pursuing.

Unfortunately, a QUERY or ARRAYFORMULA will only be updated when that sheet is opened manually. How would you overcome that? Coupler.io is a suitable solution, but, unlike QUERY formulas, do not have a way to filter the returned rows.

1minManager

In Google Sheets formulas work all the time AFAIK. Used something similar in a few apps and had no problems with the Sheet not being open.

Not sure about Excel or Smartsheet

Rafael_ANEIC-PY

I can confirm this as well.

1minManager

Only other way to do it would be to use actions to write to the second sheet. But thats alot faffier than this way

Ferret

Oh. That is very good news. I’m afraid I drank the punch found here and jumped right in:

“Data loads only when the spreadsheet is open. The imported data may not be available until you open the spreadsheet with the IMPORTRANGE formula. This is troublesome if the data in the spreadsheet is synchronized with a third-party app or tool.”

“Never. Spreadsheets update external api only onOpen(). But there is no documentation and this may change in the future.”

I’m assuming a change to Sheets changed this or that Appsheet is triggering the onOpen() event on the sheet when accessing the data…
Either way, thank you two for your feedback here. Without I would have made this much more complicated.

1minManager

Yeah, IMPORTRANGE is only for pulling stuff between different spreadsheets. What I’m saying is you have 2 tabs in the same sheet. So all the formulas are working in that one sheet

Ferret

Oh, I totally misread that.

That implementation sounds perfect, thank you again.

Rafael_ANEIC-PY

Exactly, and Importrange is among the easiest things you can run with google apps scripts, here’s a short snip of code that copies a bunch of columns from a sheet to another, using a timer you can set this to run as often as once per minute:

function Import() {
var sss = SpreadsheetApp.openById("1izqDSG3U-_tWYeXterp-GHO2kQ9IGK_IModZbcSt_LI"); // sss = source spreadsheet
var ss = sss.getSheetByName("Exportar"); // ss = source sheet
var range = ss.getRange("A:DB");
var values = range.getValues();

var tss = SpreadsheetApp.openById("1a6t49HLggfdpKjNLiLVLm_01DTsa4zoyMgwqye7pzag"); // tss = target spreadsheet
var ts = tss.getSheetByName("Script test"); // ts = target sheet
var ssRange = ts.getRange("A:DB");
  
ssRange.setValues(values);
};

Ferret

Very helpful, thanks.