jsongoogle-apps-scriptpostzendesk-api

Loop though sheet to create JSON key-value pairs for API POST


I am attempting to write a POST method Google Apps Script a the moment (quite an achievement for someone scratching around trying to learn as he goes) and have managed step one of my goal which is an initial script that works - in that it runs and creates a field in Zendesk providing I 'hard code'/explicitly write the key-value pairs in the script (as shown below).
What I have been trying to do now is loop through the rows in Sheet to get the key-value pairings and POST(?) that for each row/entry thus allowing me to create multiple fields simply by entering the data in a sheet.

I am sure that it's going to be a for loop but i've hit a brick wall trying to actually figure it out and was hoping someone here could help please.

I have my data in a Sheet in columns A:B (attributes are Type and Title) if that makes sense? I get frustrated that I know just enough to get me going but not quite how to finish it :-(

function CreateField2(){
  var sheet = SpreadsheetApp.getActiveSheet(); // data i want to use is here in columns A:B (type, text) - this will be expanded to other attributes eventually
//this is my data explicitly called out which works fine
  var data = {"ticket_field": {
    "type": "text", "title": "Age"}
  };      
//how do i take the values from my sheet and use them here?

  var url = 'https://url.com/api/v2/ticket_fields';
  var user = 'myaddress@somewhere.com/token';
  var pwd = 'myAPItokenHere';
  var options = {
      'method' : 'post',
      'headers': {
        'Authorization': "Basic " + Utilities.base64Encode(user + ':' + pwd)
      },
      'payload' : JSON.stringify(data),
      'contentType': 'application/json',
      'muteHttpExceptions': true
  };
  UrlFetchApp.fetch(url, options);
}

My data in the sheet will look like this: enter image description here

UPDATE: Following comment below regarding objects vs array of objects. I think what i actually need to do is alter the loop so that it runs the post method for each iteration? Added the following:

    // Can i change the array of objects into separate objects?
const things = [
  data // this is the data gathered using either of the proposed methods
];
const filteredArr = things.reduce((thing, current) => {
  const x = thing.find(item => item.place === current.place);
  if (!x) {
    return thing.concat([current]);
  } else {
    return thing;
  }
}, []);
console.log(filteredArr)
// End of newly added code
//Note:  Not sure this is the right approach, i think i might need to to iterate over and perform the post function for each one?

Solution

  • Replace this :-

    var sheet = SpreadsheetApp.getActiveSheet(); // data i want to use is here in columns A:B (type, text) - this will be expanded to other attributes eventually
    //this is my data explicitly called out which works fine
      var data = {"ticket_field": {
        "type": "text", "title": "Age"}
      };      
    

    With this:-

      const ss = SpreadsheetApp.getActiveSpreadsheet()
      const ssSource = ss.getSheetByName('Sheet1')
      const dataRange = ssSource.getDataRange().getValues();    
      var result = [];
      var head = dataRange[0]; // Getting Head Row
      var cols = head.length;
      var row = [];
      for (var i = 1; i < dataRange.length; i++)
      {
        row = dataRange[i]; // Getting data Rows
        var obj = {}; // Clearing Object
        for (var col = 0; col < cols; col++) 
        {
          obj[head[col]] = row[col];  // Assigning values to Keys  
        }
        result.push(obj);  // Pushing Object
      } 
      const data = JSON.stringify({ "ticket_field" : result}).replace(/[[\]]/g, '')
    

    You can remove JSON.stringify in option parameter

    Reference:

    Array

    Replace