google-apps-scriptgoogle-sheets

How to hide certain columns?


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


Solution

  • 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>