⌙ GDE , Google Apps Script , Google Sheets , V8

AppSheet/PayPal integrated payment solutions and tips on handling data in Google Sheets

Martin Hawksey

I’ve been very fortunate to have been able to attend a number of sessions about Google’s no-code application development platform, AppSheet, including co-hosting a Totally Unscripted episode with AppSheet co-founder Praveen Seshadri and various sessions with Christian Schalk. 

Something I’ve been aware of is that ‘seeing’ is one thing, ‘doing’ is another even with a ‘citizen developer’ centric platform like AppSheet. In preparation for my new role at CTS I set myself the challenge of creating a small e-commerce application with AppSheet which integrates with a payment gateway like PayPal. The video below shows what I’ve come up with:

In this post I’ll provide an overview of how PayPal was integrated into AppSheet with the help of Google Apps Script. Even if you are not interested in payment integrations this post also hopefully pulls together useful tips, best practices and code patterns for reading/writing data to Google Sheets included in the Code Highlights section.    

Payment processing options and user flow

AppSheet has various options to orchestrate other processes including the new Apps Script integration. A consideration is that while these can be called using different events I’ve not found an elegant way to stop the user from continuing to use the app while the Apps Script function runs. This isn’t just a consideration when integrating with checkout solutions like PayPal, but also a general consideration when using any other third party APIs.

This said there are still various ways you can use a payment solution with AppSheet. For example, if you need to send an invoice for payment online there are various ways you can look at doing this, one option being using data collected via AppSheet in a Google Sheet to generate invoices/payment links which can then be sent out as an email. This process could be entirely controlled using AppSheet automations (see Send an email from an automation). For ways to generating invoices/payment links in Google Sheets Amit Agarwal has published several posts on this including:

PayPal integrated checkouts

For my app I was interested in the PayPal integrated checkout which is an easy way to add PayPal payment options commonly found in ecommerce sites. This allows a quick setup of online payment options using the PayPal JavaScript SDK. For those unfamiliar with PayPal the flow is illustrated below. 

Image credit: PayPal

As part of this users land on a webpage where the Javascript SDK has been added and an order is created. The user can click on an automatically generated payment button which takes them through the payment process, before returning to your webpage.

To achieve this I’m using a Google Apps Script bound to a Google Sheet published as a Web App. The Google Sheet I’m using is the same one being used as the data source for the AppSheet app. 

Note: There are limitations to the number of simultaneous users using an Apps Script Web App, which will vary given the type of operation you are performing (in this benchmark report on Concurrent Writing to Google Sheets was successfully able to have 26 concurrent users without data loss).

PayPal Payment – source code and setup

The code used in this project is available in this GitHub repo.  To use this code in your own AppSheet app there is a little customisation required:

Code highlights

Hopefully the code inline comments explain what is going on. The sequence of calls is as follows:

doGet() – asynchronously loading data

This function renders the HTML template. Following best practices data is loaded asynchronously and to get the customer order summary a call is made to getCustomerOrder(). 

getCustomerOrder() – reading and reducing Google Sheets data

In the ordering app there are separate tables/sheets for the customer order and product details requiring some data joining. When working with entire sheets of data I’ll usually use a spread syntax and destructuring assignment pattern I picked up from Kanshi Tanaike. Splitting the sheet header and data values using this makes it easier to get the column index which means as long as column heading names aren’t changed users can move columns around without breaking the script: 

  // To build the customer order we get all the orders in the 'cart' sheet 
  // and filter for the customer_id
  const order_sheet = doc.getSheetByName(ORDERS_SN);
  const [orders_header, ...orders] = order_sheet.getDataRange().getValues();
  
  // get the indexes of orders in cart sheet columns
  const orderCustID = orders_header.indexOf('Customer ID');
  const orderProd = orders_header.indexOf('Product');
  const orderQuant = orders_header.indexOf('Quantity');
  const orderTot = orders_header.indexOf('Total');
  const orderStatus = orders_header.indexOf('Order Status');
  
  // get the order for this customer return [product, quantity, line total]
  let order_total = 0;
  const customer_order = orders.reduce((ar, row) => {
    if (row[orderCustID] == customer_id && row[orderStatus] == "IN CART") {
      ar.push({
        product: prod_lookup[row[orderProd]],
        quantity: row[orderQuant],
        total: row[orderTot].toFixed(2)
      });
      order_total += row[orderTot];
    }
    return ar;
  }, []);

As I wanted to get a running total of the customer order there is an array.reduce to get the order details for items in the customer’s cart:

// get the order for this customer return [product, quantity, line total]
  let order_total = 0;
  const customer_order = orders.reduce((ar, row) => {
    if (row[orderCustID] == customer_id && row[orderStatus] == "IN CART") {
      ar.push({
        product: prod_lookup[row[orderProd]],
        quantity: row[orderQuant],
        total: row[orderTot].toFixed(2)
      });
      order_total += row[orderTot];
    }
    return ar;
  }, []);

showOrderSummary() – PayPal payment handling

Back client side in the Payment.html showOrderSummary() handles the customer order details returned from server side from getCustomerOrder(). The code for creating the PayPal payment button and handling payment approval comes directly from the PayPal: Link onApprove documentation. In the onApprove event payment details are recorded by calling recordPayment().

recordPayment() – flattening JSON objects, concurrent writes and sheet updates

There’s quite a bit going on in the recordPayment function worth calling out. You can see from an example response payment from PayPal, that various payment details are available nested within the response object:

To make it easier to write the payment details to Google Sheets the data is flattened and column headings are used to map the location of where the data should be recorded. You can read more about Google Apps Script Patterns: Writing rows of data to Google Sheets the V8 way.

To allow concurrent writing another example from Kanshi Tanaike has been used (See Concurrent Writing to Google Spreadsheet using Form), which uses the Lock Service.

The final part of this function is to update the Orders sheet to change the customer order rows from IN CART to COMPLETE. As each item ordered is stored in a single row and the ordered items might not be on adjacent rows the function uses the Advanced Sheets Service to call the spreadsheets.values.batchUpdate method. This approach is used because it’s more efficient than calling .setValue() on individual rows and doesn’t require handling concurrent updates if using .setValues():

// Note complete items for the customer ID
  const order_sheet = doc.getSheetByName(ORDERS_SN);
  const [orders_header, ...orders] = order_sheet.getDataRange().getValues();

  // get the indexes of Orders sheet columns
  const orderCustID = orders_header.indexOf('Customer ID');
  const orderStatus = orders_header.indexOf('Order Status');
  const orderStatusCol = String.fromCharCode(64+orderStatus+1); // convert column number to letter

  // Find complete orders in the cart complete
  // based on https://stackoverflow.com/a/55719638/1027723 and https://stackoverflow.com/a/63164207/1027723
  const updateRows = orders.reduce((ar, e, i) => {
    if (e[orderCustID] == customer_id && e[orderStatus] == "IN CART") {
      ar.push({range: `'${ORDERS_SN}'!${orderStatusCol}${i + 2}`, values: [[`COMPLETE`]]});
    }
    return ar;
  }, []);

  try {
    // Batch update the Orders sheet with completed purchases
    Sheets.Spreadsheets.Values.batchUpdate({data: updateRows, valueInputOption: "USER_ENTERED"}, doc.getId());
  } catch(e) {
    status = 'error';
    error.push('Could not update orders');
  }

Again credit goes to Kanshi Tanaike for the Stack Overflow solutions for selectively updating Google Sheets data.

Making your own AppSheet ordering app

If you are interested in using this code in your own AppSheet ordering app as a starting point I had a look at the following templates for inspiration:

For this project I ended up customising the ‘Public App (with private data)’ template. This proved to be a particularly good project to build on given the clever way it uses Slices to filter the data to a ‘current user’. Having a ‘current user’ reference made it possible to set up similar slices for other data including orders and payments. This meant there could be a shared table of orders but the current user would only see their orders.

Important: AppSheet publicly accessible apps can have unlimited users for $50/mo/app. However, the app must use absolutely no confidential data. In the case of my project some modification would be required to meet this criteria.

Summary

Hopefully this post highlights how Google Apps Script can be used to enhance an AppSheet app and create integrations with third party services and APIs. More importantly it hopefully illustrates how Apps Script and use of the Sheets Advanced Service can be used to effectively manage data in Google Sheets, optimising calls for reading/writing data to reduce response times and preventing concurrent overwriting.

chevron_left
chevron_right
css.php