How to hide grid lines in google spreadsheet programmatically in a script

Question

Is there a programmatic way to control hide/unhide Gridlines or protected ranges in Google spreadsheet? There is an option in the menu - View > Gridlines or Protected Ranges to do this. But I want to do this in a script.

Thanks.

Answer 1

Try Sheet.setHiddenGridlines(true). See https://developers.google.com/apps-script/reference/spreadsheet/sheet#setHiddenGridlines(Boolean)

Answer 2

A little late, but here's a handy snippet to accomplish this by calling the V4 API from within Google Apps Script.

For this to work:

• You'll need to enable the Sheets v4 API in your Google Cloud Console for the Sheet's bound Google Apps Script project, which you can get to from within the Script editor, from Resources->Cloud Platform project...

• You'll likely also need to enable the Sheets V4 API Resources->Advanced Google services...

function test() {
  var spreadsheetId = SpreadsheetApp.getActive().getId();
  var sheetId = SpreadsheetApp.getActiveSheet().getSheetId();
  hideGridlines(spreadsheetId, sheetId, false);
}

/**
 * Hide or show gridlines
 *
 * @param {string} spreadsheetId - The spreadsheet to request.
 * @param {number} sheetId - The ID of the sheet.
 * @param {boolean} hideGridlines - True if the grid shouldn't show gridlines in the UI.
 **/
function hideGridlines(spreadsheetId, sheetId, hideGridlines) {
  var resource = {
    "requests": [
      {
        "updateSheetProperties": {
          "fields": "gridProperties(hideGridlines)",    
          "properties": {
            "sheetId": sheetId,
            "gridProperties": {
              "hideGridlines": hideGridlines
            }
          }
        }
      }
    ],
    "includeSpreadsheetInResponse": false,
    "responseIncludeGridData": false,
  }

  Sheets.Spreadsheets.batchUpdate(resource, spreadsheetId)  
}

Answer 3

I use the command:

sheet.getRange(1,1,maxrows,maxcolumns).setBorder(true,true,true,true,false,false,"white");

This change the linecolor for white, which is the same of the background. Then, the gridline is "hidden". This works for me.