Fixing error: cannot read property “values” from undefined

Typeerror: cannot read property “values” from undefined is one of the most persistent and frequent errors you can get when doing any type of scripting in Google Apps Script. One of the most frequent questions I get on this blog, so I decided to dedicate a post to helping people troubleshoot this pesky error.

First, we’ll talk about what this error means, then we’ll look at the two most common scenarios in which you might get this error, and finally, we’ll talk about what we can do to fix it.

What does typeerror: cannot read property “values” from undefined really mean?

To understand this, you first need to understand what it means to be undefined in JavaScript. Let’s take a look at a simple example using variables:

// This line is a variable declaration
// we create it, but do not assign it a value 
// so it is undefined

var a; 

// In this line we declare and assign a variable
// this is not undefined 

var b = 6;

Something is undefined in JavaScript when it is an object that is expected to hold a reference to a value, but in fact references nothing. There is a really good explanation at W3 schools on undefined in JS that is worth a look if you want some better examples.

In the context of our error message, it says we are trying to read a set of properties called “values” from something that is undefined, likely with a line that looks something like this if you are using the resources on this site:

var email = e.values[4];

When the script runs, it gets to this line or a line like it and and says “Ok, I see you want to create a variable called email, and you want this variable to equal the 5th value of something called e (our form submission event), but wait a minute, e is undefined! There is nothing there! Error!”

The event parameter (e) exists because we injected it into the script, but it doesn’t equal anything, so in turn we can’t access any of its properties. In the case of most Google Apps Scripts projects, this occurs because there is not event (e) passed in when you run the script in the debugger.

What Can I Do to Fix This in Google Apps Script? 

For most people, this is a quick fix: Submit a test form. DO NOT TRY TO RUN IN DEBUGGER. 

Remember the script needs an event, meaning we have to give it one. Since we’ve set a trigger tied to a form submission, that is the type of event that it expects.

 

 

 

42 thoughts on “Fixing error: cannot read property “values” from undefined”

  1. Arthur says:

    Hello,
    I have a problem with my forms I have this error but I send a test form but I have the same problem. Can you help me ?

    1. admin says:

      Can you post your code for me to look at?

      1. Curt Painter says:

        I am curious if there was a solution. I am having the same problem as Arthur. I have pasted my code below:

        function lunchDetention(e)
        {
        var studentName = e.values[4];
        var detentionDate = e.values[3];

        startDate = new Date(detentionDate);
        endDate = new Date( detentionDate);

        var startDate = new Date( );

        var myCal = CalendarApp.getDefaultCalendar( );

        myCal.createAllDayEvent(studentName,detentionDate);

        }

        1. BrownBearWhatDoYouSee says:

          First, make sure you are testing this by submitting a test form. Can you confirm you are doing that?

          This issue usually indicates an issue with a typo in the function, or a mis-referenced spreadsheet cell. Are you sure that the values (e.values[4] and e.values[3]) are correct using zero indexing, meaning the first column of the spreadsheet (usually the timestamp) is e.values[0]?

          If it’s not that, there are other things you can try as well.

  2. Lounhass says:

    Hello

    same goes for me.

    I copied and pasted your script but I keep getting the error message even though I added the correct values

    Please help me!

  3. ITGuide says:

    Hi Jeff,

    I’m liking this blog on Google App Scripts!

    I suspect you may have the answer to this already but 1 way around this that I found handy is to use javascript “try” and “catch” block. This worked for me.

    https://stackoverflow.com/questions/17180141/how-do-you-pass-back-a-custom-error-message-from-google-apps-scripts

    All you have to do to ignore the error is to leave your error handling empty.

    try{
    // function code goes here;
    }catch(e){
    // igore error
    // or if you prefer display an error message here
    }

    1. BrownBearWhatDoYouSee says:

      Thanks for the link! You’re right that try/catch is a great paradigm for error handling and has tons of applications for making robust Google Apps. Thanks for reading!

  4. nicol biden says:

    This error occurs in Chrome Browser when you read a property or call a method on an undefined object . Uncaught TypeError: Cannot read property of undefined error is probably easiest to understand from the perspective of undefined, since undefined is not considered an object type at all (but its own undefined type instead), and properties can only belong to objects within JavaScript. There are a few variations of this error depending on the property you are trying to access. Sometimes instead of undefined it will say null.

    http://net-informations.com/js/iq/unerror.htm

    1. BrownBearWhatDoYouSee says:

      Thanks for the link to the helpful resource. JE

  5. OH Please help… I have tried every tip tool and resource I can find to fix the failed to run error.
    I had the undefined error in debugger. The try/catch fixed that just to check the code.
    But I still get a failed trigger and the template isn’t created when I submit test form after test form after test form.
    //here is my code:
    function CreateSOI(e) {
    //e.values is an array of form values
    var timestamp = e.values[0];
    var lastname = e.values[1];
    var firstname = e.values[2];
    var grade = e.values[3];
    var age = e.values[4];
    var dateadmin = e.values[5];
    var cfu = e.values[6];
    var cfc = e.values[7];
    var mfu = e.values[8];
    var efu = e.values[9];
    var efc = e.values[10];
    var cfs = e.values[11];
    var cft = e.values[12];
    var nfu = e.values[13];
    var dfu = e.values[14];
    var css = e.values[15];
    var msuv = e.values[16];
    var mssv = e.values[17];
    var msua = e.values[18];
    var mssa = e.values[19];
    var msi = e.values[20];
    var esc = e.values[21];
    var ess = e.values[22];
    var nss = e.values[23];
    var nst = e.values[24];
    var nsi = e.values[25];
    var dsr = e.values[26];
    var csr = e.values[27];
    var cmu = e.values[28];
    var cmum = e.values[29];
    var cmr = e.values[30];
    var cms = e.values[31];
    var dmu = e.values[32];
    var mmi = e.values[33];
    var figural = e.values[34];
    var symbolic = e.values[35];
    var semantic = e.values[36];
    var creativity = e.values[37];
    var memory = e.values[38];
    var evaluation = e.values[39];
    var formversion = e.values[40];

    //file is the template file, and you get it by ID
    var file = DocumentApp.getFileById(‘1DcJvrFF_8sZQgL3hc1J4GdJ9spTfDNGo6QG642HGgq4′);

    //We can make a copy of the template, name it, and optionally tell it what folder to live in
    //file.makeCopy will return a Google Drive file object
    var folder = DriveApp.getFolderById(’17tih1cwh7gZI3yVV6g8UFPbb5SWxIUUH’)
    var copy = file.makeCopy(lastname + ‘.’ + firstname + ‘SOIclusterFormCR’, folder);

    //Once we’ve got the new file created, we need to open it as a document by using its ID
    var doc = DocumentApp.openById(copy.getId());

    //Since everything we need to change is in the body, we need to get that
    var body = doc.getBody();

    //Then we call all of our replaceText methods
    body.replaceText(‘<>’, timestamp);
    body.replaceText(‘<>’, lastname);
    body.replaceText(‘<>’, firstname);
    body.replaceText(‘<>’, grade);
    body.replaceText(‘<>’, age);
    body.replaceText(‘<>’, dateadmin);
    body.replaceText(‘<>’, cfu);
    body.replaceText(‘<>’, cfc);
    body.replaceText(‘<>’, mfu);
    body.replaceText(‘<>’, efu);
    body.replaceText(‘<>’, efc);
    body.replaceText(‘<>’, cfs);
    body.replaceText(‘<>’, cft);
    body.replaceText(‘<>’, nfu);
    body.replaceText(‘<>’, dfu);
    body.replaceText(‘<>’, css);
    body.replaceText(‘<>’, msuv);
    body.replaceText(‘<>’, mssv);
    body.replaceText(‘<>’, msua);
    body.replaceText(‘<>’, mssa);
    body.replaceText(‘<>’, msi);
    body.replaceText(‘<>’, esc);
    body.replaceText(‘<>’, ess);
    body.replaceText(‘<>’, nss);
    body.replaceText(‘<>’, nst);
    body.replaceText(‘<>’, nsi);
    body.replaceText(‘<>’, dsr);
    body.replaceText(‘<>’, csr);
    body.replaceText(‘<>’, cmu);
    body.replaceText(‘<>’, cmum);
    body.replaceText(‘<>’, cmr);
    body.replaceText(‘<>’, cms);
    body.replaceText(‘<>’, dmu);
    body.replaceText(‘<>’, mmi);
    body.replaceText(‘<>’, figural);
    body.replaceText(‘<>’, symbolic);
    body.replaceText(‘<>’, semantic);
    body.replaceText(‘<>’, creativity);
    body.replaceText(‘<>’, memory);
    body.replaceText(‘<>’, evaluation);

    //Lastly we save and close the document to persist our changes
    doc.saveAndClose();

    }

  6. I GOT IT …. OMG I GOT IT!!! Super Proud of myself!

    The error issue was that I had previously tried to run autocrat add on to achieve same end result and while I had deleted its components it was still causing issues with my new script. The autocrat not being reliable is the whole reason I chose to find and write the script instead.

    THANK YOU for this!

    I am now taking it a step further to generate 4 documents perform submit all with the same data.

    1. BrownBearWhatDoYouSee says:

      Awesome! I’m glad you got everything figured out. Thanks for reading, and be sure to post back if you hit anymore road blocks.

      JE

  7. Hazel says:

    Hi! Thanks for much for posting! I am filtering my form responses into various tabs based on Col2. I’d like for all responses from each tab to populate a doc in a folder for that tab.

    1. Should I be filtering in the responses sheet like I currently am or would it be better to write a filter into the function? If so how?

    2. If filtering first is best, how do I define which tab the values come from?

    1. BrownBearWhatDoYouSee says:

      I’m not sure what you mean by filtering here. Can you post an example of some code?

      Thanks for reading,
      Jeff

  8. Allison says:

    I’m using your code:
    function myFunction(e) {
    //e.values is an array of form values
    var timestamp = e.values[0];

    The trigger I’m using is On Edit because I want a doc to populate when I paste data into the spreadsheet rather than when a form is filled out. When I complete the event (e) by pasting new data into the spreadsheet, I’m notified that e is undefined: “TypeError: Cannot read property “0” from undefined. (line 3, file “Code”)”

    Can you help?

    1. BrownBearWhatDoYouSee says:

      Hi Allison,

      Thanks for reaching out. I’ve never really used the onEdit trigger, so the issue is likely about the shape of the event parameter that gets passed into myFunction, the variable we reference as e throughout the rest of the script. It seems like ‘e’ is likely defined, but since it is not a form submission, it does not have a values property, so e.values is undefined. My recommendation would be to log out the value of e using Logger.log(e) to see what is available to you on the edit functionality event. The logs are in the View > Logs menu item in the apps script editor.

      After you figure that out, you’ll likely have to change the way you are accessing the data. Feel free to post back here if you have any questions after logging the value of e.

      Thanks for reading,
      Jeff

  9. farah says:

    Thank you so much for your post. I am an absolute novice with a medical background but I tried my hand. Wanted a new letter to be created using a template named after the patient name and triggered whenever a new form was submitted. But it doesnt seem to do it. I have tried submitted a test form but error.

    please help. 🙁

    function myFunction(e) {
    var nameofPatient = e.values[1];
    var icNumber = e.values[2];
    var dateSeenInClinic = e.values[3];
    var dateOfSurgery = e.values [4];
    var surgeryPerformed = e.values [5];
    var costOfSurgery = e.values [6];
    var referralFee = e.values [7];
    var referredFrom = e.values [8];
    var file = DriveApp.getFileById(‘ReferralReply’);
    var folder = DriveApp.getFolderById(‘PremierClinic’);
    var copy = file.makeCopy(nameofPatient, folder);
    var doc = DocumentApp.openById(copy.getId());
    var body = doc.getBody();

    body.replaceText(‘{{Name of Patient}}’, nameofPatient);
    body.replaceText(‘{{Ic Number}}’, icnumber);
    body.replaceText(‘{{Date Seen In Clinic}}’, dateseeninClinic);
    body.replaceText(‘{{Date of Surgery}}’,dateofSurgery);
    body.replaceText(‘{{Surgery Performed}}’,surgeryperformed);
    body.replaceText(‘{{Cost of Surgery}}’,costofsurgery);
    body.replaceText(‘{{Referral Fee}}’,referralfee);
    body.replaceText(‘{{Referred From}}’,referredfrom);

    doc.saveAndClose();

    }

    1. BrownBearWhatDoYouSee says:

      Hi Farah,

      The first thing I see is that you are calling both DriveApp.getFileById and DriveApp.getFolderById using the names of the files/folders instead of the id. The ID is in the URL browser bar and is a long string of numbers and characters. There should also be methods like DriveApp.getFolderById available, but I like to get things by their unique id. I’d suggest starting there, which should give you some more additional error messages.

      JE

  10. Daniel says:

    Hi guys, my script as shown below worked for awhile but suddenly stopped with the TypeError cannot read property ‘values’ of undefined (line 2, file “code”). I don’t really understand the suggestions about try/catch and stuff.


    function myFunction(e){
    var FirstName = e.values[1];
    var UserEmail = e.values[3];
    var Subject = “Your MARKON e-Magazine has arrived!✨”;
    var response = UrlFetchApp.fetch(“http://www.markonmag.com/”);
    Logger.log(response.getContentText());
    var Message = “Hi ” + FirstName + “!” + “\n\nThank you for making the smart decision of downloading MARKON e-magazine.✨ \nWe hope you’d take away some valuable insights! \n\nStay Connected, Stay Curious! \n\nWith Love 💖, \nDaniel & Nicole\nCo-Creators of MARKON”;
    var filename = ‘MARKON e-Magazine.pdf’;
    var file = DriveApp.getFilesByName(filename);

    MailApp.sendEmail(UserEmail, Subject, Message, {name: ‘MARKON’, attachments: [file.next().getAs(MimeType.PDF)]});

    }

    1. BrownBearWhatDoYouSee says:

      Hi Daniel,
      Are there any other add-ons that you might have installed that could conflict with this? Absent that, I’d recommend checking out this post on debugging and throwing a few log statements in there to inspect the value of ewhen you submit a form: https://jeffreyeverhart.com/2020/01/24/debugging-in-google-apps-script/

      Please post back if it continues to give you trouble,
      Jeff

  11. Mike says:

    I get the line 2 error with this code. I am trying to auto-populate a google doc on submission of a google form. I don’t want all the fields of the form, just the one. Thanks!

    function autoFillGoogleDocFromForm(e) {
    var timestamp = e.values[0];
    var name = e.values[1];
    var phoneNumber = e.values[2];
    var emailAddress = e.values [3];
    var whatIsYourQuestion = e.values[4];

    var templateFile = DriveApp.getFileById(“1FjzFvfHQVgOlA180tJjsM0PPZh_srx5EC4X2_rwx5eQ”);
    var templateResponseFolder = DriveApp.getFolderById(“1Il3z-2Ce6iWkdW10j0wEpZx8Z4dtxmba”);

    var copy = templateFile.makeCopy(Whatisyourquestion, templateResponseFolder);

    var doc = DocumentApp.openById(copy.getId());

    var body = doc.getBody();

    body.replaceText(“{{Whatisyourquestion}}”, whatIsYourQuestion);

    doc.saveAndClose();

    }

    1. BrownBearWhatDoYouSee says:

      Make sure you are submitting a test form, all of the triggers correctly formatted, and that you aren’t running in the script editor. Other than that, the first argument you pass into this function needs to be a string: var copy = templateFile.makeCopy(Whatisyourquestion, templateResponseFolder);

  12. Criss Munoz says:

    Hi there, I think this is so helpfull thanks a lot!! I have an error going on stating — TypeError: Cannot read property ‘values’ of undefined (line 2, file “Code”)Dismiss — I tried different things I saw here but didnt work this is my code look :

    function AutoFillWorkOrder(e) {
    var Timestamp = e.values(0);
    var Date = e.values(1);
    var YourName = e.values(2);
    var Order = e.values(3);
    var CustomersName = e.values(4);
    var Email = e.values(5);
    var Phone1 = e.values(6);
    var Phone2 = e.values(7);
    var Quantity = e.values(8);
    var MakeModel = e.values(9);
    var L = e.values(10);
    var W = e.values(11);
    var H = e.values(12);
    var WeightLbs = e.values(13);
    var Deposit = e.values(14);
    var COD = e.values(15);
    var Total = e.values(16);
    var SpecialInstructions = e.values(17);

    var templatefile = DriveApp.getFileById(“1XxXL6rAeI3O4tj547yO0r4Rtx6bW4AiqvwfAYrJwjk8”);
    var templateResponseFolder = Driveapp.getfolderbyid(“1kjaMSX7w2IOLGKpywe69TX20BWwhWEwR”);

    var copy = templatefile.makeCopy(CustomersName + ‘ , ‘ + Order, templateResponseFolder);

    var doc = DocumentApp.openById(Copy.getId());

    var body = doc.getBody();

    body.replaceText(“{{Date}}”, Date);
    body.replaceText(“{{Your Name}}”, YourName);
    body.replaceText(“{{Order#}}”, Order);
    body.replaceText(“{{Customers Name}}”, CustomersName);
    body.replaceText(“{{Email}}”, Email);
    body.replaceText(“{{Phone1}}”, Phone1);
    body.replaceText(“{{Phone2}}”, Phone2);
    body.replaceText(“{{Quantity}}”, Quantity);
    body.replaceText(“{{Make&Model}}”, MakeModel);
    body.replaceText(“{{L}}”, L);
    body.replaceText(“{{W}}”, W);
    body.replaceText(“{{H}}”, H);
    body.replaceText(“{{WLbs}}”, WeightLbs);
    body.replaceText(“{{Deposit}}”, Deposit);
    body.replaceText(“{{DOC}}”, COD);
    body.replaceText(“{{Subtotal}}”, Total);
    body.replaceText(“{{Total}}”, Total);

    doc.saveAndClose();

    }

    1. BrownBearWhatDoYouSee says:

      Anywhere you access the event values, you need to use square brackets [] where you are currently using parentheses (); you’re first line should look like this: var Timestamp = e.values[0]

  13. John says:

    I have a question about the trigger,

    What if I’m using Zapier to fill in the cells from another app, how would I get the code to execute then?

    1. BrownBearWhatDoYouSee says:

      Ahhh, that is a great question. Since there is no form submission, there is no form submission event object. TBH, I’m not entirely sure because I don’t know how the Zapier integration works down to the specifics. You might try wrapping the code in an onEdit trigger and see what that gets you:https://developers.google.com/apps-script/guides/triggers

      The event object would be different, but if you do some logging you might be able to get something to work: https://developers.google.com/apps-script/guides/triggers/events#edit

  14. Criss Munoz says:

    I just fixed it, I also checked the spacing and the caps and its all correct, but still is not filling up the spaces: I will paste it again here and let me know if you find something odd. Man I wanted to also say that you are amazing, thanks a lot for what you do!!

    function AutoFillWorkOrder(e) {
    var Timestamp = e.values[0];
    var Date = e.values[1];
    var yourname = e.values[2];
    var Order = e.values[3];
    var CustomersName = e.values[4];
    var Email = e.values[5];
    var Phone1 = e.values[6];
    var Phone2 = e.values[7];
    var Quantity = e.values[8];
    var MakeModel = e.values[9];
    var L = e.values[10];
    var W = e.values[11];
    var H = e.values[12];
    var WeightLbs = e.values[13];
    var Deposit = e.values[14];
    var COD = e.values[15];
    var Total = e.values[16];
    var SpecialInstructions = e.values[17];

    var templatefile = DriveApp.getFileById(“1ltvfMjzHuey8vkHu65sGbuUVxfn1GnCS9Jh1WKAiOvA”);

    var templateResponseFolder = DriveApp.getFolderById(“1kjaMSX7w2IOLGKpywe69TX20BWwhWEwR”);
    var copy = templatefile.makeCopy(CustomersName + ‘ , ‘ + Order, templateResponseFolder);

    var doc = DocumentApp.openById(Copy.getId());

    var body = doc.getBody();
    body.replaceText(“{{Date}}”,Date);
    body.replaceText(“[[yourname]]”,yourname);
    body.replaceText(“{{Order}}”,Order);
    body.replaceText(“{{CustomersName}}”, CustomersName);
    body.replaceText(“{{Email}}”, Email);
    body.replaceText(“{{Phone1}}”, Phone1);
    body.replaceText(“{{Phone2}}”, Phone2);
    body.replaceText(“{{Quantity}}”, Quantity);
    body.replaceText(“{{MakeModel}}”, MakeModel);
    body.replaceText(“{{L}}”, L);
    body.replaceText(“{{W}}”, W);
    body.replaceText(“{{H}}”, H);
    body.replaceText(“{{WLbs}}”, WeightLbs);
    body.replaceText(“{{Deposit}}”, Deposit);
    body.replaceText(“{{DOC}}”, COD);
    body.replaceText(“{{Subtotal}}”, Total);
    body.replaceText(“{{Total}}”, Total);

    doc.saveAndClose();

    }

  15. Leo says:

    Thanks for the these posts. This is my first time trying to code. I’m getting the error message and can’t work out why. I’m sure it’s some oversight on my part. This is my code:

    function appendSignatureRow(e){
    const lock = LockService.getScriptLock();
    lock.waitLock(30000);
    const Organisation = e.values[1];
    const Name = e.values[2];
    const Position = e.values[3];
    const tableCells = [Organisation, Name, Position]
    const letter = DocumentApp.openById(‘1LxDgdY3Ed9qQWqWggRDnWmPDjolWTak8es2RhnjqvjY’)
    const body = letter.getBody();
    const table = body.getTables()[0]
    const tableRow = table.appendTableRow()
    tableCells.forEach(function(cell, index){
    let appendedCell = tableRow.appendTableCell(cell)
    })
    letter.saveAndClose();
    lock.releaseLock();
    }

    1. BrownBearWhatDoYouSee says:

      How are you running the script? It expects to be run from the spreadsheet using the on form submit trigger?

  16. Mr. Hoskins says:

    I am trying to create an agenda from google forms where I do not have to personally recreate a new file each time. I want the process to look like this

    Go to google forms
    fill the form out to fulfill my agenda
    and it automatically populates over to a word dox as a nicely formatted agenda for the day.

    here is my code based off of your video (https://www.youtube.com/watch?v=ziLtj5-_D7c) and website post (https://jeffreyeverhart.com/2018/09/17/auto-fill-google-doc-from-google-form-submission/)

    I know basically ZERO about coding so im sure that is my biggest issue here

    function AutofillgoogledocfromAgendaForm(e) {
    var date = e.values[0];
    var lessontopic = e.values[4];
    var todaysobjective = e.values[5];
    var standardsTEKS = e.values[6];
    var announcements = e.values[7];
    var DailyAttendance = e.values[8];
    var SpecialInstructions = e.values[9];
    var inperson = e.values[10];
    var remote = e.values[11];

    var agendatemplate1 = DriveApp.getFileById(“1RB_s3DZqDQAQtMOzzsAMU2jxHLNv1vXrmhePx0ZXdF4”);

    var PopulatedAgendas = DriveApp.getFolderById(“1KEyq31RSpGmh-TYJXQd_-0tfk4XyRQlA”) ;
    var copy = file.makeCopy(date + ‘,’ + lessontopic, PopulatedAgendas);

    var doc = DocumentApp.openById(copy.getId());

    var body = doc.getBody();

    body.replaceText(“{{date}}”, date);
    body.replaceText(“{{lessontopic}}”, lessontopic);
    body.replaceText(“{{todaysobjective}}”, todaysobjective);
    body.replaceText(“{{standards(TEKS)}}”, standardsTEKS);
    body.replaceText(“{{Announcements}}”, announcements);
    body.replaceText(“{{DailyAttendance}}”, DailyAttendance);
    body.replaceText(“{{SpecialInstructions}}”,SpecialInstructions);
    body.replaceText(“{{Inperson}}”, inperson);
    body.replaceText(“{{Remote}}”, remote);
    doc.saveAndClose();
    }

    1. BrownBearWhatDoYouSee says:

      It would be helpful to know what is happening now. Are you seeing an error, or are all of the fields not populating correctly?

      If you can provide more info, I can give you some additional advice.

      Regards,
      Jeff

  17. Tammy M says:

    I am new to this and followed along with a tutorial. I have done troubleshooting and CAN NOT figure out what to do to run this properly! Please help. 🙁

    function autoFillGoogleDocfromForm(e) {

    var timestamp = e.values[0];
    var fullname = e.values[1];
    var troopnumber = e.value[2];
    var Email = e.value[3];
    var parentEmail = e.value[4];
    var Req1 = e.value[5];
    var Req2 = e.value[6];
    var Req3 = e.value[7];
    var Req4 = e.value[8];
    var Req5d1 = e.value[9];
    var Req5d2 = e.value[10];
    var Req5d3 = e.value[11];
    var Req5d4 = e.value[11];
    var Req5d5 = e.value[12];
    var Req5d6d = e.value[13];

    var templateFile = DriveApp.getFileById(“1W1lcsrlKBNz65MXzjxJLifvahjkfOgu8kvnyZzrnQc4”);
    var templateResponseFolder = DriveApp.getFolderById(“1uYePvnryDusPc1K2j_-7u2nFtebZhQJI”);

    var copy = templateFile.makeCopy(fullName, templateResponseFolder);

    var doc = DocumentApp.openById(copy.getId());

    var body = doc.getBody();

    body.replaceText(“{{FullName}}”, fullname);
    body.replaceText(“{{Date}}”, timestamp);
    body.replaceText(“{{Req1}}”, Req1);
    body.replaceText(“{{Req2}}”, Req2);
    body.replaceText(“{{Req3}}”, Req3);
    body.replaceText(“{{Req4}}”, Req4);
    body.replaceText(“{{Req5d1}}”, Req5d1);
    body.replaceText(“{{Req5d2}}”, Req5d2);
    body.replaceText(“{{Req5d3}}”, Req5d3);
    body.replaceText(“{{Req5d4}}”, Req5d4);
    body.replaceText(“{{Req5d5}}”, Req5d5);
    body.replaceText(“{{Req5d6d}}”, Req5d6d);

    doc.saveAndClose();
    }

    1. BrownBearWhatDoYouSee says:

      Make sure that all of your variables match between where you define them at the top and where you use them in the code below. For example, you define a variable called ‘fullname’ but then try to use a variable called ‘fullName’ in the line where you make a copy. This would cause an error, so start by fixing that and then check all of the other usages.

      Thanks for reading,
      Jeff

  18. Mila Boteva says:

    Hello i am trying to run a code but i have this error
    TypeError: Cannot read property ‘values’ of undefined (line 3, file “Code”)

    function autoFillGoogleDocFromForm(e) {

    var organization = e.values[1];
    var country = e.values[2];
    var chair = e.values[3];
    var person = e.values[4];
    var email = e.values[5];
    var phone = e.values[6];
    var motivation = e.values[7];
    var member = e.values[8];
    var participation = e.values[9];
    var requirements = e.values[10];
    var presentation = e.values[11];
    var prices = e.values[12];
    var venue = e.values[13];
    var access = e.values[14];
    var service = e.values[15];
    var event = e.values[16];
    var training = e.values[17];

    var file = DriveApp.getFileById(‘1BsvL7leQ27OpF8rwFjIdoDCKHqVxfdDC’);

    var folder = DriveApp.getFolderById(‘1DyfRqLS1d7nyR2eyEkEFFFpJvSjf4blX’);
    var copy = file.makeCopy(organization + ‘_’ + country, folder);

    var doc = DocumentApp.openById(copy.getId());

    var body = doc.getBody();

    body.replaceText(‘{{organization}}’, organization);
    body.replaceText(‘{{country}}’, country);
    body.replaceText(‘{{chairorganize}}’, chair);
    body.replaceText(‘{{person}}’, person);
    body.replaceText(‘{{contact}}’, email+’/’+phone);
    body.replaceText(‘{{motivation}}’, motivation);
    body.replaceText(‘{{member}}’, member);
    body.replaceText(‘{{participation}}’, participation);
    body.replaceText(‘{{requirements}}’,requirements);
    body.replaceText(‘{{presentation}}’,presentation);
    body.replaceText(‘{{price}}’, prices);
    body.replaceText(‘{{venue}}’, venue);
    body.replaceText(‘{{access}}’, access);
    body.replaceText(‘{{service}}’, service);
    body.replaceText(‘{{event}}’, event);
    body.replaceText(‘{{training}}’, training);

    doc.saveAndClose();
    }

    i dont understand why ….i tried test form but i the new copy files does not appear in the folder

    1. BrownBearWhatDoYouSee says:

      Hi,
      Thanks for reading. If you are trying the test form and still getting that error, I’d recommend taking another look at the trigger set in the script editor to make sure the right event is getting sent from the spreadsheet. Thanks for reading, and comment back if that doesn’t yield results.

      Regards,
      Jeff

  19. Haley C says:

    Thank you for all of your work! I have used this script and set up the trigger but when I submit a form I don’t get a new document…any ideas? I tried running it in the script editor to see what’s going on and got this error message:

    “TypeError: Cannot read property ‘value’ of undefined
    autofillWorksheetFromForm @ Code.gs:2”

    function autofillWorksheetFromForm(e) {
    var studentName = e.value[2];
    var dateSubmitted = e.value[0];
    var studentId = e.value[3];
    var studentGrade = e.value[4];
    var primaryLanguage = e.value[5];
    var dateOfRequest = e.value[6];
    var reasonForRequest = e.value[9];
    var linkToRequest = e.value[8];
    var correctionAorC = e.value[1];
    var schoolName = e.value[10];

    var templateFile = DriveApp.getFileById(’18qlgLDfb8wh3UB6csI9bcIwKV0GWUntZdqSFdLQKRKs’);
    var templateResponseFolder = DriveApp.getFolderById(‘1A26ZW-M3GRdzSeVcidiAB64cXYn_arSk’);

    var copy = templateFile.makeCopy (correctionAorC + “-” + studentName + “-” + schoolName);
    var doc = DocumentApp.openById(copy.getId());

    var body = doc.getBody();

    body.replaceText(“{{StudentName}}”,studentName);
    body.replaceText(“{{DatefromTimestamp}}”, dateSubmitted);
    body.replaceText(“{{StudentId}}”, studentId);
    body.replaceText(“{{StudentGrade}}”, studentGrade);
    body.replaceText(“{{PrimaryLanguage}}”, primaryLanguage);
    body.replaceText(“{{DateofRequest}}”, dateOfRequest);
    body.replaceText(“{{ReasonforRequest}}”,reasonForRequest);
    body.replaceText(“{{LinktoWrittenRequest}}”,linkToRequest);

    doc.saveAndClose();
    }

    1. BrownBearWhatDoYouSee says:

      You have a type. It should be e.values not e.value in all of the place where that appears. You will also always get that particular error code when running the code in the script editor.

  20. Emmanuel Meraz says:

    Good afternoon guys,
    I’m running into this problem “cannot read property “values” from undefined” with a simple auto-fill from sheets into a template document. The code below is what I’m using could someone help me please? I’m new and still figuring things out.

    function autoFillGoogleDocFromForm(e) {
    var timestamp = e.values[0];
    var firstName = e.values[1];
    var lastName = e.values[2];
    var gender = e.values[3];
    var height = e.values[4];
    var weight = e.values[5];
    var bmi = e.values[6];
    var alevel = e.values[7];

    var templateFile = DriveApp.getFileById(“18y4UhZnTlMhQhE0dQ13vCQrbwCJxJ1dZetCBh_DZD_w”);
    var templateResponseFolder = DriveApp.getFolderById(“1Sb6D1MYCR1Rj_8I_z-PtaxysrIe9SqJZ”);

    var copy = templateFile.makeCopy(lastName + ‘,’ + firstName, templateResponseFolder);

    var doc = DocumentApp.openById(copy.getId());

    var body = doc.getBody();

    body.replaceText(“{{FirstName}}”, firstName)
    body.replaceText(“{{LastName}}”, lastName)
    body.replaceText(“{{Gender}}”, gender)
    body.replaceText(“{{Height}}”, height)
    body.replaceText(“{{Weight}}”, weight)
    body.replaceText(“{{Bmi}}”, bmi)
    body.replaceText(“{{ActivityLevels}}”, alevel)

    doc.saveAndClose();

    }

    1. BrownBearWhatDoYouSee says:

      Hi Emmanuel,
      Common question that I’ve outlined in another post here: https://jeffreyeverhart.com/2016/08/06/fix-typeerror-cannot-read-property-values-undefined/

      If it is still problematic after reading that post, feel free to comment back.

      Thanks for reading,
      Jeff

  21. Morris Finestone says:

    Hi Jeff, this is amazing as I have never attempted it. I have had an error. Can I get some help please.

    Here is the error I get:
    11:16:17 AM
    Notice
    Execution started
    11:16:17 AM
    Error
    TypeError: Cannot read property ‘values’ of undefined
    autoFillGoogleDocFrom
    @

    I attempted to follow your coding letter for letter but it doesn’t want to work.

    1. BrownBearWhatDoYouSee says:

      Hi Morris, I assume these are the logs when a form is submitted? Running from the script editor won’t work. JE

  22. Morris Finestone says:

    I figured I should include my code:

    function autoFillGoogleDocFrom(e) {
    var timestamp = e.values[0];
    var emailaddress = e.values[1];
    var firstName = e.values[2];
    var lastName = e.values[3];
    var grade = e.values [4];
    var date = e.values[5];
    var timeorPeriodThisHappened = e.values[6];
    var whatHappened = e.values[7];
    var whoWasInvolved = e.values [8];
    var howDidThisMakeYouFeel = e.values [9];
    var inretrospect = e.values [10];

    var templateFile = DriveApp.getFileById(“1M6RioWXj7FlniyFdvPuzayrUswBzxm6_zdlaDZ31wq0”);
    var templateResponseFolder = DriveApp.getFolderById(“13BAqStsKtVu4lIUmEcyTd8Qfty8uvixw”);

    var copy = templateFile.makeCopy(lastName + ‘, ‘ + firstName, templateResponseFolder);

    var doc = DocumentApp.openById(copy.getId());

    var body = doc.getBody();

    body.replaceText(“{{email}}”, email);
    body.replaceText(“{{FirstName}}”, firstName) ;
    body.replaceText(“{{LastName}}”, lastName);
    body.replaceText(“{{Grade}}”, grade);
    body.replaceText(“{{Date}}”, date);
    body.replaceText(“{{TimeorPeriod}}”, timeOrPeriod);
    body.replaceText(“{{Whathappened}}”, whatHappened);
    body.replaceText(“{{Whoelsewasinvolved}}”, whoWasInvolved);
    body.replaceText(“{{Howdidthismakeyoufeel}}”, howDidThisMakeYouFeel);
    body.replaceText(“{{Inretrospect}}”, inRetrospect);

    doc.saveAndClose();

    }

Leave a Reply

Your email address will not be published. Required fields are marked *