How to insert a column with data using Google Sheets API?

Question

I have the following code with which i am trying to insert a column

var updateData = {
            range: `${sheetName}!R1C${index + 1}`,
            majorDimension: "COLUMNS",
            values: [["Test1"], ["Test2"], ["Test3"], ["Test4"], ["Test5"]],
          }

const apiBody = {
    valueInputOption: "RAW",
    data: updateData,
  };
var result = await fetch(
    `https://sheets.googleapis.com/v4/spreadsheets/${spreadSheetId}/values:batchUpdate`,
    {
      method: "POST",
      headers: {
        "Content-Type": "application/json",
        Authorization: `Bearer ${token}`,
      },
      body: JSON.stringify(apiBody),
    }
  )
    .then((response) => {
      return response.json();
    })
    .catch((error) => {
      console.log("Update Cell Error: ", error);
    });

The above code gives me TypeError:

Please note that I use R1C1 notation in range instead of A1 notation as my column index are dynamically generated.

Thanks in advance!

Answer 1

If you want to use R1C1 as the range of Sheets API, how about the following modification?

From:

var updateData = {
            range: `${sheetName}!R1C${index + 1}`,
            majorDimension: "COLUMNS",
            values: [["Test1"], ["Test2"], ["Test3"], ["Test4"], ["Test5"]],
          }

To:

var ar = [["Test1"], ["Test2"], ["Test3"], ["Test4"], ["Test5"]];
var updateData = {
  range: `${sheetName}!R1C${index + 1}:R1C${index + 1 + ar.length}`,
  majorDimension: "COLUMNS",
  values: ar,
};

• By this, if index is 1, the values of Test1 to Test5 are put into cells "B1:F1".

• In this modification, your access token can be used for updating your Spreadsheet. Please be careful about this.

Note:

• Unfortunately, I'm not sure whether I could correctly understand your expected result. About I have the following code with which i am trying to insert a column, if you want to put your values as one column, how about the following modification? By this, when index is 1, the values of Test1 to Test5 are put into cells "B1:B5".

  var ar = [["Test1"], ["Test2"], ["Test3"], ["Test4"], ["Test5"]];
  var updateData = {
    range: `${sheetName}!R1C${index + 1}:R${ar.length + 1}C${index + 1}`,
    majorDimension: "ROWS",
    values: ar,
  };
  

• The whole modified script using the 1st modification is as follows.

var ar = [["Test1"], ["Test2"], ["Test3"], ["Test4"], ["Test5"]];
var updateData = {
  range: `${sheetName}!R1C${index + 1}:R1C${index + 1 + ar.length}`,
  majorDimension: "COLUMNS",
  values: ar,
};

const apiBody = {
  valueInputOption: "RAW",
  data: updateData,
};
var result = await fetch(
  `https://sheets.googleapis.com/v4/spreadsheets/${spreadSheetId}/values:batchUpdate`,
  {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      Authorization: `Bearer ${token}`,
    },
    body: JSON.stringify(apiBody),
  }
)
  .then((response) => {
    return response.json();
  })
  .catch((error) => {
    console.log("Update Cell Error: ", error);
  });

Reference:

• Method: spreadsheets.values.batchUpdate