Append a new cell (row) at the end of specific column in google spreadsheet using api

Question

Could not figure out a way to append data at the end of specific column in google sheets through v4 APIs.

I know we can append a completely new row using Append API: https://developers.google.com/sheets/api/samples/writing#append_values

But this adds a completely new row starting from 1st column. I need to push/append data starting from specific column only.

Like following is the sheet I am working on:

Now I need to add multiple categories like "Ca1" and "Cat2" to the Categories column. I know I can pass data for not to be filled/edited columns as null like:

"majorDimension": "ROWS",
"values": [ 
           [null,"Cat1",null]
           [null,"Cat2",null]
          ]

But after this, if next time I need to append data to Tags column (say Tag1, Tag2) only using the same format, the resulting sheet would become:

"majorDimension": "ROWS",
    "values": [ 
               [null,null,"Tag1"]
               [null,null,"Tag2"]
              ]

And hence empty spaces in between like C6 cell will increase if there is no solution, once data becomes huge. I need to maintain the data like this only (column-wise only).

Is there any solution to append data at the end of a specific column?

Answer 1

You can determine the next empty cell using the existing values and then define your range from there.

1. Get the column values

GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/C:C

2. Response is a ValueRange, which includes an array of values. Thankfully, "empty trailing rows and columns will not be included", so you can use that to your advantage.

var lastRow = ValueRange.values.length;

3. Specify your new range selecting only the next empty cell in column C

var range = 'Sheet1!C' + (lastRow+1);

4. Send your update request

Sheets.Spreadsheets.Values.update(
  {
    'majorDimension': 'COLUMNS',
    'values': [['Tag1']]
  },
  spreadsheetId,
  range, 
  { valueInputOption: 'USER_ENTERED' }
);