(3hrs after initial post - edited examples to be more clear, including current and desired output)

I have a spreadsheet with a few thousand rows of data, each row approximately 30 columns long. On a daily basis this sheet has additional rows added (but never removed). The headers are the same for all columns, but each row does not always have the same columns populated. i.e. sometimes all 30 columns are populated, other times it may be only 20.

I want to loop through these rows and turn each row into a JSON formatted API request. The API does not recognize null, empty or other non-Boolean values so I need some way to adjust the JSON fields for each submission to remove these empty fields from the JSON object. Additionally, the JSON object does contain some arrays, I'm not sure if this makes a difference or not.

So far I have a looping script working but without any way to remove the fields which are empty in the google sheet. I know I can use filter.Boolean to clean my initial array, but I don't know how to create a dynamic structure for my JSON object that can loop each row.

I am a hobby developer so I feel quite out of my depth here. Any help would be really appreciated!

Here is what is working so far, with some comments included for reference.

function googleSheetsToTrackingDBAPI() {

  var data = []; // Make the array empty
  var sheet = SpreadsheetApp.getActive().getSheetByName("trackingDB");
  var data = sheet.getDataRange().getValues(); //I don't currently have headers on this sheet but they can be added.
  var dataLength = data.length; //Length of the data to use for loop
  Logger.log(dataLength);  //Checking for length of each loop

  // Now to make an array and store all my data in it. 
  for (var i=0; i<dataLength; i  ) {

var ModeCode = data[i][0];
var score = data[i][1];
var attempt = data[i][2];
var difficultyCode = data[i][3];
var description = data[i][4];
var Link = data[i][5];
var season = data[i][6];
var TypeCode = data[i][7];
var languageCode = data[i][8];
var defUnit1 = data[i][9];
var defUnit2 = data[i][10];
var defUnit3 = data[i][11];
var atkUnit1 = data[i][12];
var atkUnit2 = data[i][13];
var atkUnit3 = data[i][14];
var atkSetCode = data[i][15];
var CodesAtk1 = data[i][16];
var CodesAtk2 = data[i][17];
var CodesAtk3 = data[i][18];
var defSetCode = data[i][19];
var CodesDef1 = data[i][20];
var CodesDef2 = data[i][21];
var CodesDef3 = data[i][22];

    // To filter out null, empty, etc via filter(Boolean).  It works but the JSON data is an object so this won't help with JSON structure.
    //var filteredData = data.filter(Boolean);
    //Logger.log(filteredData);

    var payload = 
          [{
            ModeCode,
            atkTeamCodes : [
            atkUnit1,
            atkUnit2,
            atkUnit3
          ],
            defTeamCodes : [
            defUnit1,
            defUnit2,
            defUnit3
          ],
          atkSetCode,
          atkAbilityCodes : [
            CodesAtk1,
            CodesAtk2,
            CodesAtk3
          ],
          defSetCode,
          defAbilityCodes : [
            CodesDef1,
            CodesDef2,
            CodesDef3
          ],
            TypeCode,
            difficultyCode,
            score,
            season,
            attempt,
            Link,
            description,
            languageCode,
          }];
      Logger.log(payload);
    
    var modPayload = JSON.stringify(payload);
    Logger.log(modPayload);

      var headers = {
          "API-KEY": 'itsakey'
      }

      var trackingDBurl = "https://text.com/textapiurl"
    
      var trackingDBparams = { 
        "method":"post",
        "contentType":"application/json",
        "headers": headers,
        "payload": modPayload
      }

    var postPayload = UrlFetchApp.fetch(trackingDBurl, trackingDBparams);
}}

Here is an example of a few rows from the spreadsheet (I added headers for clarity in this post, though in script I don't account for them).

Current output;

[{"ModeCode":"Flying","atkTeamCodes ":["Unit12","Unit13","Unit14"],"defTeamCodes ":["Unit1","Unit2",""],"atkSetCode":"","atkAbilityCodes ":["","",""],"defSetCode":"","defAbilityCodes ":["",null,null],"TypeCode":"Competitive","difficultyCode":"Easy","score":75,"season":11,"attempt":1,"Link":"https://youtu.be/blahblahblah1","description":"Easy win","languageCode":"ENG"}]

[{"ModeCode":"Small","atkTeamCodes ":["Unit4","",""],"defTeamCodes ":["Unit9","Unit10",""],"atkSetCode":"3","atkAbilityCodes ":["code1","code2","code3"],"defSetCode":"","defAbilityCodes ":["",null,null],"TypeCode":"Competitive","difficultyCode":"Complex","score":2,"season":13,"attempt":1,"Link":"https://youtu.be/blahblahblah3","description":"Very difficult to win","languageCode":"ENG"}]

Desired output:

[{"ModeCode":"Flying","atkTeamCodes ":["Unit12","Unit13","Unit14"],"defTeamCodes ":["Unit1","Unit2"],"TypeCode":"Competitive","difficultyCode":"Easy","score":75,"season":11,"attempt":1,"Link":"https://youtu.be/blahblahblah1","description":"Easy win","languageCode":"ENG"}]

[{"ModeCode":"Small","atkTeamCodes ":["Unit4"],"defTeamCodes ":["Unit9","Unit10"],"atkSetCode":"3","atkAbilityCodes ":["code1","code2","code3"],"TypeCode":"Competitive","difficultyCode":"Complex","score":2,"season":13,"attempt":1,"Link":"https://youtu.be/blahblahblah3","description":"Very difficult to win","languageCode":"ENG"}]

I'm a bit lost if I can even keep this structure? Maybe I have to move the dynamic part to the array creation and base that off of each row in the spreadsheet? But I'm not sure how to create a JSON object new each time including only specific element names.

Any and all suggestions would be greatly appreciated. Thank you!!

CodePudding user response：

To remove empty (null, undefined, empty string) values from a javascript object, you can iterate over the keys with for...in, and then check the corresponding value, like this:

var json = payload[0]
var newJson = {}
for (var key in json)
{
  if (!(json[key] === null || json[key] === undefined || json[key] === ''))
  {
    newJson[key] = json[key]
  }
}

Then use newJson in your API call.

This takes care of everything except those arrays of empty items. That can be detected with array.every, or in the case of partially empty arrays, reduced with .filter(Boolean):

var json = payload[0]
var newJson = {}
for (var key in json)
{
  if (!(json[key] === null || json[key] === undefined || json[key] === ''
    || (Array.isArray(json[key]) && json[key].every(function (item)
    {
      return (item === '' || item === null || item === undefined)
    }))
  ))
  {
    if (Array.isArray(json[key])) {
      newJson[key] = json[key].filter(Boolean)
    } else {
      newJson[key] = json[key]
    }
  }
}

or perhaps more concise/readable:

var isEmpty = function (x) { return (x === '' || x === null || x === undefined) }

var newJson = {}
for (var key in payload[0])
{
  var value = payload[0][key]
  if (!(
    isEmpty(value) || (Array.isArray(value) && value.every(isEmpty))
  ))
  {
    if (Array.isArray(value)) {
      newJson[key] = value.filter(Boolean)
    } else {
      newJson[key] = value
    }
  }
}
var newPayload = [newJson]