CODEWITHSUNDEEP
jsongoogle-apps-script
Juan Sebastian
Juan Sebastian

Reputation: 3

Google Apps Script. JSON to Spreadsheet - Result too long (API to Sheet) Work only with 100 reg

Good morning, I have made a Script that connects with an APi, it returns a JSON, I serialize it and complete an array with it .. then I paste it in a Google Spreadsheet, but it only works with 100 records. However if in the same Script, under the JSON my Google Drive makes it complete. When I check the Logger it tells me the result is too long ...

My code.

function getOT() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('OT');
  var url ='https://app.XXXXX.com/api/work_orders';
  var authorization=XXXXXXApi(XXX,'app.XXXXXX.com','/api/work_orders','GET','443');
  var Options ={
  "url": "https://app.XXXXXX.com/api/work_orders",
  "method": "GET",
  "timeout": 0,
  "headers": {  "Authorization": "Hawk id=\""+authorization.cID+"\", ts=\""+ authorization.cTS+"\", nonce=\""+authorization.cNONCE+"\", mac=\""+authorization.cHMAC+"\"",
  },
};

 var response = UrlFetchApp.fetch('https://app.XXXX.com/api/work_orders', Options);
 var array = [];
 var CC = response.getContentText();
 var datos = JSON.parse(CC);
 Logger.log (CC)

 for (var i = 0; i < datos.data.length ;i++) {
  array.push([datos.data[i]['wo_folio']],[datos.data[i]['description']],[datos.data[i]['personnel_description']],[datos.data[i]['items_log_description']])
  
 };
           
  for (var i = 0; i < datos.data.length ;i++){
     var startrow=2 + +i;
    sheet.getRange(startrow, 1).setValue(datos.data[i]['wo_folio']);
    sheet.getRange(startrow, 2).setValue(datos.data[i]['description']);
    sheet.getRange(startrow, 3).setValue(datos.data[i]['personnel_description']);
    sheet.getRange(startrow, 4).setValue(datos.data[i]['items_log_description']);
   }
 
DriveApp.createFile('XX.csv', CC); //copio el JSON a mi drive

}

Upvotes: 0

Views: 1582

Answers (2)

Juan Sebastian
Juan Sebastian

Reputation: 3

I have put the size of the data, and I figure 100

var CC = response.getContentText();
var datos = JSON.parse(CC);
var registros=datos.data;
Logger.log(registros.length);

[21-01-03 00:19:21:398 ART] 100.0 .. but i have too many more...

If I run the query from postman, it has more than 300 records .. it only happens when I query it from GAS

Upvotes: 0

Mart&#237;
Mart&#237;

Reputation: 2851

The Logging output too large. Truncating output. message is not an error, it's just a notice. It simply doesn't show the entire data, only a subset. This means that the data is actually there but is not shown to you when using a single call. Try logging the output inside a loop and print all the elements.

As for the TypeError: Cannot read property 'wo_folio' of undefined, it seems that this happens because you are modifying increasing the length property of datos.data:

const array = ['a', 'b']
array.length = 3
console.log(array) // outputs ['a', 'b', empty]

Notice that if I try to get the third element of array 2, you'll get undefined. In a similar way, datos.data[i]['wo_folio'] will become undefined['wo_folio'] if iterated outside the original length, and thus the error is thrown. And just so you know, changing an array length via its length property is rarely used.

Upvotes: 2

Related Questions