error submit form google apps script bad value

Question

I have an application that submits a form. I'm sure that my code is fine, but when I execute the application I run into this error: error encountered script bad value.

Here are my two functions: doGet writes the form and onSumbit submits the form into the spreadsheet

function doGet()
{
var Myapp= UiApp.createApplication();
var panel=Myapp.createAbsolutePanel().setStyleAttributes({'position':'fixed',
                                                        'top':'15%',
                                                        'left':'2%',
                                                        'height':'300',
                                                        'width':'500',});
var NameLabel=Myapp.createLabel().setText('Name');
var NameTb=Myapp.createTextBox().setId('Nameid').setName('Name');
var LastNameLabel=Myapp.createLabel().setText('Last name');
var LastNameTb=Myapp.createTextBox().setId('Lastnameid').setName('LastName');
var emailLABEL=Myapp.createLabel().setText('Email');
var emailtb=Myapp.createTextBox().setName('Email');
var serverHandler=Myapp.createServerHandler('onSubmitForm');
var ButtonSubmit=Myapp.createButton('submit',serverHandler);
   serverHandler.addCallbackElement(NameTb).addCallbackElement(LastNameTb).addCallbackElement(emailtb);
var panelButton =Myapp.createFlowPanel().setStyleAttributes({'position': 'fixed', 
                                                           'border' : '2px solid black',
                                                           'top' : '80%',
                                                           'left' : '70%',
                                                           'width' : '55', 
                                                           'height':'30'}); 

Myapp.add(panel);
Myapp.add(panelButton);
panel.add(NameLabel);
panel.add(NameTb);
panel.add(LastNameLabel);
panel.add(LastNameTb);
panel.add(emailLABEL);
panel.add(emailtb);
panelButton.add(ButtonSubmit);

return Myapp;
}
function onSubmitForm(e)
{
var Myapp = UiApp.getActiveApplication();

var sheet     =SpreadsheetApp.openById('0Ar2BlsmmO4nZdHN6WXp2akx1QWUzX3lKX19rYzUyRVE&usp').getActiveSheet();

var numRows = sheet.getLastRow(); 

sheet.getRange("A"+lastRow).setValue(e.parameter.Name);
sheet.getRange("B"+lastRow).setValue(e.parameter.LastName);
sheet.getRange("C"+lastRow).setValue(e.parameter.Email);

return Myapp;
}

Thank you for your help.

here is my new function of the submit , but i still i have the same error, ps : i have an other function doGet , where i write my form function onSubmitForm(e) { var Myapp = UiApp.getActiveApplication(); var sheet =SpreadsheetApp.openById('0Ar2BlsmmO4nZdHN6WXp2akx1QWUzX3lKX19rYzUyRVE&usp').getActiveSheet(); var lastRow = sheet.getLastRow(); sheet.getRange("A"+lastRow).setValue(e.parameter.Name); sheet.getRange("B"+lastRow).setValue(e.parameter.LastName); sheet.getRange("C"+lastRow).setValue(e.parameter.Email); return Myapp; } — Marooweb
please edit your post with the right code rather than commenting... not very readable. — Serge insas
no problem, it was just a tip to improve your chances of getting appropriate help. That said, I'm afraid the code you show is not an exact copy of your real code... where is e defined ? could you show the doGet ? and why is it called "onSubmitForm" ??? normally it should be doGet / doPost... I really don't understand how you make it work. — Serge insas
i will put the two functions may be it ill be more clear for everybody — Marooweb

Serge insas Serge insas · Accepted Answer · 2013-11-14T18:47:54

The error seems to come from the spreadSheetApp call, does this Id exist and have you edit access to it ?

Another point : don't use getActiveSheet() in that context as there is no spreadsheet open so no active sheet .... use getSheetByName() instead.

Last point : you are using a variable lastRow that does not exist.... you defined numRows , well use it ! (but add +1 otherwhise you will overwrite the last row each time you submit.

Here is the replacement code :(I used another ID for my test, revise the value and dubble check it)

var sheet = SpreadsheetApp.openById('0AnqSFd3iikE3dGFpMm1IMHo5OGZteUZSTEV6NnRUT2c').getSheetByName('Sheet1');

var numRows = sheet.getLastRow()+1; 

sheet.getRange("A"+numRows).setValue(e.parameter.Name);
sheet.getRange("B"+numRows).setValue(e.parameter.LastName);
sheet.getRange("C"+numRows).setValue(e.parameter.Email);

And finally, why didn't you use a more usual doGet / doPost structure, it would simplify the script as it doesn't need callbackElement and handler... ? Also, think about adding something in the handler function to tell the user that his form has been submitted otherwise they will click it indefinitely ;-)

error submit form google apps script bad value

1 Answers