How do I hide column in google sheets using APIs?

Question

In my project we generate a google sheet with certain number of columns. One of the column, is not important to the user, so I want to hide it for them.

I considered not even generating the column but then that column is important for debugging purposes.

I checked the google APIs doc but couldn't find anything. Any suggestion would be highly appreciated.

Reffered to this doc but couldn't find any answer https://developers.google.com/sheets/api/samples/rowcolumn

Answer 1

It is not necessary to use google-sheets-api to hide a column. Try this code:

// TODO: Replace the column index with your own.
  var COLUMN_INDEX = 1; 
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.hideColumns(COLUMN_INDEX);

Answer 2

I believe your goal is as follows.

• You want to hide a column on a sheet in a Google Spreadsheet.
• You want to achieve this using Sheets API.

In this case, it is required to use Method: spreadsheets.batchUpdate. The sample request body is as follows.

Sample request body:

In this request body, columns "B" and "D" of sheetId are hidden. In this case, the gridrange is used. At that time, the first index is 0. Please be careful about this.

When you use this request body, please modify the values of sheetId, startIndex, and endIndex.

{
  "requests": [
    {
      "updateDimensionProperties": {
        "properties": {
          "hiddenByUser": true
        },
        "range": {
          "sheetId": 0,
          "startIndex": 1,
          "endIndex": 2,
          "dimension": "COLUMNS"
        },
        "fields": "hiddenByUser"
      }
    },
    {
      "updateDimensionProperties": {
        "properties": {
          "hiddenByUser": true
        },
        "range": {
          "sheetId": 0,
          "startIndex": 3,
          "endIndex": 4,
          "dimension": "COLUMNS"
        },
        "fields": "hiddenByUser"
      }
    }
  ]
}

For example, when you test this request body, please copy and paste the above request body to "APIs Explorer" of Method: spreadsheets.batchUpdate. And, when you set your Spreadsheet ID and "Execute" button, you can see the result in your Spreadsheet.

Sample curl command:

The sample curl command is as follows.

curl --request POST \
  'https://sheets.googleapis.com/v4/spreadsheets/[SPREADSHEET_ID]' \
  --header 'Authorization: Bearer [YOUR_ACCESS_TOKEN]' \
  --header 'Accept: application/json' \
  --header 'Content-Type: application/json' \
  --data '{"requests":[{"updateDimensionProperties":{"properties":{"hiddenByUser":true},"range":{"sheetId":0,"startIndex":1,"endIndex":2,"dimension":"COLUMNS"},"fields":"hiddenByUser"}},{"updateDimensionProperties":{"properties":{"hiddenByUser":true},"range":{"sheetId":0,"startIndex":3,"endIndex":4,"dimension":"COLUMNS"},"fields":"hiddenByUser"}}]}' \
  --compressed

References:

• Method: spreadsheets.batchUpdate
• UpdateDimensionPropertiesRequest