Column Security Filters?

[Ferret]

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.

[Steve (Steve Coile)]

@Ferret

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

Nope.

[1minManager (Simon Robinson)]

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 (Simon Robinson)]

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

[1minManager (Simon Robinson)]

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

[Rafael_ANEIC-PY (Rafael Almeida)]

@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.

I can confirm this as well.

[Ferret]

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

IMPORTRANGE Tutorial

“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.”

How frequently does IMPORTRANGE refresh?

“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 (Simon Robinson)]

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 (Rafael Almeida)]

@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

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.