Learn-to-send-email-via-google-script-html-no-server: Issue with updating spreadsheet

[muthulazar]

Hi,

I have created a form and placed the fields as your sample spreadsheet. i have done the step by step instruction as you specified in the link

https://github.com/dwyl/html-form-send-email-via-google-script-without-server

The mail came as you described in the tutorial.
But i cant able to get the data from form to be filled in the spreadsheet.

Kindly help me in resolving this.
Thanks in advance.

[mckennapsean]

Did the email contain all the form data, or was it blank? It is hard to tell without a working example what might be going on here. I would have to try to replicate it. Every time I follow our instructions, it works fine for me. Have you tried all of the steps more than once? Did you go so far as part 3, so past step 13? This is where the code and necessary steps for setting up this code are specified. Pay attention to steps 14 & 15, being sure that the script's permissions are set to anyone, even anonymous.

[ibsenvalath]

I had this problem. I got it fixed by reading the doc directly rather than getting it through id in record_data method like this.
var doc = SpreadsheetApp.getActiveSpreadsheet();

Also if you are using a fresh document you created , then don't forget to add a header entry for the first row like in the example document and change the name of the sheet to 'responses'.

[mckennapsean]

ahhhhh, that would likely mean that the setup() function is not getting run (step 14). didn't know that the key could just be replaced, but that seems to be valid. I will double-check, but that is simpler and removes an unnecessary, confusing step! thanks for the find!

@nelsonic - do you know if there is a reason behind storing the spreadsheet's KEY in the setup() function for just using the key later? I can't think of one, and I will test this works before pulling, but it seems like a nice fix!

https://github.com/dwyl/html-form-send-email-via-google-script-without-server/blob/master/google-apps-script.js#L36

[nelsonic]

@ibsenvalath & @mckennapsean I've _wanted_ to remove the setup() to simplify. if you can, do it!

[crashspringfield]

I'm having the same problem as @muthulazar . I tried replacing var doc like @ibsenvalath said to no avail. Emails are sending though.

[rug1]

I am having the same problem as @muthulazar and @crashspringfield. The emails are sending fine but my spreadsheet is not being updated. I followed the tutorial and I can't see what I'm missing, any suggestions appreciated 😄

[mckennapsean]

Hmmm, so with the new code, there is no setup() to run. Did you make your own copy of the spreadsheet before editing the script? Does the spreadsheet have a responses tab and appropriate headers for the form ID values? Did you save & republish a new version of the script after adding any spreadsheet-capabilities to it? I think those are the basic troubleshooting steps I know of. If none of that works for you, please let us know and I can try to reproduce.

[rug1]

I actually got it working by having no doc variable and making var sheet = SpreadsheetApp.getActiveSheet();. However, I'm a bit confused now to how it knows what spreadsheet I'm wanting to update (in this case the responses spreadsheet). These are my variables:

var sheet   = SpreadsheetApp.getActiveSheet();
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row     = [ new Date() ]; // first element in the row should always be a timestamp

[mckennapsean]

This code may default to the first or last opened tab in a worksheet (but yes, this could be a problem if you have multiple tabs/sheets in one spreadsheet). Every Google Script is tied to a single worksheet, so it really does not need the ID at all. We removed the need for a setup() function a bit ago, but if you just copied the code from the online Google Sheet, then it may have an older version which required you to run the setup() function to get this document ID. We removed this unnecessary code and step, but without it the version if the code you may have had wouldn't work, unfortunately!

This may be an example of why we need to update the spreadsheet @nelsonic - I could try to request access to the current one, or create a copy and add a few of the maintainers onto it, whatever you think would work! The Script Editor just needs to be kept up to date with the repository's code.

Thanks for sharing your solution @rug1 !