Google sheets API - Cannot append data in ProtectedRange column

Question

I created a new sheet using API

fetch(
        `https://sheets.googleapis.com/v4/spreadsheets/${docId}/values/${sheetName}?valueInputOption=USER_ENTERED`,
        {
          method: "PUT",
          headers: {
            "Content-Type": "application/json",
            Authorization: `Bearer ${token}`,
            Accept: "application/json",
          },
          body: JSON.stringify({ values: tableInfo }),
        }
      )

After creating the google I set the first column (A) as protected and hidden, I used the batchUpdate API to do that using the code below

const options = {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      Authorization: `Bearer ${token}`,
    },
    body: JSON.stringify(formatDetails),
  };
  try {
    const formatApi = await fetch(
      `https://sheets.googleapis.com/v4/spreadsheets/${spreadSheetId}:batchUpdate`,
      options
    );
    const formatRes = await formatApi.json();
    return formatRes;
  } catch (error) {
    console.log("Format Column Error: ", error);
  }

const formatDetails = {
requests: [
{
    updateDimensionProperties: {
      properties: {
        hiddenByUser: true,
      },
      fields: "hiddenByUser",
      range: {
        sheetId: tabId,
        dimension: "COLUMNS",
        startIndex: 0,
        endIndex: 1,
      },
    },
  }
},
{
    addProtectedRange: {
      protectedRange: {
        protectedRangeId: tabId,
        range: {
          sheetId: tabId,
          startRowIndex: 0,
          startColumnIndex: 0,
          endColumnIndex: 1,
        },
        description: "Protected! Cannot edit",
        warningOnly: true,
        requestingUserCanEdit: true,
      },
    },
  }
}
]

Now I use sheets API append a row in the sheet like below:

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

When I append the row, the protected cell is not getting appended with the value.

Column A | Column B | Column C
Data A1 | Data B1 | Data C1 --> Data already in the sheet
Data A2 | Data B2 | Data C2 --> Data already in the sheet
         Data A3 | Data B3 | Data C3 --> New data

After appending the sheet looks like above. Please help me how to fix this. Thank you!

Answer 1

When I saw your showing script, I guessed that the reason for your current issue might be due to "Method: spreadsheets.values.append". In your script, how about the following modification?

From:

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

To:

const token = "###"; // Please set your value.
const spreadSheetId = "###"; // Please set your value.
const sheetName = "###"; // Please set your value.
const data = [[###]]; // Please set your value.

const res = await fetch(
  `https://sheets.googleapis.com/v4/spreadsheets/${spreadSheetId}/values/${sheetName}`,
  {
    method: "GET",
    headers: { Authorization: `Bearer ${token}` },
  }
)
  .then((response) => {
    return response.json();
  })
  .catch((error) => {
    console.log("Update Sheet Error: ", error);
  });

const range = `'${sheetName}'!A${res.values.length + 1}`;
var result = await fetch(
  encodeURI(`https://sheets.googleapis.com/v4/spreadsheets/${spreadSheetId}/values/${range}?valueInputOption=USER_ENTERED`),
  {
    method: "PUT",
    headers: {
      "Content-Type": "application/json",
      Authorization: `Bearer ${token}`,
    },
    body: JSON.stringify({ values: data }),
  }
)
  .then((response) => {
    return response.json();
  })
  .catch((error) => {
    console.log("Update Sheet Error: ", error);
  })

• In this modification, first, in order to retrieve the last row number, the values are retrieved. After the row number is retrieved, the values of data are put into the cells using "Method: spreadsheets.values.update". By this, the values are put from column "A".

Reference:

• Method: spreadsheets.values.update