get content and send API request to Zendesk

gistfile1.txt

function ImportHCcontent(){
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var source = SpreadsheetApp.getActiveSpreadsheet();
  var range = sheet.getDataRange();
  var start_row = 1;
  
  for (var i = start_row; i < range.getLastRow();  i++) {
    
    // get values
    var title = range.offset(i, 15).getValue();
    var body = range.offset(i, 16).getValue();
    var locale = range.offset(i, 18).getValue();
    var section_id = range.offset(i, 11).getValue();
    // var label_names = []; label_names.push(range.offset(i, 5).getValue().split(',')); 
    var label_names = range.offset(i, 5).getValue().split(',');
    var author_id = range.offset(i, 3).getValue();
    var comments_disabled = range.offset(i, 4).getValue();
    var draft = range.offset(i, 6).getValue();
    var promoted = range.offset(i, 7).getValue();
    var position = range.offset(i, 8).getValue();
    var outdated = range.offset(i, 19).getValue();

    // color processed rows
    rowRange = range.offset(i, 0, 1);
    rowRange.setBackgroundColor("#add8e6");
    
    Logger.log("title --> " + title);
    Logger.log("body --> " + body);
    Logger.log("locale --> " + locale);
    Logger.log("section_id --> " + section_id);
    Logger.log("label_names --> " + label_names);
    Logger.log("author_id --> " + author_id);
    Logger.log("comments_disabled --> " + comments_disabled);
    Logger.log("promoted --> " + draft);
    Logger.log("promoted --> " + promoted);
    Logger.log("position --> " + position);
    Logger.log("outdated --> " + outdated);
    
    // prepare ticket payload
    var articles = {"article": {}};
    
    articles.article.title = title;
    articles.article.body = body;
    articles.article.locale = locale;
    // articles.article.section_id = section_id;
    
    if ( label_names ) { articles.article.label_names = label_names } // array
    // if ( author_id ) { articles.article.author_id = author_id*1 } // number
    if ( comments_disabled ) { articles.article.comments_disabled = true } else { articles.article.comments_disabled = false } // boolean
    if ( draft ) { articles.article.draft = true } else { articles.article.draft = false } // boolean
    if ( promoted ) { articles.article.promoted = true } else { articles.article.promoted = false } // boolean
    if ( position ) { articles.article.position = position*1 } // number
    if ( outdated ) { articles.article.outdated = true } else { articles.article.outdated = false } // boolean
    
    Logger.log( "________ARTICLE________" );
    Logger.log( JSON.stringify(articles) );
            
    this.createArticles(articles,i,section_id);
  }
  
};

function createArticles(data,k,section) {
  
  var sid = section;
  var url = zd_url + "/api/v2/help_center/sections/"+sid+"/articles.json";
  
  Logger.log("id ==> " + sid);
  Logger.log("url ==> " + url);
  
  var payload = JSON.stringify( data );  
  var options = {
    "headers": {"Authorization": "Basic " + Utilities.base64Encode(zd_admin_email + "/token:" + zd_admin_token)},
    "method" : "POST",
    "contentType" : "application/json",
    "payload" : payload
  };
  options.muteHttpExceptions = true;
  
  var response = UrlFetchApp.fetch(url, options);
  var response_code = response.getResponseCode();
  var json = response.getContentText();
  var data = JSON.parse(json);
  
  Logger.log("RESPONSE");
  Logger.log(data);
  
  this.recordAPIresonse(data,k,response_code);
}

function recordAPIresonse(data,k,response_code){ // API response handler
  
  var sheet = SpreadsheetApp.getActiveSheet();
  
  var range = sheet.getDataRange();
  if ( response_code < 300 ) {
    // record positive response
    var url = data.article.html_url;
    var title = data.article.title;
    var response = '=HYPERLINK("'+url+'","'+title+'")';
    
    sheet.getRange(k+1, 21).setValue(response);
    sheet.getRange(k+1, 22).setValue(data.article.created_at);
    // color row
    rowRange = range.offset(k, 0, 1);
    rowRange.setBackgroundColor("#99CC99");
  } else {
    // record negative response
    sheet.getRange(k+1, 21).setValue("FAILED");
    sheet.getRange(k+1, 22).setValue(data);
    // color row
    rowRange = range.offset(k, 0, 1);
    rowRange.setBackgroundColor("#ff69b4");
  }

}