Error after switch from public to private google sheet



Hi all,
I’m “struggling” with my first voice app made with this amazing framework (and it’ a pleasure), so I’m definitely a newbie!

I’ve followed the nice tutorial from Jan “Use Google Sheets as CMS for your Voice App” to switch from a public to a private Google Sheets. I think the configuration and security part is ok, cause I can see from Google Developer Console the access attempt made by the new service account, when i use the app and there is no auth error into the console; but when Jovo retrieves the data from the private sheets it gets only “undefined” values…

Here is my config.js:

cms: {
        GoogleSheetsCMS: {
            caching: true,
            //spreadsheetId: 'xxxx',
            //access: 'public',
            spreadsheetId: 'xxxx',
            access: 'private',
            credentialsFile: './credentials/google-sheets.json',            
            sheets: [
                    name: 'cibi',
                    type: 'ObjectArray',
                    range: 'A:C',
                    position: '1'
                    name: 'categorie',
                    type: 'ObjectArray',
                    range: 'A:B',
                    position: '2'

and this is the relevant logs:

  Start request (framework) ---------------------------------------------

  Adding resources to $cms object:
  Retrieving private spreadsheet
  Spreadsheet ID: xxxx
  Sheet name: cibi
  Sheet range: A:C
  Retrieving private spreadsheet
  Spreadsheet ID: xxxx
  Sheet name: categorie
  Sheet range: A:B

  -- middleware 'setup' done (851 ms)
  -- Request JSON (jovo-framework) -----------------------------------------

The rest of code is unchanged, and i’ve cloned the public sheet to get a private one; any suggestion?

I’ve noticed only one strange behaviour, after i’ve invited the service account to collaborate a received a back into my mail a “Delivery Status Notification (Failure)” cause the domain does not exists, voiceappsheets is the project i’ve created to manage the credential stuff.

Best Regards,


Found It!
When we use the public sheets the column name is not case sensitive, while it is when using the private ones. I’ve lowercased all the column names and now it works flawlessly.



Great! Thanks for clarifying :slight_smile: