Add header to spreadsheet while creating spreadsheet using Google spreadsheet CREATE API

Question

I am using Google spreadsheet API to create spreadsheet using node.js. I want to add an header (having three columns h1, h2, h3) while creating spreadsheet. I am able to create spreadsheet, but I am not able to add header. Any option to do that?

Answer 1

How about following GAS sample? If you want to run except for GAS, please tell me.

In order to use this, at first, please enable Google Sheet API v4 of Advanced Google services and of Google API Console.

How to use it is as follows.

1. In the script editor, select Resources > Advanced Google services

2. In the dialog that appears, click the on/off switch for Google Sheets API.

3. At the bottom of the dialog, click the link for the Google API Console.

4. In the console, click into the filter box and type part of the name of the API "Google Sheets API", then click the name once you see it.

5. On the next screen, click Enable API.

6. Close the Developers Console and return to the script editor. Click OK in the dialog. The advanced service you enabled is now available in autocomplete.

The detail information is https://developers.google.com/apps-script/guides/services/advanced.

Script :

Sheets.Spreadsheets.create({
  "properties": 
  {
    "title": "filename" // filename
  },
  "sheets": 
  [
    {
      "data": 
      [
        {
          "startRow": 0, // 1st row
          "startColumn": 7, // column h
          "rowData": 
          [
            {
              "values": 
              [
                {
                  "userEnteredValue": 
                  {
                    "stringValue": "sample text h1"
                  }
                }
              ]
            },
            {
              "values": 
              [
                {
                  "userEnteredValue": 
                  {
                    "stringValue": "sample text h2"
                  }
                }
              ]
            },
            {
              "values": 
              [
                {
                  "userEnteredValue": 
                  {
                    "stringValue": "sample text h3"
                  }
                }
              ]
            }
          ]
        }
      ]
    }
  ]
});

Result :

I'm sorry for being long JSON data. Please change the sample text. If you want to use a number for the cell, please use "numberValue" instead of "stringValue".

If I misunderstand your question, I'm sorry.

Added 1 :

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var range = sheet.getRange('h1:h3');
var protection = range.protect().setDescription('protected');  
var me = Session.getEffectiveUser();
protection.addEditor(me);
protection.removeEditors(protection.getEditors());
if (protection.canDomainEdit()) {
  protection.setDomainEdit(false);
}

Above script protects h1:h3. Owner can edit all cells, but other users cannot edit only h1:h3.

Added 2 :

Sheets.Spreadsheets.create({
  "properties": 
  {
    "title": "filename"
  },
  "sheets": 
  [
    {
      "protectedRanges": 
      [
        {
          "range": 
          {
            "startColumnIndex": 7,
            "endColumnIndex": 8,
            "startRowIndex": 0,
            "endRowIndex": 3,
            "sheetId": 0
          },
          "description": "protected",
          "editors": 
          {
            "users": 
            ["your e-mail address"
            ]
          }
        }
      ],
      "data": 
      [
        {
          "startColumn": 7,
          "startRow": 0,
          "rowData": 
          [
            {
              "values": 
              [
                {
                  "userEnteredValue": 
                  {
                    "stringValue": "sample text h1"
                  }
                }
              ]
            },
            {
              "values": 
              [
                {
                  "userEnteredValue": 
                  {
                    "stringValue": "sample text h2"
                  }
                }
              ]
            },
            {
              "values": 
              [
                {
                  "userEnteredValue": 
                  {
                    "stringValue": "sample text h3"
                  }
                }
              ]
            }
          ]
        }
      ],
      "properties": 
      {
        "sheetId": 0
      }
    }
  ]
});

This script create new spreadsheet. At that time, it adds data to 'h1:h3' and protects them. Please add your e-mail address as the editor. This is used Google Sheet API v4.