Allow users to update your view’s data with Google Forms

Follow

Comments

29 comments

  • Avatar
    Fev Ramireli

    I'm trying to use your script but I keep getting the same error in line 7:

    var data = sheet.getDataRange().getValues();

    getDataRange null

    I already checked and double checked the variable sheetName, but since I just inserted the code and I'm by no means a coder or programmer, I don't know what to do.

    Attached a ss for more deatail

  • Avatar
    Awesome Table

    Hello Fav Ramireli,

    Could you create a ticket for your issue? Please, share your view and your spreadsheet.
    Make sure you also give access your spreadsheet at "support@awesome-table.com". 

    Thanks in advance.

    Thomas

  • Avatar
    Fev Ramireli

    Thank you for the follow up, my Awesome Table its working like a charm.

  • Avatar
    Bill

    On function geteditresponseurl line

    var columnIndex = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("Update").getColumn().toString();

    I'm getting

    TypeError: Cannot call method "getColumn" of null. (line 36, file "Code")

  • Avatar
    Thomas Morin

    Hello Bill,

    You need to reproduce the exact code we mention in the article above. Your code is not the same in this article.

    The var columnIndex allows you to attribute the column where you want to put the link like this:

    var columnIndex = 8 ;

    Finally, the var sheet must be like this:

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

    Please, apply identically the code we provide in this article and your view should work.

    Thank you for your understanding.

    Thomas

  • Avatar
    Harvest Church

    Hi there,

    I have an inventory that I'm trying to use this script on. I've created a view, I already had the spreadsheet, I have a form as well and configured the form responses tab in the spreadsheet. When I run the script it runs through but doesn't generate any links in my spreadsheet. No errors when I run the debugging script and if I open up the form and input some data it generates a link. I added just a few rows, but I have a thousand lines of data I'd like to import and from what I gathered this script was supposed to auto generate links for every line?

  • Avatar
    Awesome Table

    Hello,

    This script is made to only generate edit URLs from responses that are directly coming from Google Form that is why it is neither generating urls nor displaying any errors.

    Nicolas

  • Avatar
    Cat Russell

    Hello, I really am loving this tool. 

    I have my table working with a template and have the update links inserted which are working as well. Yay.  

    But in some of the examples, there is an "add entry" or "add row" button at the very top of the example (view) which opens the form for a new entry via the Google Form. 

    How do I add that to my view? I can't seem to work it out. 

    Thanks

  • Avatar
    Awesome Table

    Hello Cat!

    Sorry if our documentation was not clear enough!

    You can add any HTML on top of your Awesome Table view using templates.

    Here is how to configure a template sheet:https://support.awesome-table.com/hc/en-us/articles/115004170225--Part-2-Set-up-your-template#2

    Here is how to have a header: https://support.awesome-table.com/hc/en-us/articles/115001118689-Add-a-header-on-your-view-and-build-advanced-templates-with-CSS-and-Javascript#3

    From then you can simply add an HTML button with a link to your Google Form.

    Please let me know if I can help you with this.

    Regards,

    Nicolas

     

  • Avatar
    Joseph Vaughan

    It seems that one also needs to enter text in a cell alongside the prefilled URL, such as "Edit Entry".  Otherwise, the button doesn't show up for each row.   Perhaps it would be a good idea for the script to do that too? 

  • Avatar
    Nicolas Gauvin

    Hello Joseph,

    We wanted to keep this article very simple, not talk too much about the graphical aspect.

    Indeed, you will need to create an HTML template with a button with some text included, with a marker for the Update column.

    Please let me know if you need any help.

    Nicolas

  • Avatar
    Billy Milon Esparza

    Hi! As dar as I understand, this solution allows any person to edit any of the forms submitted, right? Is there an option to allow people to edit only the forms they have submitted? 
    Thanks, 

  • Avatar
    LA Family Housing Location

    Hello,

    Great template, but when I run the script, it generates the same edit URL which, when clicked, takes me to edit the very first submission no matter how many more were created.

     

    This is the code I used (I had great trouble getting openByURL to work, so I used OpenById:

     

    var formId = '10P_tOQFUdXPbUjOEWTGnBYhezpLcjLsoyXCDMMzM6qA';
    var sheetName = 'Form Responses 1';
    var columnIndex = 12 ;

    function getEditResponseUrls() {
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
    var data = sheet.getDataRange().getValues();
    var form = FormApp.openById(formId);
    for(var i = 2; i < data.length; i++) {
    if (data[i][0] != '' && data[i][columnIndex-1] == '') {
    var timestamp = data[i][0];
    var formSubmitted = form.getResponses();
    if (formSubmitted.length < 1) continue;
    var editResponseUrl = formSubmitted[0].getEditResponseUrl();
    sheet.getRange(i+1, columnIndex).setValue(editResponseUrl);
    }
    }
    }

     

    Thanks!

  • Avatar
    Thomas Morin

    Hello Billy Milon Esparza,

    You could use a proxy. In this way, users will be able to see only their own responses but if you want that users are able to see all the responses and edit only their own responses, it is not possible.

    Thank you for your understanding.

    Thomas

  • Avatar
    Thomas Morin

    Hello LA Family Housing Location,

    Actually, you need to use the "var formByURL" instead of the "var formByID". Otherwise, it won't work for you.

    If you can not make it work, please create a ticket and share with us your Awesome Table and your view at "support@awesome-table.com". 
    Just make sure you give us access to your data.

    Kind regards.

    Thomas

  • Avatar
    Eric Heide

    Hello,

    We have the same problem as Fev Ramireli had on 20.12.2018. Could you maybe please share the solution to that here?

    Kind regards,

     

    Eric

  • Avatar
    Nicolas Gauvin

    Hello Eric,

    The issue the other user had is that he did not set the value of "sheetName" with the right sheet name (he had the name of the spreadsheet, not the sheet).

    Please check that the name is correct (this is case and space sensitive).

  • Avatar
    Brandon Carraway (Edited )

    I have tried to follow this as exactly as possible, but I cannot seem to get past an error with the code:

    EDIT:

    The columnIndex is a new value, which is not reflected in the screenshot below (I added more columns with data)

     

    EDIT EDIT: 

    Just realized the problem was I was entering the 'view' form link, not the 'edit' form link... It works now, for whoever experiences the same problems.

     

    I've thumbed through the comments, but I guess I'm too dumb to understand what is wrong. XD

    Sheet: https://docs.google.com/spreadsheets/d/1lCPG-NGt1z9lyjYfDO_UY2uUp4MjMqOXomUUMpQ9whY/edit#gid=2057458028

    Form: https://docs.google.com/forms/d/e/1FAIpQLSez4malOO3zvhtdmT83pRZ9swZ52wd4MopHNvCAKS84-9bkxw/viewform

     

    Please help! I've created the Form, the Sheet, entered the code, and setup the trigger, but I can't get any further. 🤦‍♂️

  • Avatar
    Thomas Morin

    Hello Brendan Carraway,

    I created a ticket because it will be easier to discuss with you.

    Kind regards.

    Thomas

  • Avatar
    Brandon Carraway

    No need, Thomas - I figured it out.

    I was pasting the link to the 'view' form, not the 'edit' form in the Script Editor.

    I fixed the code, and now it's working. 🍻

  • The official script doesn't work.

    But LA Family Housing Location's works properly

  • Avatar
    Thomas Morin

    Hello Вячеслав Копаев,

    I create a ticket because it will be easier to discuss.

    Kind regards.

    Thomas

  • Avatar
    Fernando Rojas

    Hello!

    I followed the tutorial and it didn't work.

    I coudn't get the link of each responses. I filled the google form from the share link.

     

    Regards, Fernando

  • Avatar
    Thomas Morin

    Hello Fernando Rojas,

    I created a ticket because it will be easier to discuss with you.

    Kind regards.

    Thomas

  • Avatar
    Joe Donohoe

    Thanks for the informative post.

    We're brainstorming whether or not to add an update button for a particular project. In our normal Form-to-Sheet-to-AwesomeTable workflow, we don't require users to sign-in or anything. We have an extra column in the spreadsheet where staff can validate the entry, then enter a 1 if everything looks good. Then the query feature in our Maps With Table view looks for a 1 in that column before displaying the entry on the map.

    Any ideas how to make our validation column with 1s get deleted when a user edits their form entry, so that the entry isn't displayed until a staff member can check it out the updated entry and re-enter a 1?

    Thanks,

    Joe

  • Avatar
    Nicolas Gauvin

    Hello,

    This is not possible with a native Google Form feature.

    If you have coding knowledge, this would be achievable with an attached script (however this does not fall under our support scope).

    Regards,

    Nicolas

  • Avatar
    Joe Donohoe

    Thanks for the reply Nicolas.

    Yeah, I can do a stand-alone script, and I knew it wasn't necessarily an Awesome Table feature. I just thought I'd reach out and see if you all had done something similar or could point me in the right direction. No big deal.

     

  • Avatar
    José Pérez Colom

    Hello 

     I have the same problem in line 12 with:

      No se puede encontrar el método getResponses(string). (línea 12, archivo "Código")

     

    I doesnt work.

     

    ¿ could you help me?

     

    Thanks in advance

     

  • Avatar
    Randolph Abelardo

    Hi @José Pérez Colom.

    Is your Google Sheets datasheet linked to a live Google Forms?

    Please share your:

    AwesomeTable link
    Share with us your AwesomeTable & Google Sheets datasource so we can pinpoint where the problem is and provide the right solution at the soonest possible time. Detailed instructions here.

    Google Sheets datasource
    It's best to set the sharing settings to Anyone with the link. Click here to learn more.

    You can opt to anonymize your content by using dummy data (fake, but realistic) if your Sheets contains confidential info.


    - Randy

Please sign in to leave a comment.