Bot receives some fields as null from an update in google sheets

[gtreichel]

I have a Google form linked to a Google sheet with the AppSheet addin enabled.  My AppSheet app is linked to the first tab of the Google sheet and reads the Google sheet fully.  

I have a bot that is set to trigger based on an add or update to that google sheet.  All of this setup works and based on the monitoring of the bot, I see that the bot receives all of the columns from the sheet for both adds and updates.

Issue: there are two columns that are consistently sent to the bot as null.  The columns in the sheet indeed have text values.

I've tried to regenerate the table, I've deleted and recreated the bot, but I can't seem to figure out what is causing these two fields to be sent to the bot as null.  Any ideas?

[scott192]

For the two fields that are null, what are those field types set to in Appsheet?

[gtreichel]

[Submission for Application or Presentation] is the first field and is Text

[Relate any special highlights resulting from the project from your perspective and/or from the students' perspective.] is the second field and is LongText.

Open to any ideas you might have.

[scott192 · Accepted solution]

I'm guessing you have used the 'Form Responses 1' sheet that is auto created by the Google Form you are using?

I can't make a comment about the first field, but the Name of the second field causes some alarm.  There are some characters used in the name that could be illegal in Appsheet?

There is a possibility that if you look carefully at the column heading in the sheet and the field name in appsheet that they are different.  Not only the naming conventions, but also (from experience) people who create Google Forms (and lists in general) are fond of adding extra spaces at the end of lines.  Most scripted interactions with data usually try to clean up these spurious extra characters by trimming, but then any data comparisons can fail because the trim has caused a difference (all be it invisible to the naked eye) in what is being compared.

I'm sorry I can't be more specific than that, but one place where you can make some good changes in your workflow is to Name your questions in the Google Form using Q1, Q2, Q3, Q4 etc.  Use the Description field in the Google Form to flesh out the question.  So in your case, the first question on your Google Form would be Q1 and its Description would be 'Submission for Application or Presentation'.

Your 'Form responses 1' sheet would have simple Q1,Q2,Q3 column headings and would import nicely into Appsheet where it would be easier to troubleshoot any missing data.

[gtreichel]

scott192, your response helped steer me down a path that resolved the issue.  For the first field, i found that in the Google Sheet, it ended with a space.  I tried editing the google sheet, but that didn't seem to fix it.  I opened the Google Form and retyped the field slowly, letting it save a couple times.  I ended up with the exact same text, but that seemed to clear out what ever was there that was causing the issue.  

For the second field, it did have an apostrophe.  Instead of trying to determine specifically if that was the issue, I gave the field a shorter title and moved the full question to the description field.

In the end, both resolved the issue.  In both cases, it wasn't obvious what the issue was.  The key was to retype the title and let the Google Form to overwrite the field.  I've now learned that you need to test with a bot to see if any field comes through as null.  If so, retype that field and seek to simplify the title if it has special characters.  Thanks!