CODEWITHSUNDEEP
google-apps-script
Dan Barton
Dan Barton

Reputation: 41

Looping JSON response in google script

We are trying to work out how to loop through a JSON response.

We have managed to call the API of our 3rd party database and pull the first line (the headers) but need to loop through all the rows and then copy them to a google sheets.

Any ideas?

Upvotes: 4

Views: 5516

Answers (1)

Christian4561
Christian4561

Reputation: 72

Not too much information on what information is in the JSON you're receiving or how you're going to handle it, so here's my general answer:

Once you receive the full JSON data, you can turn it into an object by using JSON.parse( jsonString ) where jsonString is the data you received from the API. More on that here.

If your row values are stored in an array, you can easily loop through them using the forEach() method. More on that here. Below is example JSON data and a function to parse through it.

Example Data

{
  "name": "Example Data",
  "rows": [
    {
      "string": "I'm a string",
      "number": 14
    },
    {
      "string": "Chicago",
      "number": 36
    }
  ]
}

Example Parse Function

function handleJsonResponse(data) {
  //Parse response and get sheet
  var response = JSON.parse(data);
  var spreadsheet= SpreadsheetApp.getActive().getSheetByName(response.name);
  if (spreadsheet === null) {
    //Error here
  }

  //Loop through data and add it to spreadsheet
  response.rows.forEach(function( row, index ) { 
    //This function will be executed for every row in the rows array

    //Set the index of the row to the first column in the sheet
    //2 is added to the index for the row number because index starts at 0 and we want to start adding data at row 2
    spreadsheet.getRange(index + 2, 1).setValue(index);

    //Set the value of string to the second column
    spreadsheet.getRange(index + 2, 2).setValue(row.string);

    //Set the value of number to the third column
    spreadsheet.getRange(index + 2, 3).setValue(row.number);

  });
}

If you have any questions feel free to ask.

Upvotes: 2

Related Questions