When sending a POST message to Google Sheets, I keep getting an undefined entry on the spreadsheet. The message is POST'ed successfully from my client, but it does not seem to be processed by the Google Sheets API endpoint.
How can I fix this? I was following the tutorial here to get it working.
Form Code
import { Component, OnInit } from '@angular/core';
import { FormBuilder, FormGroup, Validators, FormControl } from '@angular/forms';
import { Breakpoints, BreakpointState, BreakpointObserver } from '@angular/cdk/layout';
import { Observable } from 'rxjs';
import { environment } from 'src/environments/environment';
@Component({
templateUrl: './about.component.html'
})
export class AboutComponent implements OnInit {
isMobile: Observable<BreakpointState>;
submitted = false;
registerFormGroup: FormGroup;
constructor(
private breakpointObserver: BreakpointObserver,
private formBuilder: FormBuilder) { }
private scriptURL = 'https://script.google.com/macros/s/123/exec';
registerForm = document.forms['registerForm'];
ngOnInit() {
this.isMobile = this.breakpointObserver.observe([ Breakpoints.Handset, Breakpoints.Tablet ]);
this.registerFormGroup = new FormGroup({
name: new FormControl(),
email: new FormControl()
});
this.registerForm = this.formBuilder.group({
name: ['', Validators.required],
email: ['', [Validators.required, Validators.email]]
});
}
get f() { return this.registerForm.controls; }
onSubmit() {
this.submitted = true;
// Stop here if form is invalid
if (this.registerForm.invalid) {
return;
}
const formObject = this.registerForm.getRawValue();
const serializedForm = JSON.stringify(formObject);
if (environment.production === false) {
console.log(serializedForm);
}
fetch(this.scriptURL, { method: 'POST', body: serializedForm})
.then(response => console.log('Success!', response))
.catch(error => console.error('Error!', error.message));
}
}
Google Sheet
Google Sheet Code
var sheetName = 'Sheet1'
var scriptProp = PropertiesService.getScriptProperties()
function initialSetup () {
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
scriptProp.setProperty('key', activeSpreadsheet.getId())
}
function doPost (e) {
var lock = LockService.getScriptLock()
lock.tryLock(10000)
try {
var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
var sheet = doc.getSheetByName(sheetName)
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
var nextRow = sheet.getLastRow() + 1
var newRow = headers.map(function(header) {
return header === 'timestamp' ? new Date() : e.parameter[header]
})
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
.setMimeType(ContentService.MimeType.JSON)
}
catch (e) {
return ContentService
.createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
.setMimeType(ContentService.MimeType.JSON)
}
finally {
lock.releaseLock()
}
}