CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api
user3491784
user3491784

Reputation: 23

Google Script not Appending Spreadsheet

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.

Upvotes: 0

Views: 674

Answers (4)

user3491784
user3491784

Reputation: 23

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.

Upvotes: 0

Akshin Jalilov
Akshin Jalilov

Reputation: 1716

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]);
}

Upvotes: 1

eddyparkinson
eddyparkinson

Reputation: 3700

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.

Upvotes: 0

Henrique G. Abreu
Henrique G. Abreu

Reputation: 17752

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));
  }
}

Upvotes: 0

Related Questions