0

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

enter image description here

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()
  }
}
methuselah
  • 12,766
  • 47
  • 165
  • 315
  • And the exact and complete error message is? – JB Nizet Oct 28 '18 at 09:36
  • There is no error message. The message is posted successfully. `{"name":"tom","email":"tom@hotmail.co.uk"}` `Success! ` but it seems on the Google end, it is processed incorrectly somehow... maybe as it does not expect that format? – methuselah Oct 28 '18 at 09:38
  • 1
    Ah, so please rephrase your question, since it literally says: "I keep getting an undefined error message.". – JB Nizet Oct 28 '18 at 09:39
  • 1
    Sure. Just updated the question with more details. – methuselah Oct 28 '18 at 09:40
  • Although I'm not sure whether this is the direct solution of your issue, for example, when ``doPost(e)`` is called, can you confirm the object ``e`` using Stackdriver? Because in your request, I thought that the sent value might be at ``e.postData.contents``. – Tanaike Oct 28 '18 at 22:11
  • @Tanaike how do I confirm the object e using Stackdriver? – methuselah Oct 28 '18 at 22:15
  • 1
    Is this document useful for you? https://developers.google.com/apps-script/guides/logging#using_stackdriver_logging And is this thread useful for your situation? https://stackoverflow.com/questions/53008348/how-to-take-data-in-google-sheet-script-via-post-request-in-json-format/53018010#53018010 – Tanaike Oct 28 '18 at 22:18

0 Answers0