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://itsupportdesk1611575857.zendesk.com/api/v2/ticket_fields';
  var user = '[email protected]/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:

CodePudding user response：

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 = { "ticket_field" : result}

Reference:

Array

CodePudding user response：

Try

function table2json() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('YourSheetName');
  var [headers, ...rows] = sheet.getDataRange().getValues();
  var data = {}
  var items = []
  rows.forEach(function(r) {
    var obj={}
    r.forEach(function (c, j) {
      obj[headers[j]] = c
    })
    items.push(obj)
  })
  data['ticket_field'] = items
  Logger.log(JSON.stringify(data))
}

you will get

{"ticket_field":[{"type":"text","title":"Summary"},{"type":"Multi-line text","title":"Description"},{"type":"Drop-down list","title":"Choose a thing"}, ... ]}