Skip to content

Instantly share code, notes, and snippets.

@dtturcotte
Last active December 4, 2023 21:26
Show Gist options
  • Save dtturcotte/e427078e0f65e8634cd1051aa1e044bc to your computer and use it in GitHub Desktop.
Save dtturcotte/e427078e0f65e8634cd1051aa1e044bc to your computer and use it in GitHub Desktop.
onEditWebhook Google Sheets App Script: send Google Sheet's updated row data to Node.js endpoint
const ngrokURL = 'NGROK_URL_HERE'
// 'kinesis': sends data to be stored in kinesis, which emits a message
// 'db': sends data to be stored in PostgreSQL, whose trigger emits a message
const endpoint = 'kinesis'
const url = `${ngrokURL}/api/polls/${endpoint}`
/*
On row edit (either by human entry or by timed trigger), get data and send to endpoint
*/
function atEdit(editedData, isProgrammatic) {
let rowData, range, sheet, updatedRow, updatedColumn, newValue
if (!isProgrammatic) {
range = editedData.range
sheet = range.getSheet()
updatedRow = range.getRow()
updatedColumn = range.getColumn()
newValue = range.getValue()
rowData = sheet.getRange(updatedRow, 1, 1, sheet.getLastColumn()).getValues()
console.log('Human-Entered rowData: ', rowData)
} else {
rowData = editedData
console.log('Programmatic rowData: ', rowData)
}
// rowData: [ [ 20, 'Christie', 2 ] ]
const dataForKinesis = rowData.map((arrRow) => {
return {
id: arrRow[0],
name: arrRow[1],
value: arrRow[2]
}
})
const options = {
method: 'post',
contentType: 'application/json',
payload: JSON.stringify(dataForKinesis),
headers: {
'ngrok-skip-browser-warning': 'true'
}
}
try {
const response = UrlFetchApp.fetch(url, options)
console.log('Fetch Success: ', response.getContentText())
} catch (error) {
console.log('Fetch Error: ', error)
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment