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 )
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
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()); } } |
Leave a Reply