Google Apps Script获取API数据仅存入单个单元格，如何拆分至多行多列？

Hey there! I see the issue here — right now you're just dumping the raw JSON response text into a single cell instead of parsing it and formatting it into a table structure. Let's get this sorted out for you.

Your current code takes the entire string returned by the API and puts it in cell A1. To split this into rows and columns, we need to:

• Parse the JSON response into a JavaScript object
• Extract the actual data records from the object
• Convert those records into a 2D array that Google Sheets can understand
• Write that array to the sheet using setValues() (instead of setValue(), which only handles single values)

Here's the updated code that will do exactly that:

function spotpriser() { 
  // Add https:// to the API URL (required for UrlFetchApp)
  var apiUrl = 'https://api.energidataservice.dk/dataset/Elspotprices' + 
               '?start=2022-07-01&end=2022-07-02' + 
               '&sort=HourDK'; 
  
  var response = UrlFetchApp.fetch(apiUrl); 
  var jsonData = JSON.parse(response.getContentText()); // Parse JSON into an object
  
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear(); // Optional: Clear existing data before writing new data
  
  // Extract the records array from the API response
  var records = jsonData.records;
  
  if (records.length === 0) {
    sheet.getRange(1,1).setValue("No data returned from API");
    return;
  }
  
  // Create header row from the keys of the first record
  var headers = Object.keys(records[0]);
  var dataArray = [headers]; // Start with headers as the first row
  
  // Add each record's values as a new row
  records.forEach(function(record) {
    var row = headers.map(function(header) {
      return record[header];
    });
    dataArray.push(row);
  });
  
  // Write the entire 2D array to the sheet
  sheet.getRange(1, 1, dataArray.length, dataArray[0].length).setValues(dataArray);
}

Let me break down the key changes:

• Fixed the API URL: Added https:// because UrlFetchApp.fetch() requires a full, valid URL.
• Parsed the JSON: JSON.parse() converts the raw text response into a usable JavaScript object, so we can access the records array that contains all the price data.
• Created headers: We take the keys from the first record (like HourDK, PriceArea, SpotPriceDKK) to make a header row.
• Built a 2D array: Each record's values are added as a new row in the array, matching the order of the headers.
• Used setValues(): This method writes the entire array to the sheet in one go, which is much more efficient than writing cell-by-cell, and it automatically fills rows and columns correctly.

内容的提问来源于stack exchange，提问作者Kim Hinge