Google Script not Appending Spreadsheet

0
votes

I'm trying to write a little script to make my coworkers and mine lives easier. I am trying to append lines to a spreadsheet based on information entered into a custom form. The code posted below just the doPost block which should be appending the google spreadsheet. 

function doPost(form) { 
  var PN = form.PartNumber;
  var REV = form.Revision;
  var DATE = form.RevisionDate;
  var DESC = form.Description;
  var NOTE = form.PartNotes;
  var URL = form.myFile.getURL();  
  var ss = SpreadsheetApp.openById("ID HERE"); // removed ID for sake of safety (let me be paranoid)
  var sheet = ss.getSheetName('Uploads');
  sheet.appendRow([PN,REV,DATE,DESC,NOTE,URL]);
}

I am unsure why it isn't writing to the spreadsheet but it isn't throwing me any errors. If you can offer any insight as to what is wrong I would greatly appreciate it; there are many guides online but most seem to be based on deprecated functions/code/etc.

Thanks for your time.

4
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-spreadsheet-api
Two things come to mind: 1) did you check the format of all your form-variables, i.e. are they string(able)? 2) did you check, if "sheet" is properly set? You could try to "sheet.appendRow(["test", "test", "test"]); and see if even that doesn't work - rst
It does not work. In fact it doesn't even seem like the doPost block is running. - user3491784
Google Forms already do this, why do you need code? They auto add form data to a sheet. If you want to customize you can use formulas, filter etc. - eddyparkinson
I'm trying to include it myself because I want to have the ability to include file uploads. My goal is to reduce the amount of user error in our tracking systems which stems from our information entry and file upload processes being decoupled. It is very rare that someone forgets do enter the info, but when they do it causes many problems down the road (months later). - user3491784

4 Answers

1
votes

Instead of using doPost, set up a "On form submit" trigger.

You need to get the namedValues to be able to pull specific values and take the first output.

Also, it should be "getSheetByName('Uploads')" .

As pointed out in the previous answer, it is unclear what you are trying to achieve by "form.myFile.getURL();" If you want to get the form url you might as well create it as a string, as it always stays the same.

Here is a working example of your code:

function doPost(form) { 
  var formResponses = form.namedValues;
  var PN = formResponses.PartNumber[0];
  var REV = formResponses.Revision[0];
  var DATE = formResponses.RevisionDate[0];
  var DESC = formResponses.Description[0];
  var NOTE = formResponses.PartNotes[0];

  //var URL = form.myFile.getURL();   //Not sure what you are tyring to get here as form URL will always be the same.
  var URL = "Your form's url";    //You can put the form url in here so it will be pushed in to every row.

  var ss = SpreadsheetApp.openById("ID HERE"); // removed ID for sake of safety (let me be paranoid)
  var sheet = ss.getSheetByName('Uploads');
  sheet.appendRow([PN,REV,DATE,DESC,NOTE,URL]);
}
0
votes

The form fields are nested in a "parameter" property in the doPost parameter.

So, you should access them using: 

function doPost(form) { 
  var actualForm = form.parameter;
  var PN = actualForm.PartNumber;
  //etc

To double check all parameters your receiving and their names, you could append to your sheet everything stringfied, like this:

sheet.appendRow([JSON.stringify(form)]);

--edit

This form.myFile.getURL() also looks odd. I guess another good debugging trick you could do is to wrap everything in a try-catch and email yourself any errors you get. For example:

function doPost(form) { 
  try { 
    //all your code
  } catch(err) {
     MailApp.sendMail('yourself@etc', 'doPost error', err+'\n\n'+JSON.stringify(form));
  }
}
0
votes

On form submit

onFormSubmit works. "doPost" looks wrong.

Simple example: 

function Initialize() {

  var triggers = ScriptApp.getProjectTriggers();

  for(var i in triggers) {
    ScriptApp.deleteTrigger(triggers[i]);
  }

  ScriptApp.newTrigger("SendGoogleForm")
  .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
  .onFormSubmit()
  .create();

}

function SendGoogleForm(e) 
{  
  try 
  {

Full example - Scroll down to the code http://www.labnol.org/internet/google-docs-email-form/20884/ (Note: example sends email)

Trigger docs: https://developers.google.com/apps-script/guides/triggers/events

Notes: I think the problem is doPost, Does it work with google Forms? Never seen it used with google forms.

0
votes

First and foremost, thank you everyone who has responded with information thus far. None of the solutions posted here worked for my particular implementation (my implementation is probably to blame, it is very crude), but they definitely set me down the path to a working version of my form which we now lightly use. I have posted some of the code below:

function sheetFill(form, link) { 

try { 

var formResponses = form.namedValues;
var toForm = [0,0,0,0,0,0,0];

for (i=0;i < form.PartNumber.length;i++){
    toForm[0] = toForm[0]+form.PartNumber[i];
}

... (several for loops later)

var d = new Date();
var ss = SpreadsheetApp.openById("IDHERE");
var sheet = ss.getCurrentSheet;
ss.appendRow([toForm[0], toForm[1], toForm[2], toForm[3], toForm[4], toForm[5], toForm[6], link, d]);

} catch(err) {
 MailApp.sendEmail('EMAIL', 'doPost error', err+'\n\n'+JSON.stringify(form));
 }
}

It is not very versatile or robust and isn't elegant, but it is a starting point.