Google App Script - can't setValue with data from API

Question

I have a Google script which calls an API, and I want to put the results into a Google Sheet. My code is below. I can get the response from the API no problem but I can't then insert it into the sheet. When I run the script I get an error:

Exception: The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues.

Can anyone advise on what I am doing wrong here?

// custom menu
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Get Data','displayData')
      .addToUi();
}

// function to call API
function calldata() {
  
  // Call the API
    var response = UrlFetchApp.fetch("https://webhooks.mongodb-realm.com/api/client/v2.0/app/cards-fvyrn/service/Cards/incoming_webhook/getCardMetadata");

  
  // Parse the JSON reply
  var json = response.getContentText();
  var data = JSON.parse(json);
  
  Logger.log(data[0]); <--first item in the returned data
  Logger.log(data.length);
  
  return data;
  
}

function displayData() {
  var data = calldata();
  var numRows = data.length  
  var sheet = SpreadsheetApp.getActiveSheet();
  //var headerRow = ['brand', 'manufacturer', 'variation']
  //sheet.appendRow(headerRow)
 
  var newLine = sheet.getLastRow() + 1;
  var newRange = sheet.getRange(newLine,1,numRows,4);
  newRange.setValues(data); 
}

The returned data looks like this (truncated)

 [{_id={$oid=5fd5de40134d172aa8279cac}, manufacturer=SAGE, brand=, variation=Jerseys Printing Plates Yellow}, {_id={$oid=5fd5de40134d172aa8279cad}, variation=Crusade Materials Prime, brand=Rookies and Stars, manufacturer=Leaf}, {variation=Biography of a Legend Autographs Silver, manufacturer=SP, brand=Chirography, _id={$oid=5fd5de40134d172aa8279cae}}, {variation=Future Star Materials Gold, manufacturer=Upper Deck, _id={$oid=5fd5de40134d172aa8279caf}, brand=Rookie Debut}, {manufacturer=SPx, brand=, variation=Rookie Autographs NFL Logo, _id={$oid=5fd5de40134d172aa8279cb0}}, {_id={$oid=5fd5de40134d172aa8279cb1}, brand=Absolute Memorabilia, manufacturer=Playoff, variation=Team Quads Materials Prime Spectrum}, {_id={$oid=5fd5de40134d172aa8279cb2}, variation=Century Club Printing Plates Yellow, brand=Aspire, manufacturer=Sage}, {manufacturer=Upper Deck, variation=Sweet Leather Signatures Dual, brand=Sweet Spot, _id={$oid=5fd5de40134d172aa8279cb3}}, {brand=Premier, _id={$oid=5fd5de40134d172aa8279cb4}, variation=Stitchings Variation, manufacturer=Upper Deck}, {manufacturer=Upper Deck, variation=Loyalty Signatures, _id={$oid=5fd5de40134d172aa8279cb5}, brand=Ultimate Collection}, {brand=Premier, _id={$oid=5fd5de40134d172aa8279cb6}, variation=Stitchings Variation Platinum, manufacturer=Upper Deck}, {variation=Rookie Swatch Supremacy, manufacturer=SPx, brand=, _id={$oid=5fd5de40134d172aa8279cb7}}, {brand=Certified Materials, variation=Certified Skills Blue, _id={$oid=5fd5de40134d172aa8279cb8}, manufacturer=Leaf}, {manufacturer=Playoff, brand=Absolute Memorabilia, variation=Star Gazing Materials Prime Oversize Spectrum, _id={$oid=5fd5de40134d172aa8279cb9}}, {brand=Absolute Memorabilia, variation=War Room Spectrum, _id={$oid=5fd5de40134d172aa8279cba}, manufacturer=Playoff}, {brand=Cerified Materials, _id={$oid=5fd5de40134d172aa8279cbb}, variation=Certified Potential Gold, manufacturer=Certified}, {variation=Spectrum Platinum Autographs, _id={$oid=5fd5de40134d172aa8279cbc}, brand=Absolute Memorabilia, manufacturer=Playoff}, {_id={$oid=5fd5de40134d172aa8279cbd}, manufacturer=Playoff, brand=Absolute Memorabilia, variation=Team Trios Silver}, {manufacturer=Playoff, variation=Xtra Points Black, _id={$oid=5fd5de40134d172aa8279cbe}, brand=Prestige}, {brand=Prestige, _id={$oid=5fd5de40134d172aa8279cbf}, manufacturer=Playoff, variation=Gridiron Heritage Materials Prime}, {manufacturer=Topps, brand=, _id={$oid=5fd5de40134d172aa8279cc0}, variation=Hall of Fame Tribute Cut Autographs}, {brand=National Treasures, variation=75th Anniversary Team Materials, manufacturer=Playoff, _id={$oid=5fd5de40134d172aa8279cc1}}, {manufacturer=Topps, brand=Draft Picks and Prospects, _id={$oid=5fd5de40134d172aa8279cc2}, variation=Senior Standout Jersey Combos Holofoil}, {_id={$oid=5fd5de40134d172aa8279cc3}, brand=Gridiron Gear, manufacturer=Donruss, variation=}, {variation=Chrome Silver, brand=Draft Picks and Prospects, manufacturer=Topps}]

Duplicate of https://stackoverflow.com/questions/63720612/what-does-the-range-method-getvalues-return-and-setvalues-accept — TheMaster, Dec 15 '20 at 14:36
Thanks @TheMaster. So would the advice be to convert to a 2d array or is there another way? — Stuart Brown, Dec 15 '20 at 14:53
@StuartBrown check my answer. But please go through the post TheMaster has mentioned. — Marios, Dec 15 '20 at 14:54
@Marios, sorry yes our posts crossed. I have checked you answer and it works perfectly - many thanks — Stuart Brown, Dec 15 '20 at 14:56

score 1 · Accepted Answer · answered Dec 15 '20 at 14:51

Explanation:

Please read carefully the post of TheMaster which clearly explains how to set values in a sheet.

In your particular case, you need to convert the json object returned by the API to a 2D array that will be pasted in the sheet. You can do that with a forEach or whatever loop of your choice:

var outputData = [['brand', 'manufacturer', 'variation']];
data.forEach(r=>{
    outputData.push([r.brand,r.manufacturer,r.variation])    
  });

Solution:

Replace the current displayData with the following:

function displayData() {
  var data = calldata();
  var sheet = SpreadsheetApp.getActiveSheet();
  var outputData = [['brand', 'manufacturer', 'variation']];
  data.forEach(r=>{
    outputData.push([r.brand,r.manufacturer,r.variation])    
  });
  var newLine = sheet.getLastRow() + 1;
  var newRange = sheet.getRange(newLine,1,outputData.length,outputData[0].length);
  newRange.setValues(outputData); 
}

Google App Script - can't setValue with data from API

1 Answers1

Explanation:

Solution: