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.
