You don’t have to pay a single penny to Lodgify Integration with google sheets using Google Apps script.
Lodgify is a simple vacation rental software. We will create a Google Sheets feed by some basic booking information (about 8 data fields) pulled from Lodgify bookings using Lodgify API and also set the trigger to fetch the data automatically in the sheet.
It’s really easy to integrate Lodgify API with Google sheets using Google Apps script.
You first need to create a new Google Sheet where all your Lodgify Booking shall be exported. Once it’s created, you can add the fields that you want exporting to the Google Sheets.
I have added 8 fields to my sheet: Guest Name, Booking Property Name, Booking Date Arrival, Booking Date Departure, Booking Total Amount, Balance due, Booking Source Name, Booking Source Type
Once we’ve created the sheet, we need to create the Google Apps Script, The script will get data from the Lofgify API and that data will be parsed & added to the sheet.
You can go to the Tools menu & click on the Script Editor.
Below are the functions that I created in my script:
1. onOpen
2. getLodgifyData
3. createTrigger
function onOpen(){
var ui = SpreadsheetApp.getUi();
ui.createMenu('Menu')
.addItem('Get Lodgify Data', 'getLodgifyData')
.addSeparator()
.addItem('Create Trigger', 'createTrigger')
.addToUi();
}
function getLodgifyData(){
var data = {
'X-ApiKey': 'yours API key'
};
var options = {
'method' : 'get',
'contentType': 'application/json',
'headers' : data
};
var count = 0;
var url = 'http://api.lodgify.com/v1/reservation?limit=50&status=Booked&trash=False&periodStart=2018-01-01';
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('sheet1');
var lastRow = sheet.getLastRow();
if(lastRow > 1){
sheet.insertRowsAfter(lastRow, 1);
sheet.deleteRows(2, lastRow-1);
}
do{
var response = UrlFetchApp.fetch(url, options);
var data = JSON.parse(response.getContentText());
Logger.log(data)
var completeRow = [];
for(var i = 0; i < data.items.length; i++) {
var row = [];
row.push(data.items[i].guest.name)
row.push(data.items[i].property_name)
row.push(data.items[i].arrival)
row.push(data.items[i].departure)
row.push(data.items[i].total_amount)
row.push(data.items[i].amount_to_pay)
row.push(data.items[i].source)
row.push(data.items[i].type)
completeRow.push(row)
}
sheet.getRange(sheet.getLastRow()+1, 1, completeRow.length, 8).setValues(completeRow);
//Logger.log(completeRow)
url = data.next;
count = count + data.items.length;
Logger.log(count)
Logger.log(url)
}while(count < data.total)
var range = sheet.getRange("A2:H"+lastRow);
// Sorts by the values in column 3 (C)
range.sort({column: 3, ascending: true});
}
function createTrigger() {
// Trigger every 10 minute
ScriptApp.newTrigger('getLodgifyData')
.timeBased()
.everyMinutes(5)
.create();
}
In the script, you have to change a few things.
1. Line no 13 put your API Key. To obtain your API Keys you can get in touch with Lodgify at help@lodgify.com and They will provide you a pair of read and write keys.
2. Line no 22 change the date according to your data
3. Line no 23 change the sheet name if your sheet name is different the “Sheet1”
After that save your code and Back to the sheet and refresh your sheet.
In the sheet, you will see the menu. Almost the Lodgify Integration with google sheets is done
There is Two Menu option
1. Get Lodgify Data
Run the first menu ‘Get Lodgify Data’ after that script will redirect to the authorization page. Please authorize the code and after authorization, data will fetch successfully and looks like below:
2. Create Trigger
If you want to fetch the data automatically then run the second menu trigger will set automatically and fetches the data after 5 minutes automatically.
I hope you enjoying the post and you achieve the goal. If there is any issue or need new development please connect with me.