Add information to a spreadsheet from the Google API

Question

I have a Typescript and Node project in which I am trying to insert the information I get from the database into the spreadsheet using the Google API V4

https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdate

This is the JSON object that I get from the database:

let sheetData = [
  {
     "country":null,
     "age":25,
     "fileName":"testName1"
  },
  {
    "country":"Spain",
    "age":null,
    "fileName":"testName2"
  }
]

I transform it with papaparse:

const papa = require("papaparse")

let result = papa.unparse(sheetData, {
  header: false,
  delimiter: ';',
  encoding: 'UTF-8',
})

console.log(result)

This is what I get:

;25;testName1
Spain;;testName2

This is the xml that I use from the API to add the information:

{
  "requests": [
    {
      "pasteData": {
        "coordinate": {
          "sheetId": 123,
          "rowIndex": 2,
          "columnIndex": 1
        },
        "delimiter": ";",
        "type": "PASTE_VALUES",
        "data": ";25;testName1Spain;;testName2"
      }
    }
  ]
}

I attach a screenshot with the result of the sheet:

My problem: All the information is put in the same row, how do I have to modify the array to include line breaks and be identified by the API?

This is the JSON that works from the API by adding \n:

{
  "requests": [
    {
      "pasteData": {
        "coordinate": {
          "sheetId": 123,
          "rowIndex": 2,
          "columnIndex": 1
        },
        "delimiter": ";",
        "type": "PASTE_VALUES",
        "data": ";25;testName1\nSpain;;testName2"
      }
    }
  ]
}

This is the result I want to achieve, but I don't know how to treat the JSON with the information I get:

Could you share an example sheet? A [minimal example](https://stackoverflow.com/help/minimal-reproducible-example) can help identify what could be happening. — Gabriel Carballo, Mar 21 '22 at 21:30
@GabrielCarballo I've added a screenshot of the spreadsheet output, but the rest of the process is already in the question — menphis, Mar 21 '22 at 21:47
@GabrielCarballo I just updated the question again with what I want to achieve — menphis, Mar 22 '22 at 00:28

score 1 · Answer 1 · answered Mar 22 '22 at 01:07

I thought that in your script, how about the following sample script?

Sample script:

const sheets = google.sheets({ version: "v4", auth }); // Please use your authorization script.

let sheetData = [
  {
    country: null,
    age: 25,
    fileName: "testName1",
  },
  {
    country: "Spain",
    age: null,
    fileName: "testName2",
  },
];

let result = papa.unparse(sheetData, {
  header: false,
  delimiter: ";",
  encoding: "UTF-8",
});

console.log(result);

const requests = {
  requests: [
    {
      pasteData: {
        coordinate: {
          sheetId: 123, // Please set your sheet ID.
          rowIndex: 2,
          columnIndex: 1,
        },
        delimiter: ";",
        type: "PASTE_VALUES",
        data: result,
      },
    },
  ],
};
sheets.spreadsheets.batchUpdate(
  {
    spreadsheetId: "###", // Please set your Spreadsheet ID.
    resource: requests,
  },
  (err, result) => {
    if (err) {
      console.log(err);
      return;
    }
    console.log(result.data);
  }
);

In this modification, your value of result is used as the data of pasteData request.
When I tested this script, I confirmed that your expected result can be obtained.

Add information to a spreadsheet from the Google API

1 Answers1

Sample script: