I've been struggling to make a script that takes all the responses directly from a Google Form and sent it (or copy it) to a spreadsheet. I also want to send the spreadsheet to Google Drive after the copy is created (I already manage to do this part)
When I run the script it doesn't show any errors. The spreadsheet is created and stored in the given drive folder but the sheet is empty.
The is the script:
function getItemResponses() {
// Open a form by ID and create a new spreadsheet.
var form = FormApp.openById('1ohh3SYGm0F1wzIGeQenk6Vt-epeXY2UHgdYXtH5knWA');
//Prepare new name
var formattedDate = Utilities.formatDate(new Date(), "GMT", "dd-MM-yyyy' 'HH:mm:ss");
var name = form.getTitle() " Copy " formattedDate;
//Prepare file system objects
var oldfile = DriveApp.getFileById(form.getId());
destination = DriveApp.getFolderById("1u6HQQUWXKIkpakInbS9u6opWK10T91Ez");
//create new spreadsheet
var newSpreadsheet = SpreadsheetApp.create(name);
//Copy to newSpreadSheet
var form = FormApp.openById('1ohh3SYGm0F1wzIGeQenk6Vt-epeXY2UHgdYXtH5knWA'),getItemResponses,newSpreadsheet
//move to destination folder
var newFile = DriveApp.getFileById(newSpreadsheet.getId());
var parents = newFile.getParents();
while (parents.hasNext()) {
var parent = parents.next();
parent.removeFile(newFile); //remove from default folder
}
destination.addFile(newFile);
}
Thanks for your help in advance.
CodePudding user response：
I believe your goal is as follows.
- You want to retrieve all response values from the existing Google Form.
- You want to put the retrieved values on the new Spreadsheet.
- You want to put the new Spreadsheet in the specific folder.
Modification points:
- In your script, the new Spreadsheet is created. But the response values are not put. I thought that this might be the reason for your issue.
- In order to retrieve all response values from Google Form, I thought that this sample script might be able to be used. Ref
- In order to move the created Spreadsheet to the specific folder, in this case, you can use
moveTo.
When the above points are reflected in your script, it becomes as follows.
Modified script:
function getItemResponses() {
var form = FormApp.openById('1ohh3SYGm0F1wzIGeQenk6Vt-epeXY2UHgdYXtH5knWA');
var formattedDate = Utilities.formatDate(new Date(), "GMT", "dd-MM-yyyy' 'HH:mm:ss");
var name = form.getTitle() " Copy " formattedDate;
destination = DriveApp.getFolderById("1u6HQQUWXKIkpakInbS9u6opWK10T91Ez");
// Create new Spreadsheet.
var newSpreadsheet = SpreadsheetApp.create(name);
// Move the created Spreadsheet to the specific folder.
var newFile = DriveApp.getFileById(newSpreadsheet.getId());
newFile.moveTo(destination);
// Retrieve all response values from Google Form and put them to the created Spreadsheet.
var sheet = newSpreadsheet.getSheets()[0];
var titles = form.getItems().map(e => e.getTitle());
var formResponses = form.getResponses();
var values = formResponses.map(f => f.getItemResponses().reduce((o, i) => {
var r = i.getResponse();
return Object.assign(o, {[i.getItem().getTitle()]: Array.isArray(r) ? r.join(",") : r});
}, {})).map(o => titles.map(t => o[t] || ""));
values.unshift(titles);
sheet.getRange(sheet.getLastRow() 1, 1, values.length, values[0].length).setValues(values);
}
- When this script is run, a new Spreadsheet is created and it is moved to the specific folder, and all response values are retrieved from Google Form and they are put to the created Spreadsheet.