javascript google-sheets google-sheets-api

Creating spreadsheet with data using Google Sheets API from browser

I have the following data table, I want to create a spreadsheet with using Google Sheets API. I could use the subsequent fetch code snippet to create a spreadsheet with desired title and sheet name.

const tableInfo = [
  ["Name", "Age", "Score", "Date", "Tags"],
  ["Name1", "30", "2050", "13-12-2011", "First"],
  ["Name2", "40", "250", "11-12-2001", "First"],
  ["Name3", "35", "1345", "13-02-2011", "Second"]
]
const initialData = {
  properties: {
    title: `${fileName}`
  },
  sheets: [{
    properties: {
      title: `${sheetName}`
    },
  }, ],
};
fetch("https://sheets.googleapis.com/v4/spreadsheets", {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      Authorization: `Bearer ${token}`,
    },
    body: JSON.stringify(initialData),
  })
  .then((doc) => {
    return doc.json();
  })
  .then((doc) => {
    console.log("Doc Created!")
  })
  .catch((error) => {
    console.log("error");
  });

After creating this doc I couldn't update the created sheet with my table data, I get Status 400 error code

var docId = doc.spreadsheetId;
var sheetId = doc.sheets[0].properties.sheetId;
var sheetData = [];
sheetData.push({
  range: {
    sheetId: sheetId
  },
  values: tableInfo,
});
var sheetBody = {
  data: sheetData,
  valueInputOption: "RAW",
};
fetch(`https://sheets.googleapis.com/v4/spreadsheets/${docId}:batchUpdate`, {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
      Authorization: `Bearer ${token}`,
      Accept: "application/json",
    },
    body: sheetBody,
  })
  .then((updateDoc) => {
    console.log("Doc Updated!! ", updateDoc);
  })
  .catch((err) => {
    console.log("Update Error: ", err);
  });

What am I doing wrong with the above code?

Also, Is it not possible to create a spreadsheet along with data using Google Sheets API? Should I create the file first and then update it with data?

Also please note the tableInfo data is dynamic, it could have 5 columns or 50 columns, 10 rows, or 100s of rows.

Solution

About After creating this doc I couldn't update the created sheet with my table data, I get Status 400 error code and What am I doping wrong with the above code?, when I saw your script, I noticed the following modification points.

Modification points:

Your endpoint of https://sheets.googleapis.com/v4/spreadsheets/${docId}:batchUpdate is for "Method: spreadsheets.batchUpdate". But, your showing request body is not correct. In this case, how about using "Method: spreadsheets.values.update"?
The fetch API is run with the asynchronous process. In your script, the 2nd request is run before the 1st request is completely finished.
In the case of .catch((error) => {console.log("error");}), error is not correctly shown.

When these points are reflected in your script, how about the following modification?

Modified script:

const token = "###"; // Please set your access token.
const fileName = "sample filename"; // Please set Spreadsheet title.
const sheetName = "sampleSheet1"; // Please set sheet name.

const tableInfo = [
  ["Name", "Age", "Score", "Date", "Tags"],
  ["Name1", "30", "2050", "13-12-2011", "First"],
  ["Name2", "40", "250", "11-12-2001", "First"],
  ["Name3", "35", "1345", "13-02-2011", "Second"]
]
const initialData = {
  properties: { title: `${fileName}` },
  sheets: [{ properties: { title: `${sheetName}` } }],
};
fetch("https://sheets.googleapis.com/v4/spreadsheets", {
  method: "POST",
  headers: {
    "Content-Type": "application/json",
    Authorization: `Bearer ${token}`,
  },
  body: JSON.stringify(initialData),
})
  .then((doc) => {
    return doc.json();
  })
  .then((doc) => {
    console.log("Doc Created!")

    fetch(`https://sheets.googleapis.com/v4/spreadsheets/${doc.spreadsheetId}/values/${sheetName}?valueInputOption=USER_ENTERED`,
      {
        method: "PUT",
        headers: {
          "Content-Type": "application/json",
          Authorization: `Bearer ${token}`,
          Accept: "application/json",
        },
        body: JSON.stringify({ values: tableInfo }),
      }
    )
      .then((updateDoc) => {
        console.log("Doc Updated!! ", updateDoc);
      })
      .catch((err) => {
        console.log("Update Error: ", err);
      });

  })
  .catch((error) => {
    console.log(error);
  });

When this script is run, a new Spreadsheet is created and the values of tableInfo are put into the 1st sheet of the created Spreadsheet.

Note:

In this modification, it supposes that your access token can be used for creating a new Spreadsheet and putting the values to Spreadsheet with Sheets API. Please be careful about this.

References:

Added:

From your following reply,

I applied your changes and I get this error. { "error": { "code": 400, "message": "Invalid values[1][4]: list_value {\n values {\n string_value: "First"\n }\n}\n", "status": "INVALID_ARGUMENT" } } My apologies I forgot to mention that the data columns (in this case "Tags") may contain array of values. how do I fix this.

tableInfo: [["Name","StartDate","Flagged","Tags","Score"],["User1","2023-07-24",true,["First"],"3000"],["User2","2023-07-28",true,["Second"],"2450"],["User3","2023-08-01",true,["Third"],"0"]]}]

I couldn't notice that your value of tableInfo is different from your actual situation. And, I think that your actual value is an invalid array. If your actual value is as follows,

const tableInfo = [
  ["Name","StartDate","Flagged","Tags","Score"],
  ["User1","2023-07-24",true,["First"],"3000"],
  ["User2","2023-07-28",true,["Second"],"2450"],
  ["User3","2023-08-01",true,["Third"],"0"]
];

Please modify this as follows and test it again.

const tableInfo = [
  ["Name","StartDate","Flagged","Tags","Score"],
  ["User1","2023-07-24",true,["First"],"3000"],
  ["User2","2023-07-28",true,["Second"],"2450"],
  ["User3","2023-08-01",true,["Third"],"0"]
].map(e => e.flat());

By this modification, each element of tableInfo is flattened.