Thursday 6 March 2014

Recreate SharePoint lists in Google Apps, with both bulk editing and edit form functionality

This solution came about from a requirement to recreate SharePoint lists in Google Apps, taking across large numbers of list items, with each item needing to have  its own edit form populated with the appropriate data. Additionally, users should be able to edit the Google Sheet, and have those changes reflected in the edit forms. This attempts to give the same functionality as SharePoint where items can be edited in edit form, or in bulk via datasheet view.

The solution involves a Google Sheet, with related form, some scripting, and an Awesome Table to display the end result.

To begin, I created a form, with the same field names and types as on SharePoint, and a Google Sheet to collect these responses.

Next I used a great script used as part of an Awesome Table gadget
This uses the forms service, to find the appropriate URL for each submitted form, and write this to the sheet. (in my example, this is writing the URL to column E (5))

var formURL = 'https://docs.google.com/a/nihr.ac.uk/forms/d/abcdefghijklmnop123456789/edit';
var sheetName = 'Master';
var columnIndex = 5;

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

I set a trigger for this to run on form submit, so that when new forms are created, the URLs can be obtained and recorded in the sheet


I could have pasted in the historical data list from SharePoint at this point, but these would not have an edit form associated with them.  I therefore used a script to generate a number of blank form entries, which could be later used as the edit forms for the data.

function CreateSomeBlanks() {
  var form = FormApp.openById('abcdefghijklmnop1234567890');  //replace with your own form ID
  var numberrows = Browser.inputBox('How many blank forms do you want to create', Browser.Buttons.OK_CANCEL);
  for (var x = 0; x < numberrows; x++) {
    var formResponse = form.createResponse();
    formResponse.submit();
    Utilities.sleep(1000);  // we want each entry to have a unique timestamp, so introduce a delay
  }
};

With Google forms you can use a "get prefilled url" so that for new forms specific data can be entered already when the user loads the new form up. I found that you could use this method also for edit forms and use the awesome table to specify what data needs to go in. This allowed me to adapt the generated URL to prefill the edit forms with sheet data.

(NB Any dates need converting into "yyyy-mm-dd" as text using an array formula such as =ArrayFormula(text(K3:K,"yyyy-mm-dd")) as otherwise the prefil does not work correctly)

Once this is done, paste in all the historical data into the Google Sheet.

Here's the Awesome Table setup used for the demo
And here's the background sheet

The end result - each row has an edit link, while any changes to the edit forms write back to the sheet. In addition if any changes are made to the sheet, these values are also populated in the edit forms.

28 comments:

  1. Hi James, I saw this linked from the AT forums. Does this only work with form entries (not something I have any knowledge of) or will it work with normal inputted data onto a googlesheet? I have a sheet with 36k+ entries (imported from xlsx) and would like to use the stringFilter search boxes to search for a specific entry and then edit that directly in AW, rather than sorting columns and/or doing a CTRL+F on the sheet itself which is annoying, especially when sorting by column as the process takes ~5 seconds to complete due to the amount of data. Possible to do? Thanks.

    ReplyDelete
    Replies
    1. Hi - this method was specifically designed to work with forms - I have not seen any other methods of being able to edit data via an Awesome Table unfortunately. With this method the forms interface allows you to edit data, without that data having been inputted into forms in the first place, so hopefully it could be of use in your situation (although the volume of data you have is quite large, so would take a while to create lots of blank form entries even using the script)

      Delete
  2. Hello James. First of all, thanks for sharing. I am trying your method without a half success yet, but I won´t give up. I have a simple question about the conversion dates. Do I have to apply that conversion to the timestamp column? Would you be so kind to explain the reasons why the prefill does not work properly if one does not execute this conversion?

    Thanks in advance.

    Alex

    ReplyDelete
  3. Hi again James. I would be really glad if you could help me with this. I am not sure what am I doing wrong. I have gotten the generated URLs with the second script, but when trying to bulk the data into the forms through AT I get a not found error message, but the forms are there and were generated correctly by the script. Would you be so kind to give me a clue? I can not bulk the data into the forms with AT yet...

    Thanks in advance.

    Alex

    ReplyDelete
  4. Hello James. Just to let you know: 100% SUCESS!! I have accomplished the goal of bulking data, my mistake was that I was not following your instrucctions correctly, particularly those ones related to the prefilled forms.
    So, don´t bother in answer please, and once again, thanks for sharing your knowledge, it is one way to become inmortal!! :)

    Best regards,

    Alex

    ReplyDelete
    Replies
    1. Hi Alejandro - Apologies for delay in responding. Am pleased you have this all working :-)

      Delete
  5. Does this still work? I made a sample some months ago which worked fine whilst now changes on the sheet are not reflected on the form.

    ReplyDelete
    Replies
    1. Yes, I used this method today and it still works fine

      Delete
    2. Thanks. I recall it worked. Url form seems to have changed. I've set the new url on the script and shouts: No item with the given ID could be found, or you do not have permission to access it. (line 8, file "getEditResponseUrls")

      Delete
    3. I get that same error usually either when I do not have edit rights to the form I am trying to use in the script, or less frequently when I have put in a wrong URL for the form.

      Delete
  6. Thanks. I've fixed the url and works again.
    If I may ask one more question, is it possible that prefilled forms can only be seen by persons who's been granted to see the awesome-table?

    ReplyDelete
    Replies
    1. Hi Bolbo, data only actually physically exists in the forms once you have clicked on the link for that line in the Awesome Table and saved. Until that time, the data is just in the sheet and the awesome table. Unless someone guesses the exact URL of the edit form for a particular entry (pretty much impossible) only those people who can view the Awesome table would see the edit link and be able to access the form URL as a result. Therefore essentially, only those people who have access to the awesome table (either by your spreadsheet sharing settings or via site permissions on the page where the AT is hosted) would be able to see the form data.

      Delete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Hi James.
The "get prefilled url" is not enough so that the form displays MultipleChoiceItems csv from the spreadsheet.
I think this script could be useful:
    http://stackoverflow.com/questions/26393556/how-to-prefill-google-form-checkboxes/26395487#26395487

    ReplyDelete
    Replies
    1. Hi Bolbo - that's a really useful script - thanks for flagging this up. I don't think it could be combined easily with the method in this blogpost, but it is definitely a GAS script solution that I have bookmarked ;-)

      Delete
  9. So a possible solution without scripting could be split() columns with csv coming from checkboxes?

    ReplyDelete
  10. Hi James,
    I just tried your setup and it works perfectly - thank you so much!
    One thing I've noticed however, although the Edit Entry links work as expected and my Google spreadsheet updates correctly, the Awesome Table does not seem to show the changes until I refresh the page. Is this the same for you or am I doing something wrong?
    Perhaps there is a way to refresh the Awesome Table on form submit?

    ReplyDelete
    Replies
    1. Hi Teonie - a pleasure :-) I am afraid the lack of refresh is expected behaviour. We did talk about this on the Awesome Table Community a while back and I don't think there is a way of doing an automatic refresh on form submit, apologies.

      Delete
  11. Hi James,

    Many thanks for sharing this method. Like you and others I've got an existing spreadsheet that I would like to generate the Edit Entry links for each record. I've tried to follow your guide but am stuck at the pre-filled url stage.

    Following your guide, I have successfully created a new spreadsheet linked to a form, created the EditResponseUrl and CreateSomeBlanks functions, and created the blank records. I understand the concept of "specifying what data needs to go in" in a pre-filled url, as in something along the lines of:

    ?entry.2030766534=2016-12-12&entry.1484190582=Event&entry.1713813159=Website&entry.1577295531=Yes

    But I don't know how to adapt it to the Edit Entry links. Simply adding the above line after the generated Edit Entry links only brings me to a (correctly) pre-filled form that, upon submission, would create a new record in the spreadsheet rather than amend the existing record.

    Can you please kindly let me know how I can correct that? Many thanks for your time and help.

    Best,
    Chun

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
  12. I'm trying to recreate this as well. Is this still up and running?

    ReplyDelete
    Replies
    1. Yes this should still be working. Let me know if you have any issues

      Delete
  13. Hi James,
    thanks for this article, but your AT doesn't show up, so i can't see the template code you used in the sheet, can you please fix this?

    ReplyDelete
    Replies
    1. Hi - sure - ATs changed their code a while back (you used to be able to iframe in the setup) I have linked to the sheet and the AT setup now

      Delete
  14. thanks for the updated links.
    i wanted to ask, what if i'm having a couple of sheets that collects a couple of forms, it seems that the script is not allways running correct, using this method is it better to separate each form into it's own spreadsheet (with template) rather then gathering them into a single file?

    ReplyDelete
    Replies
    1. Hi drorlazar - A pleasure. I have not tried this with multiple forms, but think your suggestion would be a good idea if you are hitting issues. You could always use an IMPORTRANGE formula to bring back the second sheet into the first if required

      Delete
  15. Hi James, I've had problems with the edit link in that when clicked the bulk pasted/imported data does not show in the form for editing. Only new data posted via the form is editable in the form. Does this have something to do with the dates you mentioned? I was not able to convert the dates imported. I couldn't figure that part out

    ReplyDelete