Google-sheets – Secure sharing of data between Google Sheets

In case it helps anyone else in the future, the solution I've ended up pushing for is creating an Add-On to reference the sheet they wanted hidden. The other workbook / Spreadsheet is:

• set to only "people in the domain with the link can VIEW"
• The tab / sheet is hidden and protected, and
• The document is set to be copy, download, and print protected, just in case someone does find it via spreadsheet key

The add-on itself will be a domain-only add-on, and auto-installed via policy for those who should have access. I believe this should be sufficient protection for what they need.

The way to address the problem is to use a combination of custom function in google spreadsheet and google script. I will use examples to try to address solution for your problem

Formula protection :

My understanding is you will have users enter new entries into a new row. This means you could apply the formula automatically to this new row, which might prevent them from modifying that formula in the first place

1) To apply the formula to the new row, use ArrayFormula. The below example will sum values from column F to Column I in each row

=arrayFormula((F:F+G:G+H:H+I:I))
 read as = F1+G1+H1+I1 in row 1, =F2+G2+H2+I2 in row2 and so on

you can get more elaborate and remove 0 values from the sheet like so

=arrayFormula(if((F:F+G:G+H:H+I:I) = 0,"",(F:F+G:G+H:H+I:I)))

More details can be found here. By doing so you negate the need for a user to type out the formula and hence lesser modification. ArrayFormula automatically applies/fixes the formula even when a new row is inserted or deleted.

2) Since the arrayFormula would be applied in the first row (or a specific row). Set Protection in the first row(or that specific row) so that only you can modify it and which indirectly means no one can modify any formula in the sheet. This doesn't prevent user for modifying below row 1 which affects how arrayFormula works, that is why you have use column formula protection (described Below)

Format Protection:

1) Similar to above, you can set up the first row to be the master row which will be used to format the remaining rows. Since you will also lock it down, no one except you will be able to modify it!

2) To automatically copy the formatting, you can use onEdit(e) trigger in google sheets. The below code does exactly that:

function onOpen(e){
 var ui = SpreadsheetApp.getUi()
 ui.createMenu("Format sheet").addItem("Format", "setFormat").addToUi()

}
function onEdit(e){           //This is activated each time a modification happens in the sheet
  var ss = SpreadsheetApp.getActive()
  var sheet =ss.getActiveSheet()
  if(sheet.getSheetName() == "Sheet1"){
  var entryRange = e.range
  var range  = sheet.getRange(1,entryRange.getColumn(),1,entryRange.getNumColumns())              //This will be you range to get the formatting from row "1" and corresponding column based on the column being edited
  Logger.log(entryRange.getA1Notation())
  range.copyFormatToRange(sheet, entryRange.getColumn(), entryRange.getNumColumns()+entryRange.getColumn()-1, entryRange.getRow(), entryRange.getNumRows()+entryRange.getRow()-1)
  Logger.log(entryRange.getColumn())
  if(entryRange.getColumn() == 10){                                 //This column value will not be allowed modified except for row 1 of that column
    if (entryRange.getRow() != 1){                                  //The columns in row "1" will be allowed to modified
    e.range.setValue((e.oldvalue == undefined? "": e.oldvalue))
    }
  }
  }
}

function setFormat(){
  var ss = SpreadsheetApp.getActive()
  var sheet = ss.getActiveSheet()
  var firstRow = sheet.getRange(1, 1, 1, sheet.getLastColumn())
  var dataRange = sheet.getDataRange()
  firstRow.copyFormatToRange(sheet, 1, dataRange.getNumColumns(), 2, dataRange.getNumRows())

}

Copy the above to your script Editor in google sheets and save it. Then reload your google sheet. At this point, anything you modify in row "1" will be unaffected. However, any changes to rows below that will trigger onEdit to automatically copy the format from the first row and apply it to that row in the corresponding column.

Column Formula Protection: This code within the onEdit function will prevent modification to specific column, column 10 in this case. But can be modified to protect any column. (To understand why we need this, read Formula Protection: 2) above)

if(entryRange.getColumn() == 10){                                 //This column value will not be allowed modified except for row 1 of that column
        if (entryRange.getRow() != 1){                                  //The columns in row "1" will be allowed to modified
        e.range.setValue((e.oldvalue == undefined? "": e.oldvalue))
        }
      }

It will replace the oldvalue back in column 10 from column 2 onwards. However, if someone copies a whole new row in, which also happens to modify row 10, this will not prevent it from getting modified.

You can modify it to allow you access to modify row 10 like so

if(entryRange.getColumn() == 10 && Session.getEffectiveUser().getEmail() != "Your Email Here")

Note: If you make this modification, make sure to run the onEdit function from script editor once to give it permission to access your emailID.

Finally, When you reload the sheet you will notice a new menu item called Format Sheet, you can use this to apply the format in row "1" to all the rows below it! Which is done by the function setFormat(). This a fallback code, in case people modify the formatting after onEdit trigger sets the formatting. onEdit(e) is not triggered when the user modifies the format of a cell.

Hope that helps!