Error when submitting form to spread sheet using app script

Question

Ive created this script which makes a form to post a blurb, i want it to be submitted to a spreadsheet but i keep getting this error Exception: incorrect range width, was 3 but should be 5 no matter what i change the number of rows in the getRange the numbers in this error are always the same. Is there some kind of way to update the code that I dont know about? I deploy the code every time i change it.

  function doGet() {
  var app = UiApp.createApplication().setTitle('Form for news update');

  //panel for form
  var panel = app.createVerticalPanel().setId('panel');

  //elements for the form
  var postTitle = app.createLabel('Title');
  var title = app.createTextBox().setId('title');
  var postLabel = app.createLabel('new post:');
  var post = app.createTextArea().setId('post');
  var btn = app.createButton('Submit');

  //handler to execute posting by click the button

  var handler = app.createServerClickHandler('Submit');
  handler.addCallbackElement(panel);
  //add this handler to the button
  btn.addClickHandler(handler);

  //add the elements to the panel
  panel.add(postTitle)
  .add(title)
  .add(postLabel)
  .add(post)
  .add(btn);

  //add the panel to the app
  app.add(panel);

  return app;
}
function Submit(e){
//get the app and send it to the spreadsheet
var app = UiApp.getActiveApplication();

try{
   //get the post
  var postTitle = e.parameter.postTitle;
  var title = e.parameter.title;
  var post = e.parameter.post;

  //put the info into a spreadsheet
  var ss = SpreadsheetApp.openById('KEY IN HERE REMOVED FOR PRIVACY');
  var sheet = ss.getSheets()[0];
  sheet.getRange(sheet.getLastRow()+1, 1).setValues([[ title, post]]);
}catch(e){
  app.add(app.createLabel('Error occured:'+e));
 return app;
}
}

score 6 · Answer 1 · answered May 21 '14 at 20:13

This error will also happen if you have a 2 dimensional array that isn't set up 100% quadrilaterally and it doesn't match up 100% with the selected range.

for example, if you have an array:

[
[a,b,c,d,e,f,g],
[a,b,c,d,e,f,g],
[a,b,c,d,e,f]
]

it will give you an error saying Exception: incorrect range width, was 7 but should be 6

the solution is, of course, to fill in the excess cells with null values:

[
[a,b,c,d,e,f,g],
[a,b,c,d,e,f,g],
[a,b,c,d,e,f,''],
]

basically, make sure none of them are longer than any other array.

score 4 · Accepted Answer · answered Aug 29 '13 at 19:34

You've selected a range that has a start position (row,column) but no specified height (rows) or width (columns). It will default to 1 x 1. The setValues method is then trying to apply a two-dimensional array that is a different set of dimensions, 1 row x 2 columns in this example:

sheet.getRange(sheet.getLastRow()+1, 1).setValues([[ title, post]]);
               --------------------  -            ----------------
                 |                   +- column         |
                 +- row                                +- 1 row, 2 columns

When the exception reports width, equate that with columns, not rows.

You should be using .getRange(row, column, numRows, numColumns), as in:

sheet.getRange(sheet.getLastRow()+1, 1, 1, 2).setValues([[ title, post]]);

To improve maintenance, try to avoid the magic numbers.

var outData = [[ postTitle, title, post ]];
sheet.getRange(sheet.getLastRow()+1, 1, outData.length, outData[0].length)
     .setValues(outData);

This way, if you change the data that you want to store to your sheet, you don't need to maintain the line that writes it out. Instead, it will calculate the right dimensions based on the data dimensions.

Last word: Get rid of that try..catch block, you don't need it, and it will lead to mistakes like accidentally including your return app inside the catch block, possibly causing your application to fail silently.

Thank you very much for your feedback, super informative! Are there any good reads you can suggest? what should i be using instead of try..catch? Sorry i only started with app scripts yesterday — cdm89, Aug 29 '13 at 19:41
It helps to learn Javascript first, without web techs like jQuery. Online learning from Codeacademy, for instance. The various tutorials are helpful. I've learned more by answering questions here than I did using GAS on my own, so I'd recommend trying your hand at that, even just as exercises. WRT `try..catch`, see my notes in [this previous answer](http://stackoverflow.com/a/17181046/1677912). Use it only for errors that you cannot anticipate or handle other ways. — Mogsdad, Aug 29 '13 at 20:50

Error when submitting form to spread sheet using app script

2 Answers2