Google Sheets Response Using KeyObject

cms
#1

I’m having some trouble getting the proper response from Google Sheets using the KeyObject sheet type.

I set up the sheet in my config.js following the configuration tutorial which looks like this:

cms: {
GoogleSheetsCMS: {
    spreadsheetId: '<sheetID>',
    access: 'public',
    sheets: [
        {
            name: 'responses',
            type: 'KeyObject',
            range: 'A:D',
            position: 1,
        },
    ]
}

I believe the problem occurs when I attempt to make a call to the sheet using a method. I define a method and use this.tell to reference the sheet. I’m not sure about the proper syntax for the response but I’ve tried using a couple of different techniques with no success. For example when I use the following notation I get an error message that says “Speech must not be undefined”:

method() {

this.tell(this.$cms.responses.key.secondKey);

}

I have also tried using an El expression like this:

method() {

this.tell(`${this.$cms.responses.key.secondKey}`);

}

However this returns undefined. I’ve doubled checked and the sheet name and keys are properly formatted; even the case sensitivity in my function appear the same way they do in the Google Sheet.

Is there any insight on why my method fails to return the desired text from the database? Perhaps I should format the syntax differently?

Any help would be appreciated!

#2

Hi,

A couple things that might be helpful for debugging:

  • What happens if you log the whole sheet, this.$cms.responses?
  • What happens if you log it outside this method, e.g. right in the LAUNCH handler?
  • How are you calling this method? Methods usually come with a jovo parameter (method(jovo)) and need to be called with method(this) for the context to be passed. Inside the method, you would then use jovo instead of this, e.g. jovo.tell(jovo.$cms.responses.key.secondKey)
#3

Thanks for the pointers Jan,

After playing around I found the issue was related to case sensitivity. In the Google Sheet, my second key, “Object1,” begins with an uppercase letter. I used this when referring to the nested object. However, it turns out the key should remain lowercase in the Jovo call, regardless of the case used in the sheet.

I should have done this:

this.tell(this.$cms.responses.key.object1);

Instead of this:

this.tell(this.$cms.responses.key.Object1);

Thanks again Jan. This ticket is resolved.

#4

Ah, got. it. This one might have worked too I think:

this.tell(this.$cms.responses.key['Object1']);