Google Sheets Integration

JSON integration has a lot of practices, one of the simplest integrations and most used integration you can make is to integrate with google sheets.

You can use this integration to: 

1. Save user attributes. 
2. Save reservations. 
3. Save orders. 
4. Use a sheet as CRM. 

How to Integrate Your Bot With JSON API and Google Sheets

Prepare your sheet 

1. Create a new or open an existing Google Spreadsheet
2. Prepare your sheet to accept the data as suggested below:
3. Properly name the columns.
4. Add a Timestamp column to be auto-filled with the date and time of each record.

Prepare your AppScript 

function doGet(e){
  return handleResponse(e);
}

//  Enter sheet name where data is to be written below
        var SHEET_NAME = "Sheet1";

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

function handleResponse(e) {
  // shortly after my original solution Google announced the LockService[1]
  // this prevents concurrent access overwritting data
  // [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
  // we want a public lock, one that locks for all invocations
  var lock = LockService.getPublicLock();
  lock.waitLock(30000);  // wait 30 seconds before conceding defeat.
  
  try {
    // next set where we write the data - you could write to multiple/alternate destinations
    var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
    //var doc = SpreadsheetApp.openById("1BzKVTSZrhh6t9cuATPFNqENlJW5DAiBLbMlo8y3zrWY");
    var sheet = doc.getSheetByName(SHEET_NAME);
    
    // we'll assume header is in row 1 but you can override with header_row in GET/POST data
    var headRow = e.parameter.header_row || 1;
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
    var nextRow = sheet.getLastRow()+1; // get next row
    var row = []; 
    // loop through the header columns
    for (i in headers){
      if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
        row.push(new Date());
      } else { // else use header name to get data
        row.push(e.parameter[headers[i]]);
      }
    }
    // more efficient to set values as [][] array than individually
    sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
    // return json success results
    return ContentService
          .createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
          .setMimeType(ContentService.MimeType.JSON);
  } catch(e){
    // if error return this
    return ContentService
          .createTextOutput(JSON.stringify({"result":"error", "error": e}))
          .setMimeType(ContentService.MimeType.JSON);
  } finally { //release lock
    lock.releaseLock();
  }
}

function setup() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  SCRIPT_PROP.setProperty("key", doc.getId());
}

1. In the navigation bar of the spreadsheet, click on “Tools”, then click on “Script Editor”. 
2. Copy and paste this code into the editor.
3. Run the “setup()” function: From the navigation bar in the script editor, click on “Run”, select “Run Function”, then select “Setup”. 
4. Publish the script: From the navigation bar in the script editor, select “Deploy as a Web App” and publish.
5. Copy the published web app URL

In Deployment Make sure:

• "Execute the app as" is me (yourself).
• “Who Has Access To The App” that anyone can access it.

Prepare your Flow

1. In the flow where you wish to set your integration, add JSON API message.
2. Select “Get Method”. 
3. Paste the deployed web app URL (AKA the deployed app script). eg: "https://script.google.com/macros/s/..."
4. Customize the parameters' names to match the column’s name in the sheet.
5. Add attributes in the URL to assign parameters. eg: "https://script.google.com/macros/s/...?first_name={{first_name}}&last_name={{last_name}}"
6. Finally test the integration!