I'm trying to build an apps script that will take a whole bunch of forms and consolidate their answers onto one spreadsheet. I don't need all of the answers, just the ones to Yes/No questions, and it doesn't really matter what format those yes/no answers come into the spreadsheet as as long as I know which is for yes and which is for no.
I've written the below, with the first half collecting the answers, and then the second half is my attempt to get those answers into the spreadsheet. As far as I can tell, I'm not sure if I don't understand how the Forms work or if I don't understand how the sheet is working - any guidance would be much appreciated!
const auditReportConsol = () => {
const folder = DriveApp.getFolderById('1Y2QgLbHncLzPDe-UCD6WizSNnHkjkt4z');
const forms = folder.getFilesByType(MimeType.GOOGLE_FORMS);
while (forms.hasNext()) {
const file = forms.next();
const form = FormApp.openById(file.getId());
var formResponses = form.getResponses();
for (var i = 0; i < formResponses.length; i ) {
var formResponse = formResponses[i];
var itemResponses = formResponse.getItemResponses();
switch (itemResponses[i].getItem().getIndex()) {
case 7:
var gov1 = itemResponses[i].getResponse();
break;
case 9:
var gov2 = itemResponses[i].getResponse();
break;
case 11:
var gov3 = itemResponses[i].getResponse();
break;
case 13:
var gov4 = itemResponses[i].getResponse();
break;
case 15:
var risk1 = itemResponses[i].getResponse();
break;
case 17:
var risk2 = itemResponses[i].getResponse();
break;
case 19:
var risk3 = itemResponses[i].getResponse();
break;
case 21:
var risk4 = itemResponses[i].getResponse();
break;
case 23:
var risk5 = itemResponses[i].getResponse();
break;
case 25:
var risk6 = itemResponses[i].getResponse();
break;
case 27:
var breach1 = itemResponses[i].getResponse();
break;
case 29:
var breach2 = itemResponses[i].getResponse();
break;
case 31:
var breach3 = itemResponses[i].getResponse();
break;
case 33:
var asset1 = itemResponses[i].getResponse();
break;
case 35:
var asset2 = itemResponses[i].getResponse();
break;
case 37:
var asset3 = itemResponses[i].getResponse();
break;
case 39:
var asset4 = itemResponses[i].getResponse();
break;
case 41:
var asset5 = itemResponses[i].getResponse();
break;
case 43:
var dsr1 = itemResponses[i].getResponse();
break;
case 45:
var dsr2 = itemResponses[i].getResponse();
break;
case 47:
var dsr3 = itemResponses[i].getResponse();
break;
case 49:
var dsr4 = itemResponses[i].getResponse();
break;
case 51:
var dsr5 = itemResponses[i].getResponse();
break;
case 53:
var dsr6 = itemResponses[i].getResponse();
break;
case 55:
var dsr7 = itemResponses[i].getResponse();
break;
case 57:
var dsr8 = itemResponses[i].getResponse();
break;
case 59:
var dsr9 = itemResponses[i].getResponse();
break;
case 61:
var access1 = itemResponses[i].getResponse();
break;
case 63:
var access2 = itemResponses[i].getResponse();
break;
case 65:
var access3 = itemResponses[i].getResponse();
break;
case 67:
var change1 = itemResponses[i].getResponse();
break;
case 69:
var change2 = itemResponses[i].getResponse();
break;
}
Logger.log;
Logger.getLog;
}
}
var ss = SpreadsheetApp.openById('1Qlv4v5dU6caBnRfa4Z58OBnoT0g3uFEmKewTENsBOLo');
var sheet = ss.getActiveSheet();
var newForm = sheet.appendRow([gov1],[gov2],[gov3]);
}
CodePudding user response：
You can simply list required fields, and later push only them in final result row to display, like this
const auditReportConsol = () => {
const folder = DriveApp.getFolderById('1Y2QgLbHncLzPDe-UCD6WizSNnHkjkt4z');
const forms = folder.getFilesByType(MimeType.GOOGLE_FORMS);
const FIELD_LIST = [7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39,41,43,45,47,49,51,53,55,57,59,61,63,65,67,69];
const rows = [];
while (forms.hasNext()) {
const file = forms.next();
const form = FormApp.openById(file.getId());
var formResponses = form.getResponses();
for (var i = 0; i < formResponses.length; i ) {
var formResponse = formResponses[i];
var itemResponses = formResponse.getItemResponses();
var row = [];
// Pick required items
itemResponses.map( (index, item) => FIELD_LIST.indexOf(index) >=0 && row.push(item.getResponse()))
}
}
// Save rows array into sheet
var ss = SpreadsheetApp.openById('1Qlv4v5dU6caBnRfa4Z58OBnoT0g3uFEmKewTENsBOLo');
var sheet = ss.getActiveSheet();
sheet.clear();
sheet.getRange(1,1, rows.lengths, rows[0].length)
}
I might also recommend prepending form Id/Name as first column to distinguish different forms:
row.unshift(form.getTitle())
CodePudding user response：
To set this up using the linked Google Sheets from Google forms you would need to do the following
Open each Google Form and on the responses tab, select the Create Spreadsheet button.
This will then create a new Google Sheet in the same directory in which the Google Form is saved. This sheet will have a tab called Form responses 1
Then create a seperate Google sheet and use the query and Importrange function to import the data from the form response.
=query({importrange("GoogleSheetsID","Form responses1!A1:Z")}, "Select * WHERE Col1 is not null",1)
Where GoogleSheetsID is replaced with your own Google Sheet ID
You will need to give the sheet permission to access the linked file
The select statement at the end can be changed to only select the columns you want. So if you wanted columns 2,8 and 1 in that order it would be
=query({importrange("GoogleSheetsID","Form responses 1!A1:Z")}, "Select Col2, Col8, Col1 WHERE Col1 is not null",1)
If you then want to look at multiple sheets you can then use the below formula
=QUERY({importrange("GoogleSheetsID1","Form responses1!A1:Z");importrange("GoogleSheetsID2","Form responses1!A2:Z")}, "Select Col2, Col8, Col1 WHERE Col1 is not null",1)
To add more elements into this, you just replicate the
importrange("GoogleSheetsID2","Form responses1!A1:Z")
element within the {} brackets
CodePudding user response：
In a form, you can disconnect from the current worksheet and connect to another worksheet. This way you can collect the results of several forms in one spreadsheet!
Then you can collect the information you need through the onFormSubmit triggered function and put the required informations together in one master sheet.
You can identify the origin of information by this way
function getFormUrl() {
SpreadsheetApp.getActiveSpreadsheet().getSheets().forEach(sheet => console.log (sheet.getFormUrl()));
}