• Home
  • Cloud
    • General
    • SaaS
    • BPaaS
    • PaaS
    • IaaS
    • Other Internet Hosted Applications
      • WordPress
        • WooThemes Canvas
          • WooThemes Canvas CSS
  • About me
  • Why Badly Wired?
  • Contact

Badly Wired

Connecting WordPress via APIs, Plugins and other stuff - a technical notebook

You are here: Home / Code snippets / WordPress REST API retrieve data and email it as an xls using Google Sheets

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

31st July 2018 by Alan Leave a Comment

Fully Managed UK Hosting - Only £1+VAT till 1st Jan 2021 on Shared, Reseller and Dedicated Hosting! .... 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());
  }
}
[Next] Find out where to host WordPress [Read the full article…]

Fully Managed UK Hosting - Only £1+VAT till 1st Jan 2021 on Shared, Reseller and Dedicated Hosting! .... read more ....

Filed Under: Code snippets, Google Cloud, Wordpress  

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • Applications
  • Cloud
    • General
    • Google Cloud
    • IaaS
    • Other Internet Hosted Applications
      • Wordpress
        • WooThemes Canvas
        • WooThemes Canvas CSS
    • SaaS
  • Code snippets
  • Discounts
  • Genesis
  • Google Apps for Works
  • Linux
  • News
  • SEO
  • Server setup
  • Services
  • Tech Tips
  • Uncategorised
  • Useful Images
  • Useful Stuff
  • WordPress Hosting
  • WordPress Plugins

Tags

background jobs beadcrumbs bind brandings Cache canvas Centos chrome css fail2ban Find firefox Flash fraud genesis gocardless godaddy Google google maps hackers internet explorer javascript KashFlow Linus linux Magento mapquest maps microsoft mysql news nohup php plugin plugins queens diamond jubilee replace SED SEO skype Varnish Virtualmin Webmin woothemes Wordpress

 

Affiliate and Privacy Notices

This site is free to use, but hopes to cover some costs through affiliate income, some products and links are affiliates and may earn the site advertising income.

Some affiliates use Cookies to track if you purchase from them, this allows them to apportion revenue to us you will need to refer to their specific privacy notices as to how you are tracked.

This site is a participant in the Amazon EU Associates Programme, an affiliate advertising programme designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.co.uk.

  • Privacy Policy

Copyright © 2021 · Badly Wired