After years of subscribing to Zapier (an automation service that manages swapping data between online services, purposefully unlinked) to handle my dwindling online orders I get from my Analog Conversion Services and online novelties store, I've finally cobbled together my own replacement! Since I'm already a devoted user to the Google Apps ecosystem, and was already keeping a few Spreadsheets that act as local databases of incoming PayPal / eBay orders. I was using Zapier (at $29.99 a month) to transfer the data, now I've written a fully free replacement with Google Apps Script! (This should work with free Google Apps accounts as well. You'll be publishing your spreadsheet as a Web App, and allowing pretty lax permissions to your database/spreadsheet, so keep your new API URL secret from just about everybody.)
PayPal has a free Instant Payment Notification service that will handshake with your private Host, and send a copy of all incoming orders (and their payment status) within seconds of receipt. Our Google Apps Script code will act as that Host and copy the sent data into a Google Spreadsheet.
- Open a New Google Spreadsheet. Give it a good, proper name (saved to a rememberable spot), and create the header row by copy and pasting the following text into the first top left field. This will 'mirror' your Spreadsheet with the right header rows that PayPal would use, as it's tab delimited. It wouldn't hurt to name this specific Sheet something unique besides 'Sheet1' while you're here (bottom of page tab).
- From that new Google Sheet, open the Apps Script menu as seen below:
You'll be greeted with the Apps Script editor, and you should be ready to start adding code to the Sheet. I'll skip the history lessons and say it's using a version of Javascript, so the code will look familiar. Also, if this code doesn't work for you, I'm not going to help you with it. It's on you to pay someone else to figure it out, not ask me for (more) free advice!
- You can copy and paste my code below into the Apps Script editor, removing anything already there. Don't worry about saving, as it happens automatically in this Editor!
function doPost(e) { // Accepts POST submissions to this form (no GET) var PayPal_POST_parameters, PayPal_PostBackURL, PayPal_Data_Received_Object; try{ // Comment out one of the below lines, depending on your use-case: // PayPal_PostBackURL = "https://ipnpb.paypal.com/cgi-bin/webscr"; // -LIVE PayPal! PayPal_PostBackURL = "https://ipnpb.sandbox.paypal.com/cgi-bin/webscr";// -Sandbox PayPal! // Parameter is an Apps Script property name that gets an object of all the data PayPal_Data_Received_Object = e.parameter; // Add an element to the object with the property name 'cmd' PayPal_Data_Received_Object.cmd = '_notify-validate'; // header for User-Agent https://developer.paypal.com/docs/classic/ipn/integration-guide/IPNImplementation/ PayPal_POST_parameters = { 'method': 'post', 'headers': {'User-Agent':'GoogleAppsScript-IPN-VerificationScript'}, 'payload': PayPal_Data_Received_Object } // Submit a POST request back to PayPal to complete the handshake var resp = UrlFetchApp.fetch(PayPal_PostBackURL, PayPal_POST_parameters); // Logs PayPal Responce Logger.log('resp', resp) // Push Data to your blank Sheet ss = SpreadsheetApp.openById('YOUR_ID_HERE'); sheet = ss.getSheetByName('YOUR_SHEET_NAME'); // this is the entire list of PayPal variables they might include: var rowData = []; rowData.push(e.parameter.payment_type); rowData.push(e.parameter.payment_date); rowData.push(e.parameter.payment_status); rowData.push(e.parameter.pending_reason); rowData.push(e.parameter.address_status); rowData.push(e.parameter.payer_status); rowData.push(e.parameter.first_name); rowData.push(e.parameter.last_name); rowData.push(e.parameter.payer_email); rowData.push(e.parameter.payer_id); rowData.push(e.parameter.address_name); rowData.push(e.parameter.address_country); rowData.push(e.parameter.address_country_code); rowData.push(e.parameter.address_zip); rowData.push(e.parameter.address_state); rowData.push(e.parameter.address_city); rowData.push(e.parameter.address_street); rowData.push(e.parameter.business); rowData.push(e.parameter.receiver_email); rowData.push(e.parameter.receiver_id); rowData.push(e.parameter.residence_country); rowData.push(e.parameter.item_name); rowData.push(e.parameter.item_name1); rowData.push(e.parameter.item_number); rowData.push(e.parameter.item_number1); rowData.push(e.parameter.quantity); rowData.push(e.parameter.shipping); rowData.push(e.parameter.tax); rowData.push(e.parameter.mc_currency); rowData.push(e.parameter.mc_fee); rowData.push(e.parameter.mc_gross); rowData.push(e.parameter.mc_gross_1); rowData.push(e.parameter.mc_handling); rowData.push(e.parameter.mc_handling1); rowData.push(e.parameter.mc_shipping); rowData.push(e.parameter.mc_shipping1); rowData.push(e.parameter.txn_type); rowData.push(e.parameter.txn_id); rowData.push(e.parameter.notify_version); rowData.push(e.parameter.parent_txn_id); rowData.push(e.parameter.reason_code); rowData.push(e.parameter.receipt_ID); rowData.push(e.parameter.auction_buyer_id); rowData.push(e.parameter.auction_closing_date); rowData.push(e.parameter.for_auction); rowData.push(e.parameter.reason_code); rowData.push(e.parameter.receipt_id); rowData.push(e.parameter.custom); rowData.push(e.parameter.invoice); sheet.appendRow(rowData); // Enacts all changes SpreadsheetApp.flush(); }catch(e){ // Logs Error Logger.log('Caught Error:',e.message) } } - You'll note there's two sections you need to update in the code above. First is the Sheet ID, which you can find in the URL of Sheet's webpage:
The other is the Sheet name, which you changed earlier to something else besides 'Sheet1', right?https://docs.google.com/spreadsheets/d/THIS_IS_THE_SHEET_ID_COPY_THIS_LONG_CODE/edit?gid=0#gid=0 - Everything good to go? We'll need to 'Deploy' your new app to get a URL which is your new PayPal API:
- From the Deploy page, you want to choose 'Web App' from the Gear Menu, and make sure the 'Who has access' configuration is 'Anyone'.
- Click Deploy, and you'll be served with a URL of your new API (You'll note it took me 38 tries to get my code right!) Use that 'Copy' link to copy and save your precious API's URL (the one that starts with https://script.google.com/macros/s/...) We'll test it at PayPal to ensure it works next...
- Let's test with the PayPal IPN Simulator, which will send 'fake' (sandbox) orders to your new API to test everything works. You'll have to login to PayPal to use it.
- Paste your new API URL into the PayPal form, select an example transaction (I liked Cart Checkout), then scroll to the bottom to test. You can change/add any of the values on this page to ensure it's working thoroughly. Click the 'Send IPN' button at the bottom, and await the result!
- PayPal gave me the positive result, and sure enough my Spreadsheet was instantly updated with a new row from this order!
- As this was a 'Sandbox' (testing) order, I need to alter my code to use it in a 'Live' environment. Comment out the 6th line and uncomment out the 7th line in the code to switch from 'Sandbox' to 'Live' and re-Deploy your code to accept the changes. The URL won't change, but the newest version of the code will run from now on. Feel free to test the live version at PayPal as well, making sure to switch from 'Sandbox' to 'Live' on PayPal as well (it's a labeled switch in the UI of that page)
- Finally, you'll need to give PayPal your new API URL for IPN Updates (that link has instructions on where to find this setting). Now any future orders will be automatically sent to your listener, and you'll get instant copies of any PayPal order in your own Google Spreadsheet! You can develop other Apps Script code to automatically email all incoming orders, but that lesson is for another day!
- You can use Page Triggers and/or
IMPORTRANGE()functions on other spreadsheets to copy or mirror the important information you require, as I like to keep this Master Spreadsheet untouched and a permanent backup of all incoming PayPal orders.
Lucky me, I got this code working just before my next Zapier monthly renewal, and I could effectively save myself the $29.99 monthly cost going forward. I switched my account to a 'Free' account (no easy way to quit anything these days), and am happily finding other paid-for services I can replace with owned code.