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());
}
}
by
Tags:
Leave a Reply