WordPress REST API retrieve data and email it as an xls using Google Sheets

If you find this free website useful – why don’t you support this with a donation? It is easy…. read more ….

Google Script to read a WordPress REST API endpoint into a Google Sheet and then email it ( no titles in this one, see other tutorials for titles ) 

function onOpen() {
  // setup a custom menu
  SpreadsheetApp.getUi() 
      .createMenu('Barcodes')
      .addItem('Get Data', 'getPosts')
      .addToUi();
  setupSheet();
}

function setupSheet() {
  // just clear sheet and set & style headings
 var ss = SpreadsheetApp.getActiveSpreadsheet();
  // set the tab name - just for neatness
 var sheetname = ss.getSheets()[0].setName('Data');
  // clear the sheet
 var range = ss.getDataRange();
 range.clear();
}


function getPosts(){
  setupSheet();
  // loop here as we need get back posts in chunks, Rest API max is 100 post
  offset = 0;
  per_page = 10;
  while ( (got=getPage(offset,per_page)) > 0 ) {
    offset+=got;
  }
  SpreadsheetApp.flush();
  sendGoogleSpreadsheetAsExcel();
}

function getPage(offset,per_page) {
  // gets posts in chunks of per_page
  var ui = SpreadsheetApp.getUi();  // used for error messages
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  var options = {
      'method': 'get',
      "contentType" : "application/json",
      'muteHttpExceptions': true
    }
  var apiHost = 'https://YOURDOMAIN/wp-json';   // set to your own domain
  url = apiHost + '/wp/v2/ENDPOINT?per_page='+per_page+'&offset='+offset;  // set to appropriate END POINT
  try {
    var response = UrlFetchApp.fetch(url, options);
    var data = JSON.parse(response)
    // loop through the map and output to sheet
    for (i = 0; i < data.length; i++) {
     row=offset+i+1;  
     ss.getRange('A'+row).setValue(data[i].title.rendered);   // work out the data structure by looking at the JSON
     ss.getRange('B'+row).setValue(data[i].barcode_id);       // work out the data structure by looking at the JSON
  }
    return data.length;
  } catch(error) {
    var result = ui.alert( error.toString());
  }
  return 0;
}

function sendGoogleSpreadsheetAsExcel(){
  
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + ss.getId() + "&exportFormat=xlsx";
    var params = {
      method      : "get",
      headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
    };
    var blob = UrlFetchApp.fetch(url, params).getBlob();
    blob.setName(ss.getName() + ".xlsx");
    // change the EMAIL address
    MailApp.sendEmail("youremail@example.com", "Google Sheet to Excel", "The XLSX file is attached", {attachments: [blob]});   
  } catch (f) {
    Logger.log(f.toString());
  }
}