Output Google Form Questions Responses to a Google Sheet Spreadsheet

0
votes

Google Forms creates responses when a question is answered. I want to take those responses and push them to a spreadsheet.

While this is a normal function of Google Forms, the normal process will not work for me. I am generating new questions on each run. Deleting all previous questions before adding in the same questions. (If they meet certain criteria.)

I have found the following piece of code, and by logging it I can see it provides me what I need. I need a way to be able to take those responses, push them into something like an array and then output that array onto the last row of sheet.

The below code gets all responses, then grabs the response received to the items. As explained above, I need to push those values to a spreadsheet. Any help would be apprietated.

     // Open a form by ID and log the responses to each question.
 var form = FormApp.openById('1234567890abcdefghijklmnopqrstuvwxyz');
 var formResponses = form.getResponses();
 for (var i = 0; i < formResponses.length; i++) {
   var formResponse = formResponses[i];
   var itemResponses = formResponse.getItemResponses();
   for (var j = 0; j < itemResponses.length; j++) {
     var itemResponse = itemResponses[j];
     Logger.log('Response #%s to the question "%s" was "%s"',
         (i + 1).toString(),
         itemResponse.getItem().getTitle(),
         itemResponse.getResponse());
   }
 }
2
google-apps-scriptgoogle-formsgoogle-sheets-api

2 Answers

0
votes

I understood that you want to import (i + 1).toString(), itemResponse.getItem().getTitle(), itemResponse.getResponse() of Logger.log() in your script to Spreadsheet. If my understanding is correct, how about the following modification?

When you use this modified sample, please input spreadsheetId and sheetName. If you use this script as a bound script of Spreadsheet, you can use this script by modifying from SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName); to SpreadsheetApp.getActiveSheet().

Modified script :

var spreadsheetId = "### spreadsheet ID ###";
var sheetName = "### sheet name ###";

// Open a form by ID and log the responses to each question.
var form = FormApp.openById('1234567890abcdefghijklmnopqrstuvwxyz');
var formResponses = form.getResponses();
var result = []; // Added
for (var i = 0; i < formResponses.length; i++) {
  var formResponse = formResponses[i];
  var itemResponses = formResponse.getItemResponses();
  for (var j = 0; j < itemResponses.length; j++) {
    var itemResponse = itemResponses[j];
    Logger.log('Response #%s to the question "%s" was "%s"',
    (i + 1).toString(),
    itemResponse.getItem().getTitle(),
    itemResponse.getResponse());
    result.push([(i + 1).toString(), itemResponse.getItem().getTitle(), itemResponse.getResponse()]); // Added
  }
}
// var sheet = SpreadsheetApp.getActiveSheet(); // If you use this script as a bound script of Spreadsheet, you can use this.
var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName); // Added
sheet.getRange(sheet.getLastRow() + 1, 1, result.length, result[0].length).setValues(result) // Added

If I misunderstand your question, I'm sorry.

0
votes

Hi random person popping in form google. Here is some code which hopefully helps you to solve your problem.

function onOpen() {
  var menu = FormApp.getUi().createAddonMenu()
  menu.addItem("Push New Questions", 'UpdateTrainingForm')
   menu.addItem("Update Available Time Slots", 'UpdateTimeSlots')
  menu.addToUi()
}

function UpdateTrainingForm() {




//----------------------------------------------------------------------------------------------------------------------
// Block X
// Obtaining Form, Workbook and Sheet.
// Obtain the Form, Workbook and Sheet to be used.
// Delete all previous items in the form before each run.
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
var form = FormApp.getActiveForm()
var ss = SpreadsheetApp.openById('FORM-SPREADSHEET ID');
var sheet = ss.getSheetByName('Time Slots');
var sheet2 = ss.getSheetByName('Form Responses 1');

//Get all items in the form
var items = form.getItems();

//Delete All Items starting from the last to the first in the Form - Skip the Last Item - This is a picture.
var end = items.length - 1;
for(var i = end ; i >= 1; i--){form.deleteItem(i);}

//Get Form Responses from Spreadsheet.
var ResponseSheet = ss.getSheetByName("Form responses 1")
var newReponses = ResponseSheet.getRange(2, 8, 1, 7);
var responses = ResponseSheet.getRange("A2:G500")

//----------------------------------------------------------------------------------------------------------------------
// Block X
// Declare Variables
// Variables are declared here for sake of neatness.
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

var tsData1, tsData2, tsData3, tsData4, tsData5, tsData6; // Timeslot Choices Array Data
var tsHeader1, tsHeader2, tsHeader3, tsHeader4, tsHeader5, tsHeader6; // Timeslot Headers
var tsChoices1,  tsChoices2,  tsChoices3,  tsChoices4,  tsChoices5, tsChoices6; // Timeslot Choices Placeholder
var tsSeatsMax1, tsSeatsMax2, tsSeatsMax3, tsSeatsMax4, tsSeatsMax5, tsSeatsMax6; //Maximum Seats Available.
var tsSeatsRemaining1, tsSeatsRemaining2, tsSeatsRemaining3, tsSeatsRemaining4, tsSeatsRemaining5, tsSeatsRemaining6; // Timeslots Seats Remaining.


// Timeslot Choices pushed to String Values
var tsChoicesString10, tsChoicesString11, tsChoicesString12, tsChoicesString13, tsChoicesString14, tsChoicesString15;
var tsChoicesString20, tsChoicesString21, tsChoicesString22, tsChoicesString23, tsChoicesString24, tsChoicesString25;
var tsChoicesString30, tsChoicesString31, tsChoicesString32, tsChoicesString33, tsChoicesString34, tsChoicesString35;
var tsChoicesString40, tsChoicesString41, tsChoicesString42, tsChoicesString43, tsChoicesString44, tsChoicesString45;
var tsChoicesString50, tsChoicesString51, tsChoicesString52, tsChoicesString53, tsChoicesString54, tsChoicesString55;
var tsChoicesString60, tsChoicesString61, tsChoicesString62, tsChoicesString63, tsChoicesString64, tsChoicesString65;

// Update Reamining Seats Time Slots to Spreadsheet everytime someone submits a response.
var updateReaminingSlots11, updateReaminingSlots12, updateReaminingSlots13, updateReaminingSlots14, updateReaminingSlots15;
var updateReaminingSlots21, updateReaminingSlots22, updateReaminingSlots23, updateReaminingSlots24, updateReaminingSlots25;
var updateReaminingSlots31, updateReaminingSlots32, updateReaminingSlots33, updateReaminingSlots34, updateReaminingSlots35;
var updateReaminingSlots41, updateReaminingSlots42, updateReaminingSlots43, updateReaminingSlots44, updateReaminingSlots45;
var updateReaminingSlots51, updateReaminingSlots52, updateReaminingSlots53, updateReaminingSlots54, updateReaminingSlots55;
var updateReaminingSlots61, updateReaminingSlots62, updateReaminingSlots63, updateReaminingSlots64, updateReaminingSlots65;


//----------------------------------------------------------------------------------------------------------------------
// Block X
// Push Spreadsheet Values from linked Spreadsheet into Array Values
// The values below are the Time Slot Headers, Time Slot Choices, Remaining Slots and Maximum Slots.
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//Push values into an array to be used as Titles/Headers for drop down menus.
tsHeader1 = sheet.getRange(1, 1, 1, 1).getValues();
tsHeader2 = sheet.getRange(8, 1, 1, 1).getValues();
tsHeader3 = sheet.getRange(15, 1, 1, 1).getValues();
tsHeader4 = sheet.getRange(22, 1, 1, 1).getValues();
tsHeader5 = sheet.getRange(29, 1, 1, 1).getValues();
tsHeader6 = sheet.getRange(36, 1, 1, 1).getValues();

//Push values into an array to be used as choices for drop down menus.
tsData1 = sheet.getRange(2, 1, 6, 1).getValues();
tsData2 = sheet.getRange(9, 1, 6, 1).getValues();
tsData3 = sheet.getRange(16, 1, 6, 1).getValues();
tsData4 = sheet.getRange(23, 1, 6, 1).getValues();
tsData5 = sheet.getRange(30, 1, 6, 1).getValues();
tsData6 = sheet.getRange(37, 1, 6, 1).getValues();

//Push values into an array to be used to display remaining available seats.
tsSeatsRemaining1 = sheet.getRange(2, 4, 5, 1).getValues();
tsSeatsRemaining2 = sheet.getRange(9, 4, 5, 1).getValues();
tsSeatsRemaining3 = sheet.getRange(16, 4, 5, 1).getValues();
tsSeatsRemaining4 = sheet.getRange(23, 4, 5, 1).getValues();
tsSeatsRemaining5 = sheet.getRange(30, 4, 5, 1).getValues();
tsSeatsRemaining6 = sheet.getRange(37, 4, 5, 1).getValues();

//Push values into an array to be used to display remaining available seats.
tsSeatsMax1 = sheet.getRange(2, 3, 5, 1).getValues();
tsSeatsMax2 = sheet.getRange(9, 3, 5, 1).getValues();
tsSeatsMax3 = sheet.getRange(16, 3, 5, 1).getValues();
tsSeatsMax4 = sheet.getRange(23, 3, 5, 1).getValues();
tsSeatsMax5 = sheet.getRange(30, 3, 5, 1).getValues();
tsSeatsMax6 = sheet.getRange(37, 3, 5, 1).getValues();


//----------------------------------------------------------------------------------------------------------------------
// Block X
// Push Array Values for tsData into String Values
// Used to create choices. Create choice only accepts String Values. Cannot use Array Value.
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//Push Variable values into Strings values to be used as Choices (10 - 15)
tsChoicesString10 = tsData1[0].filter(String);
tsChoicesString11 = tsData1[1].filter(String);
tsChoicesString12 = tsData1[2].filter(String);
tsChoicesString13 = tsData1[3].filter(String);
tsChoicesString14 = tsData1[4].filter(String);
tsChoicesString15 = tsData1[5].filter(String);

//Push Variable values into Strings values to be used as Choices (20 - 25)
tsChoicesString20 = tsData2[0].filter(String);
tsChoicesString21 = tsData2[1].filter(String);
tsChoicesString22 = tsData2[2].filter(String);
tsChoicesString23 = tsData2[3].filter(String);
tsChoicesString24 = tsData2[4].filter(String);
tsChoicesString25 = tsData2[5].filter(String);

//Push Variable values into Strings values to be used as Choices (30 - 35)
tsChoicesString30 = tsData3[0].filter(String);
tsChoicesString31 = tsData3[1].filter(String);
tsChoicesString32 = tsData3[2].filter(String);
tsChoicesString33 = tsData3[3].filter(String);
tsChoicesString34 = tsData3[4].filter(String);
tsChoicesString35 = tsData3[5].filter(String);

//Push Variable values into Strings values to be used as Choices (40 - 45)
tsChoicesString40 = tsData4[0].filter(String);
tsChoicesString41 = tsData4[1].filter(String);
tsChoicesString42 = tsData4[2].filter(String);
tsChoicesString43 = tsData4[3].filter(String);
tsChoicesString44 = tsData4[4].filter(String);
tsChoicesString45 = tsData4[5].filter(String);

//Push Variable values into Strings values to be used as Choices (50 - 55)
tsChoicesString50 = tsData5[0].filter(String);
tsChoicesString51 = tsData5[1].filter(String);
tsChoicesString52 = tsData5[2].filter(String);
tsChoicesString53 = tsData5[3].filter(String);
tsChoicesString54 = tsData5[4].filter(String);
tsChoicesString55 = tsData5[5].filter(String);

//Push Variable values into Strings values to be used as Choices (60 - 65)
tsChoicesString60 = tsData6[0].filter(String);
tsChoicesString61 = tsData6[1].filter(String);
tsChoicesString62 = tsData6[2].filter(String);
tsChoicesString63 = tsData6[3].filter(String);
tsChoicesString64 = tsData6[4].filter(String);
tsChoicesString65 = tsData6[5].filter(String);

//----------------------------------------------------------------------------------------------------------------------
// Block X
// Create List Item 1 and Populate
// List item is created and if there are remaining seats, populate the choices.
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//Grab Remaining Slots Value (D2:D6)

updateReaminingSlots12 = sheet.getRange("D3").getValue();
updateReaminingSlots13 = sheet.getRange("D4").getValue();
updateReaminingSlots14 = sheet.getRange("D5").getValue();
updateReaminingSlots15 = sheet.getRange("D6").getValue();

//Grab Remaining Slots Value (D9:D13)
updateReaminingSlots21 = sheet.getRange("D9").getValue();
updateReaminingSlots22 = sheet.getRange("D10").getValue();
updateReaminingSlots23 = sheet.getRange("D11").getValue();
updateReaminingSlots24 = sheet.getRange("D12").getValue();
updateReaminingSlots25 = sheet.getRange("D13").getValue();

//Grab Remaining Slots Value (D16:D20)
updateReaminingSlots31 = sheet.getRange("D16").getValue();
updateReaminingSlots32 = sheet.getRange("D17").getValue();
updateReaminingSlots33 = sheet.getRange("D18").getValue();
updateReaminingSlots34 = sheet.getRange("D19").getValue();
updateReaminingSlots35 = sheet.getRange("D20").getValue();

//Grab Remaining Slots Value (D23:D27)
updateReaminingSlots41 = sheet.getRange("D23").getValue();
updateReaminingSlots42 = sheet.getRange("D24").getValue();
updateReaminingSlots43 = sheet.getRange("D25").getValue();
updateReaminingSlots44 = sheet.getRange("D26").getValue();
updateReaminingSlots45 = sheet.getRange("D27").getValue();

//Grab Remaining Slots Value (D30:D34)
updateReaminingSlots51 = sheet.getRange("D30").getValue();
updateReaminingSlots52 = sheet.getRange("D31").getValue();
updateReaminingSlots53 = sheet.getRange("D32").getValue();
updateReaminingSlots54 = sheet.getRange("D33").getValue();
updateReaminingSlots55 = sheet.getRange("D34").getValue();

//Grab Remaining Slots Value (D37:D41)
updateReaminingSlots61 = sheet.getRange("D37").getValue();
updateReaminingSlots62 = sheet.getRange("D38").getValue();
updateReaminingSlots63 = sheet.getRange("D39").getValue();
updateReaminingSlots64 = sheet.getRange("D40").getValue();
updateReaminingSlots65 = sheet.getRange("D41").getValue();


//----------------------------------------------------------------------------------------------------------------------
// Block X
// Create List Item 1 and Populate
// List item is created and if there are remaining seats, populate the choices.
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//TimeSlot 1 - Google Sheets Training
var item1 = form.addListItem()
item1.setTitle(tsHeader1).setChoices([item1.createChoice(tsChoicesString15)]);       
tsChoices1 = item1.getChoices()

if(tsSeatsRemaining1[0] > 0){
tsChoices1.push(item1.createChoice(tsChoicesString10));
item1.setChoices(tsChoices1);
} //END OF TIME SLOTS 1 CHOICE IF 1.1

if(tsSeatsRemaining1[1] > 0){
tsChoices1.push(item1.createChoice(tsChoicesString11));
item1.setChoices(tsChoices1);
} //END OF TIME SLOTS 1 CHOICE IF 1.2

if(tsSeatsRemaining1[2] > 0){
tsChoices1.push(item1.createChoice(tsChoicesString12));
item1.setChoices(tsChoices1);
} ///END OF TIME SLOTS 1 CHOICE IF 1.3

if(tsSeatsRemaining1[3] > 0){
tsChoices1.push(item1.createChoice(tsChoicesString13));
item1.setChoices(tsChoices1);
} //END OF TIME SLOTS 1 CHOICE IF 1.4

if(tsSeatsRemaining1[4] > 0){
tsChoices1.push(item1.createChoice(tsChoicesString14));
item1.setChoices(tsChoices1);
} //END OF TIME SLOTS 1 CHOICE IF 1.5

//----------------------------------------------------------------------------------------------------------------------
// Block X
// Create List Item 2 and Populate
// List item is created and if there are remaining seats, populate the choices.
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////


//TimeSlot 2 - Google Drive Training
var item2 = form.addListItem()
item2.setTitle(tsHeader2).setChoices([item2.createChoice(tsChoicesString25)]);       
tsChoices2 = item2.getChoices()

if(tsSeatsRemaining2[0] > 0){
tsChoices2.push(item2.createChoice(tsChoicesString20));
item2.setChoices(tsChoices2);
} //END OF TIME SLOTS 2 CHOICE IF 2.1

if(tsSeatsRemaining2[1] > 0){
tsChoices2.push(item2.createChoice(tsChoicesString21));
item2.setChoices(tsChoices2);
} //END OF TIME SLOTS 2 CHOICE IF 2.2

if(tsSeatsRemaining2[2] > 0){
tsChoices2.push(item2.createChoice(tsChoicesString22));
item2.setChoices(tsChoices2);
} //END OF TIME SLOTS 2 CHOICE IF 2.3

if(tsSeatsRemaining2[3] > 0){
tsChoices2.push(item2.createChoice(tsChoicesString23));
item2.setChoices(tsChoices2);
} //END OF TIME SLOTS 2 CHOICE IF 2.4

if(tsSeatsRemaining2[4] > 0){
tsChoices2.push(item2.createChoice(tsChoicesString24));
item2.setChoices(tsChoices2);
} //END OF TIME SLOTS 2 CHOICE IF 2.5

//----------------------------------------------------------------------------------------------------------------------
// Block X
// Create List Item 2 and Populate
// List item is created and if there are remaining seats, populate the choices.
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//TimeSlot 3 - Google Forms Training
var item3 = form.addListItem()
item3.setTitle(tsHeader3).setChoices([item3.createChoice(tsChoicesString35)]);       
tsChoices3 = item3.getChoices()

if(tsSeatsRemaining3[0] > 0){
tsChoices3.push(item3.createChoice(tsChoicesString30));
item3.setChoices(tsChoices3);
} //END OF IF 3.1

if(tsSeatsRemaining3[1] > 0){
tsChoices3.push(item3.createChoice(tsChoicesString31));
item3.setChoices(tsChoices3);
} //END OF IF 3.2

if(tsSeatsRemaining3[2] > 0){
tsChoices3.push(item3.createChoice(tsChoicesString32));
item3.setChoices(tsChoices3);
} //END OF IF 3.3

if(tsSeatsRemaining3[3] > 0){
tsChoices3.push(item3.createChoice(tsChoicesString33));
item3.setChoices(tsChoices3);
} //END OF IF 3.4

if(tsSeatsRemaining3[4] > 0){
tsChoices3.push(item3.createChoice(tsChoicesString34));
item3.setChoices(tsChoices3);
} //END OF IF 3.5


//----------------------------------------------------------------------------------------------------------------------
// Block X
// Create List Item 2 and Populate
// List item is created and if there are remaining seats, populate the choices.
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//TimeSlot 4 - Google Sheets Training
var item4 = form.addListItem()
item4.setTitle(tsHeader4).setChoices([item4.createChoice(tsChoicesString45)]);       
tsChoices4 = item4.getChoices()

if(tsSeatsRemaining4[0] > 0){
tsChoices4.push(item4.createChoice(tsChoicesString40));
item4.setChoices(tsChoices4);
} //END OF IF 4.1

if(tsSeatsRemaining4[1] > 0){
tsChoices4.push(item4.createChoice(tsChoicesString41));
item4.setChoices(tsChoices4);
} //END OF IF 4.2

if(tsSeatsRemaining4[2] > 0){
tsChoices4.push(item4.createChoice(tsChoicesString42));
item4.setChoices(tsChoices4);
} //END OF IF 4.3

if(tsSeatsRemaining4[3] > 0){
tsChoices4.push(item4.createChoice(tsChoicesString43));
item4.setChoices(tsChoices4);
} //END OF IF 4.4

if(tsSeatsRemaining4[4] > 0){
tsChoices4.push(item4.createChoice(tsChoicesString44));
item4.setChoices(tsChoices4);
} //END OF IF 4.5

//----------------------------------------------------------------------------------------------------------------------
// Block X
// Create List Item 2 and Populate
// List item is created and if there are remaining seats, populate the choices.
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//TimeSlot 5 - Google Sheets Training
var item5 = form.addListItem()
item5.setTitle(tsHeader5).setChoices([item5.createChoice(tsChoicesString55)]);       
tsChoices5 = item5.getChoices()

if(tsSeatsRemaining5[0] > 0){
tsChoices5.push(item5.createChoice(tsChoicesString50));
item5.setChoices(tsChoices5);
} //END OF IF 5.1

if(tsSeatsRemaining5[1] > 0){
tsChoices5.push(item5.createChoice(tsChoicesString51));
item5.setChoices(tsChoices5);
} //END OF IF 5.2

if(tsSeatsRemaining5[2] > 0){
tsChoices5.push(item5.createChoice(tsChoicesString52));
item5.setChoices(tsChoices5);
} //END OF IF 5.3

if(tsSeatsRemaining5[3] > 0){
tsChoices5.push(item5.createChoice(tsChoicesString53));
item5.setChoices(tsChoices5);
} //END OF IF 5.4

if(tsSeatsRemaining5[4] > 0){
tsChoices5.push(item5.createChoice(tsChoicesString54));
item5.setChoices(tsChoices5);
} //END OF IF 5.5

//----------------------------------------------------------------------------------------------------------------------
// Block X
// Create List Item 2 and Populate
// List item is created and if there are remaining seats, populate the choices.
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

//TimeSlot 6 - Google Sheets Training
var item6 = form.addListItem()
item6.setTitle(tsHeader6).setChoices([item6.createChoice(tsChoicesString65)]);       
tsChoices6 = item6.getChoices()

if(tsSeatsRemaining6[0] > 0){
tsChoices6.push(item6.createChoice(tsChoicesString60));
item6.setChoices(tsChoices6);
} //END OF IF 6.1

if(tsSeatsRemaining6[1] > 0){
tsChoices6.push(item6.createChoice(tsChoicesString61));
item6.setChoices(tsChoices6);
} //END OF IF 6.2

if(tsSeatsRemaining6[2] > 0){
tsChoices6.push(item6.createChoice(tsChoicesString62));
item6.setChoices(tsChoices6);
} //END OF IF 6.3

if(tsSeatsRemaining6[3] > 0){
tsChoices6.push(item6.createChoice(tsChoicesString63));
item6.setChoices(tsChoices6);
} //END OF IF 6.4

if(tsSeatsRemaining6[4] > 0){
tsChoices6.push(item6.createChoice(tsChoicesString64));
item6.setChoices(tsChoices6);
} //END OF IF 6.5

AmmendResponses();
}


function AmmendResponses () {
var ss = SpreadsheetApp.openById('FORM-SPREADSHEET ID');
var sheet2 = ss.getSheetByName('Form Responses 1');
var form = FormApp.getActiveForm()
var oldReponses = sheet2.getRange("H:H").getValues();
var oldReponsesLastRow = oldReponses.filter(String).length;
var target = oldReponsesLastRow +1
var lastOldResponse = sheet2.getRange(target, 8, 1, 6)



sheet2.getRange("B2:G").moveTo(lastOldResponse)
sheet2.deleteColumns(2, 6);
sheet2.sort(1);
form.deleteAllResponses()


}