Sample Script to store your leads from lead form extension to Google Sheet

leads_to_sheet.js

/*
Sample script to be used as a webhook listener for Google Ads Leads Campaigns

## Setup:
1) Create a new script project at https://script.google.com/ and paste the script code into the editor window
2) Create a new scpreadsheet and paste the URL in lines #15 and #65 
3) in the menu above select Init function and make a first run to initialize authorization process (https://nimb.ws/WnvXJz)
4) publish the script as a web app through the `Publish > Deply as a web app` menu. Make sure set
   the "Who has access to the app:" option to "Anyone, even anonymous"
5) use generated link as a webhook URL in Google Ads form settings
6) new sheet 'formatted leads' will be created within your spreadsheet. your leads will appear there.

*/

function doPost(e) {
  var SHEET_URL = "https://docs.google.com/spreadsheets/d/1vyc1bAKvVBd0wqXqr_rIzFKrMdME-MIhxeIKALt4H0s/edit"
  var LIST = ["time", "lead_id","form_id","api_version","google_key"]
  //e.postData.contents
  var spreadsheet = SpreadsheetApp.openByUrl(SHEET_URL)
  var leadsSheet = spreadsheet.getSheetByName('formatted leads')
  if (!leadsSheet) {
    leadsSheet = spreadsheet.insertSheet('formatted leads')
  }
  var rawSheet = spreadsheet.getSheetByName('raw context')
  if (!rawSheet) {
    rawSheet = spreadsheet.insertSheet('raw context')
  }
  var rawData = [Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm:ss')], data = null
  var header = LIST.map(function (item) {return item})
  var headerRange = leadsSheet.getRange(1, 1, 1, header.length)
  var updatedHeader = false
  try {
    data = JSON.parse(e.postData.contents)
    if (leadsSheet.getLastRow() > 0) {
      var headerRange = leadsSheet.getRange(1, 1, 1, leadsSheet.getLastColumn())
      var header = headerRange.getValues()[0]
    }
    data['time'] = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm:ss')
    var row = LIST.map(function (listKey) {return data[listKey]})
    var colData = data['user_column_data']
    for (var i = 0; i < colData.length; i++) {
      var item = colData[i]
      if (header.indexOf(item.column_name) == -1) {
        header.push(item.column_name)
        updatedHeader = true
      }
      row[header.indexOf(item.column_name)] = item.string_value
    }
    if (updatedHeader) {
      headerRange = leadsSheet.getRange(1, 1, 1, header.length)
      headerRange.setValues([header])
    }
    rawData.push('processed')
    leadsSheet.appendRow(row)
  } catch (er) {
    rawData.push('failed')
    rawData.push(er)
  }
  rawData.push(e.postData.contents)
  rawSheet.appendRow(rawData)
  return ContentService.createTextOutput("ok");

}

function init() {
  var SHEET_URL = "https://docs.google.com/spreadsheets/d/1vyc1bAKvVBd0wqXqr_rIzFKrMdME-MIhxeIKALt4H0s/edit"
  var spreadsheet = SpreadsheetApp.openByUrl(SHEET_URL)
  var leadsSheet = spreadsheet.getSheetByName('formatted leads')
  if (!leadsSheet) {
    leadsSheet = spreadsheet.insertSheet('formatted leads')
  }
  Logger.log('last row is >%s<', leadsSheet.getLastRow())
}
/*

sample request

{
"lead_id": "lead_id1",
"form_id" : "form_id1",
"user_column_data":[ {"column_name":"Full Name","string_value":"John Doe"},
                     {"column_name":"User Phone", "string_value":"12345678"},
                     {"column_name":"User Email", "string_value":"[email protected]"}],
"api_version":"1.0",
"google_key" : "secret"
}

*/

This is great! One question I have is with a multiple choice selection on the form. It's pulling in the answer, but not adding the question as a column header, but it's also putting the question a column over each answer that comes through. So it'll go in the right row, but gets pushed a column to the right. Any way to resolve this?

Thanks a lot.

Thank a lot.
I just have a question. How do you generate a Key for this webhook to use in Ads?