Data into rows and colums-CodePudding

I'm relatively new to this, I've written a function in Google Apps Script, getting data from an API.

The problem is that it's inserted into one single cell, instead of multiple rows and columns.

Can anybody help?

Tried googling examples

What I do

function spotpriser() { 
  var priser = 'api.energidataservice.dk/dataset/Elspotprices'   '?start=2022-07-01&end=2022-07-02'   '&sort=HourDK'; 
  var response = UrlFetchApp.fetch(priser); 
  Logger.log(response); 
  var fact = response.getContentText(); 
  var sheet = SpreadsheetApp.getActiveSheet(); 
  sheet.getRange(1,1).setValue([fact]); 
}

CodePudding user response：

if you do getRange(1,1).setValue() it is normal that all the data get inserted into one cell only: the one define by the range (aka A1).

Instead what you probably want to do is to get a larger range, corresponding the dimensions of your retrieved data.

For example, let's say you retrieved 3 lines of data, and one line of data is supposed to be displayed on 2 columns.

You first need to create an array of size 5 (nb of rows) where each element will also be an array, of size 2 (nb of cols for each element).

let myArray = [
  [elt1_col1_value, elt1_col2_value],
  [elt2_col1_value, elt2_col2_value],
  [elt3_col1_value, elt3_col2_value]
]

Then you can insert this array into the right number of cells, i.e defining the right range

sheet.getRange(1,1,myArray.length,myArray[0].length).setValues(myArray); // start at row 1, col 1, insert the right number of rows and cols based on the array

Note that I use setValues (with the s) to indicate that it writes in several cells.

Also make sure there is at least one element in myArray otherwise myArray[0].length will throw an error.

Documentation for getRange.