How to hide certain columns? - Google Spreadsheet

Question

On a published (as a Web Page) Google spreadsheet:

I would not like anyone seeing four columns of a spreadsheet with sensitive data. I want these four columns just for personal use.

Solutions using IMPORTRANGE function are not suitable as a solution. That's because I create a new spreadsheet every month, with a lot of data in it, and I simply copy the four columns onto the new sheet every month. These columns also perform some calculations from the data on the same sheet.

I tried hiding columns, this way. But it is a bit uncomfortable for me.

PS. This is not the same question as this one

Your question doesn’t really make it clear what you want your users to be able to do but I’d be inclined to look into building a web app that gives them access to the information they need and gives them control over the information that they need to change and doesn’t give them any access to the website directly so that you can protect your private information. That could take the form of a HTML JavaScript type web app or possibly you could get into Google business class and get into the early adopter program for app maker — Cooper
As it is a published Google Spreadsheet Web Page, users can only see what is in the cells, but they can not edit them. Anyway, maybe I can try App Maker, that's a good point to have into account. Thank you — Sergio Fernández

Cooper Cooper · Accepted Answer · 2017-11-29T18:15:42

Well since they don't need to edit anything that makes it a lot easier.

Here's the code for displaying a table on a webapp. You can choose to make it editable or not and you can leave any columns off that you wish.

var SSID='';
var sheetName=''; 
function onOpen()
{
  SpreadsheetApp.getUi().createMenu('HTML Spreadsheet')
    .addItem('Run Spreadsheet in Dialog', 'htmlSpreadsheet')
    .addToUi();
}

function htmlSpreadsheet(mode,edit){
  var mode=mode||'dialog';
  var edit=edit||true;
  var br='<br />';
  var s='';
  var hdrRows=1;
  var ss=SpreadsheetApp.openById(SSID);
  var sht=ss.getSheetByName(sheetName);
  var rng=sht.getDataRange();
  var rngA=rng.getValues();
  ;
  switch(edit){
    case true:
      s+='<table>'
      for(var i=0;i<rngA.length;i++){
        s+='<tr>';
        for(var j=0;j<rngA[i].length;j++){
          if(i<hdrRows){
            s+='<th id="cell' + i + j + '">' + '<input id="txt' + i + j + '" type="text" value="' + rngA[i][j] + '" size="10" onChange="updateSS(' + i + ',' + j + ');" />' + '</th>';
          }else{
            s+='<td id="cell' + i + j + '">' + '<input id="txt' + i + j + '" type="text" value="' + rngA[i][j] + '" size="10" onChange="updateSS(' + i + ',' + j + ');" />' + '</th>';
          }
        }
        s+='</tr>';
      }
      s+='</table>';
      break;
    case false:
      s+='<table style="border: 1px solid black;">'
      for(var i=0;i<rngA.length;i++){
        s+='<tr>';
        for(var j=0;j<rngA[i].length;j++){
          if(i<hdrRows){
            s+='<th style="border: 1px solid black;">' + rngA[i][j] + '</th>';
          }else{
            s+='<td style="border: 1px solid black;">' + rngA[i][j] + '</th>';
          }
        }
        s+='</tr>';
      }
      s+='</table>';
      break;
  }

  //s+='<div id="success"></div>';
  s+='</body></html>';
  switch (mode){
    case 'dialog':
      var userInterface=HtmlService.createHtmlOutputFromFile('htmlss').setWidth(1000).setHeight(450);
      userInterface.append(s);
      SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Spreadsheet Data for ' + ss.getName() + ' Sheet: ' + sht.getName());
      break;
    case 'web':
      var userInterface=HtmlService.createHtmlOutputFromFile('htmlss').setWidth(1000).setHeight(450);
      return userInterface.append(s).setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
   }
}

function updateSpreadsheet(i,j,value)
{
  var ss=SpreadsheetApp.openById(SSID);
  var sht=ss.getSheetByName(sheetName);
  var rng=sht.getDataRange();
  var rngA=rng.getValues();
  rngA[i][j]=value;
  rng.setValues(rngA);
  var data = {'message':'Cell[' + Number(i + 1) + '][' + Number(j + 1) + '] Has been updated', 'ridx': i, 'cidx': j};
  return data;
}

function doGet()
{
  var output=htmlSpreadsheet('web');
  return output;
}

Here's the htmlss.html page:

<!DOCTYPE html>
<html>
  <head>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <script>
    $(function() {

    });
    function updateSS(i,j)
    {
      var str='#txt' + String(i) + String(j);
      var value=$(str).val();
      $(str).css('background-color','#ffff00');
      google.script.run
         .withSuccessHandler(successHandler)
         .updateSpreadsheet(i,j,value)
    }
    function successHandler(data)
    {
      $('#success').text(data.message);
      $('#txt' + data.ridx + data.cidx).css('background-color','#ffffff');
    }
    console.log('My Code');
    </script>
    <style>
      th{text-align:left}
    </style>
  </head>
  <body>
  <div id="success"></div>

How to hide certain columns? - Google Spreadsheet

1 Answers