CODEWITHSUNDEEP
google-apps-scriptgoogle-drive-apigoogle-forms
Shep Sims
Shep Sims

Reputation: 1560

How to use Apps Script to create multiple forms that feed into one sheet WITH SHEET TITLES

I have been tasked with creating an automated feedback reporting system, which should send feedback forms to each member of a given team within our company. I have currently set it up so that for each member in a team, all other team members get that person's survey, and the responses are recorded and recorded in a single google sheet to then be exported to powerBI.

My problem is that in the responses sheet each worksheet title is "Form Response 1", "Form Response 2", etc. and not the name of the form that it came from, so the data collected cannot be attributed to the correct person, so its all pretty much useless.

I know that I could instead record all of the data to separate spreadsheets, then combine them in powerBI, or excel, or even in sheets, but this is NOT DESIRABLE. That would require additional steps in between data collection and data export, and I just want to be able to export the file and fly with that, so please, PLEASE don't recommend this as a workaround as it wouldn't help me at all.

I am including ***** before each relevant line to make it easier for those looking to reproduce to find useful lines

Here is the main function: 

function main() {
  var groupEmails = ["[email protected]", "[email protected]", "[email protected]"]
  var rootFolder = DriveApp.getRootFolder();
  var surveyFolder = getOrCreateFolder(rootFolder, "SurveyFolder");
  var responseFolder = getOrCreateFolder(surveyFolder, "responseFolder")

  *****var responseFile = SpreadsheetApp.create("Responses");
  *****responseFolder.addFile(DriveApp.getFileById(responseFile.getId()));

 // Loop through groups, creating forms for each user then sending those to all other group members
  for (i in groupEmails){
    groupEmail = groupEmails[i]
    var userEmailMap = getUserEmailMap(groupEmail);

    // Get or create SurveyFolder
    var teamSurveyFolder = getOrCreateFolder(surveyFolder, groupEmail.substring(0,groupEmail.indexOf('@')).concat("SubjectiveSurveys"));

    // Create a user folder and add a form to that folder
    var formMap = {}
    for (fullName in userEmailMap){
      var email = userEmailMap[fullName]
      var userFolder = getOrCreateFolder(teamSurveyFolder, fullName);
      var title = fullName + "'s Feedback Survey For " + getTodaysDate()

      // Create the new form and move it to the user folder
      var userForm = createFormForUser(title, getFirstName(email), responseFile.getId());
      moveFile(userForm.getId(), rootFolder.getId(), userFolder.getId());
      formMap[email] = userForm
    }
  }
}

and here is my (simplified) form creation function:

function createFormForUser(title, user, sheet) {
  form = FormApp.create(title);

  form.setTitle(title);

  // add grid 
  form.addGridItem()
  .setTitle("Feedback Type")
  .setRows(['Rate team member based on how much you would like to grab a beer with them outside of work'])
  .setColumns(['0','1','2','3','4','5','6 (N/A)'])
  .setRequired(true)

  *****form.setDestination(FormApp.DestinationType.SPREADSHEET, sheet);

  return form
}

Upvotes: 0

Views: 582

Answers (1)

TheMaster
TheMaster

Reputation: 50445

You can get form urls on each sheet using sheet.getFormUrl() and rename each sheet on the spreadsheet side.

Snippet:

responseFile
  .getSheets()
  .forEach(function(sheet) {
    if ((url = sheet.getFormUrl()))
      sheet.setName(FormApp.openByUrl(url).getTitle());
  });

Upvotes: 3

Related Questions