Apps Script - Export JSON properly into Google Sheet

Question

I'm quite beginner with Apps Script / Javascript. I have some JSON data that I'm exporting using an API.

Below is what I get when I do: console.log(jsonData);

I'm trying to:

• Transform this JSON data into CSV.
• Put the data in the first sheet of my active spreadsheet

Basically it should look like this:

However I'm stuck at this step and I cannot get the next piece of code right ... (how to transform into CSV and putting the data in a Google sheet).

Below is the beginning of my script:

function myFunction() {
  
  var apiKey = "MyApiKEY";
  var now = new Date();
  var startDate = new Date(now.setDate(now.getDate()-2))
  var endDate = startDate

  var startDateFormatted = Utilities.formatDate(new Date(startDate), "GMT+7", "yyyy-MM-dd");
  var endDateFormatted = Utilities.formatDate(new Date(endDate), "GMT+7", "yyyy-MM-dd");

  var url = 'https://public-api.vendor.com/v1/clicks?start_date='+ startDateFormatted + '&end_date=' + endDateFormatted;
  var options1 = {
    "method": "get",
    "headers": {
      "accept": "application/json",
      "Authorization": apiKey
    }
   }
  var response = UrlFetchApp.fetch(url, options1);
  var jsonData = JSON.parse(response.getContentText());

  // console.log(jsonData);

}

I would appreciate any help in getting those data into a Google Sheet. Also feel free to modify anything in the beginning of my script if you think something is wrong or unnecessary.

Answer 1

I believe your goal is as follows.

• You want to convert your sample data to CSV data.
• You want to put your sample data on the 1st sheet of the active Spreadsheet.

From your showing sample data, if jsonData is your showing sample data, how about the following modification?

From:

var jsonData = JSON.parse(response.getContentText());

To:

var jsonData = JSON.parse(response.getContentText());

// I added the below script.
const headers = ["date_of_report", "vendor_name", "product_name", "category", "avg_cpc", "avg_position", "clicks", "conversions", "conversion_rate", "cost", "cpl", "channel", "country", "email", "vendor_id"];
const values = [headers, ...jsonData.data.map(e => headers.map(h => e[h] || ""))];
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
sheet.getRange(1, 1, values.length, values[0].length).setValues(values);

// If you want to retrieve the values as CSV data. You can use the following script.
const csv = values.map(r => r.join(",")).join("\n");