Google-apps-script – How to populate and submit a Google form using info from Google Sheets

google-apps-scriptgoogle-forms

I'm new to Google script editor and not sure how to go about this. I have a group of people filling out referrals through the Google Form "A", and I would like to use the output of these forms to populate answers to (and auto-submit) the form "B". Can I take the new answers from the "A" response sheet and trigger a new submission of form "B" with all of the relevant information filled in? Is it possible to link forms in such a way?

Best Answer

i know this is an old post but this answer is for anyone else looking for the same solution to a similar problem quite recently. I did something similar recently- auto populating & auto updating a google form with data values from the google sheet after a response has been submitted, here is the working live sample form:

Related Solutions

Google-sheets – How to automatically align incoming form responses in Google Sheets

It looks like this issue is due to a bug in the new Google Sheets, but you can use this simple script as a workaround.

In your sheet, go to Tools > Script Editor. Erase the existing code in Code.gs and add this instead:

function setCellAlign(range, alignment) {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheets()[0];
   var cell = sheet.getRange(range);
   cell.setHorizontalAlignment(alignment);
 }
function onFormSubmit() {
   setCellAlign("A1:F500", "center");
}

In the second-to-last-line, change F500 to the expected bottom-right cell of your sheet. For example, if your rightmost column is G and you want to hold 1000 responses in your sheet, change the value to G1000 so the second-to-last line is setCellAlign("A1:G1000", "center");
Save the script project as "AutoAlign"
Within the Script Editor, go to Resources > Current project's triggers. Add a new trigger for onFormSubmit, From the spreadsheet, On form submit, and press "Save."

Your responses should now align automatically to the center. For whatever reason, this script fails to work 1% of the time, but if that happens the next form submission will fix the previous row.

Google-sheets – Show URL used to edit responses from a Google Form in a Google Spreadsheet by using a script

Short answer

The link points to a Q&A that has two answers but non-of them looks to me to be appropiate. I.E. the code of one of answers is incomplete and both of them send emails, so the adoption is too complex to be used as a starting point.

Below is a script that is easier to adopt.

Instructions

Create a test form and note the form ID (between ../d/ and /edit in the url).
Set the form to send responses to a new spreadsheet.
You'll be redirected to the new spreadsheet. Note the active sheet name (rename it if you want) and add a header to the column to be used to hold the response edit URLs, i.e. Edit Url. NOTE: Capitalization is very important, so be very careful on how do you write it.
Go to Tools > Script editor to add a Google Apps Script project bounded to the spreadsheet.
Replace the default code with the code below (remember to edit the global variables according to your case).
Save the project, and then add an installable form summit trigger (if you get an "Authorization required" dialog, click "Review Permissions" and then "Allow").
Submit some sample responses to test the solution.

Code

/*
 * Global Variables
 */

// Form URL
var formURL = 'https://docs.google.com/forms/d/form-id/viewform';
// Sheet name used as destination of the form responses
var sheetName = 'Form Responses 1';
/*
 * Name of the column to be used to hold the response edit URLs 
 * It should match exactly the header of the related column, 
 * otherwise it will do nothing.
 */
var columnName = 'Edit Url' ;
// Responses starting row
var startRow = 2;

function getEditResponseUrls(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues(); 
  var columnIndex = headers[0].indexOf(columnName);
  var data = sheet.getDataRange().getValues();
  var form = FormApp.openByUrl(formURL);
  for(var i = startRow-1; i < data.length; i++) {
    if(data[i][0] != '' && data[i][columnIndex] == '') {
      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+1).setValue(editResponseUrl);
    }
  }
}

Additional resources

I created a gist with the content from the question and answer. this b/c I'm exploring ways to better collaborate between end-users who write code.