Export Form responses as csv Google Apps Scripts

Question

Is there is a fast way to programmatically export all responses from a Google Form to a csv? Something like "Export responses to csv" invoked via Scripts.

Right now I'm doing it in a rock art way:

• Iterate over the forms I want to export (~75)
  • Open each form var form = FormApp.openById(formId);
  • Get responses: var formReponses = form.getResponses(); (from 0 to 700 responses each form)
  • Iterate over responses and get item responses: var preguntes = formReponses[r].getItemResponses();
    • For each itemResponse, convert it to csv/json
  • Export responses to a drive file

This is extremly slow and additionally it hangs over and over, so I had to export responses in chunks of 50 responses and save them in Drive separated files. On next execution (after letting servers to cool down for a while), I'm executing the script again, skipping the number of responses found on the chunk file.

Additionally I'm not sure that Google keeps the responses order when doing form.getResponses(); (actually I've found that if the form has been modified, the order is not the same)

Is there a better way to do it?

Answer 1

Whith the help of @JackBrown I've managed to write a Chrome extension to download responses (maybe soon in github). This will wait for each download in the formIds object until finished and then prompt for the next one:

'use strict';

function startDownload() {
    const formIds = {
        'Downloads-subfolder-here': {
            'Download-filename-here': '1-cx-aSAMrTK0IHsQkE... {form-id here}',
            'Another-filename-here': '...-dnqdpnEso {form-id here}',
            // ...
        },
        'Another-subfolder-here': {
            'Download-filename-here': '1-cx-aSAMrTK0IHsQkE... {form-id here}',
            'Another-filename-here': '...-dnqdpnEso {form-id here}',
            // ...
        },
    };

    const destFolders = Object.keys(formIds);
    const downloads = [];

    for (let t = 0, tl = destFolders.length; t < tl; t += 1) {
        const destFolder = destFolders[t];
        const forms = Object.keys(formIds[destFolder]);

        for (let f = 0, fl = forms.length; f < fl; f += 1) {
            const formName = forms[f];
            downloads.push({
                destFolder,
                formName,
                url: `https://docs.google.com/forms/d/${formIds[destFolder][formName]}/downloadresponses?tz_offset=-18000000`,
                filename: `myfolder/${destFolder}/${formName.replace(/\//g, '_')}.csv`,
            });
        }
    }

    const event = new Event('finishedDownload');
    const eventInterrupt = new Event('interruptedDownload');
    let currId;

    chrome.downloads.onChanged.addListener((downloadDelta) => {
        if (downloadDelta.id === currId) {
            if (downloadDelta.state && downloadDelta.state.current === 'complete') {
                document.dispatchEvent(event);
            } else if (downloadDelta.state && downloadDelta.state.current === 'interrupted') {
                console.log(downloadDelta);
                document.dispatchEvent(eventInterrupt);
            }
        }
    });

    downloads.reduce((promise, actual) => {
        return promise.then((last) => (last ? new Promise((resolve) => {
            const { url, filename, destFolder, formName } = actual;
            function listener() {
                document.removeEventListener('finishedDownload', listener);
                document.removeEventListener('interruptedDownload', listener);
                resolve(true);
            };
            function interrupt() {
                document.removeEventListener('finishedDownload', listener);
                document.removeEventListener('interruptedDownload', listener);
                resolve(false);
            }
            console.log(`Processant ${destFolder}, ${formName}: ${url}`);
            document.addEventListener('finishedDownload', listener);
            document.addEventListener('interruptedDownload', interrupt);
            chrome.downloads.download({ url, filename }, (downloadId) => {
                currId = downloadId;
                if (!downloadId) {
                    console.log();
                    console.log('Error downloading...');
                    console.log(runtime.lastError);
                    resolve();
                }
            });
        }) : Promise.resolve(false)));
    }, Promise.resolve(true));
}

chrome.browserAction.onClicked.addListener((/*tab*/) => startDownload());