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:
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?
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: