# Google App Script interact with Google Form and Google Sheet

### Step1 Create a Google Form
### Step2-1 Binding to Google Sheet
### Step2-2 Change Sheet Name to `sheet1`

### Step3 Create An App Script
select `Apps Script`

(you can find that project in https://script.google.com/home)

1. rename the default `.gs`

3. replace `formId`
you can find formId in `step1 url`

//main.gs
function doGet() {
  var formId ='xxxxxx';
  var sheet = exportFormDataToCSV(formId);
  var splits = sheet.split('/');
  var fileId = splits[5];
  // console.log(splits);
  // console.log(fileId);
  var res = getCSVFile(fileId,'formData');
  // console.log(res);
  return res

}

2. Create a new .gs which name `csvHandler`

4. replace `csvContent`

//csvHandler.gs
function exportFormDataToCSV(id) {
  var form = FormApp.openById(id); 
  var responses = form.getResponses();
  
  var csvContent = "時間戳記,性別,下午茶\n";
  
  for (var i = 0; i < responses.length; i++) {
    var response = responses[i];
    var itemResponses = response.getItemResponses();
    
    var row = [response.getTimestamp()];
    
    for (var j = 0; j < itemResponses.length; j++) {
      var itemResponse = itemResponses[j];
      row.push(itemResponse.getResponse());
    }
    
    csvContent += row.join(",") + "\n";
  }
  
var folderName ="FormCSVExport";
var existingFolder = DriveApp.getFoldersByName(folderName);
if (existingFolder.hasNext()) {
  var folder = existingFolder.next();
  var files = folder.getFiles();
  while (files.hasNext()) {
    var file = files.next();
    file.setTrashed(true);
  }
  folder.setTrashed(true);
}
  var folder = DriveApp.createFolder(folderName)
  var file = folder.createFile('formData.csv', csvContent, MimeType.CSV);
  
  var fileUrl = file.getUrl();
  
  Logger.log("CSV document has been created:" + fileUrl);
  
  Browser.msgBox("CSV document has been created:" + fileUrl);
  return fileUrl;
}

function getCSVFile(fileId,fileName,outFileName='data') {
  try {
    var file = DriveApp.getFileById(fileId);
    var blob = file.getBlob();
    
    var headers = {
      "Content-Disposition": `attachment; filename=${fileName}.csv`
    };
    
    var output = ContentService.createTextOutput(blob.getDataAsString());
    output.setMimeType(ContentService.MimeType.CSV);
    output.downloadAsFile(`${outFileName}.csv`); 
    
    return output;
  } catch (e) {
    console.log('error',e)
    return ContentService.createTextOutput("Error: " + e.toString());
  }
}

### Step4 Deployed API

## Source
https://developers.google.com/drive/api/reference/rest/v2
https://developers.google.com/apps-script/advanced/drive
https://stackoverflow.com/questions/63265983/google-apps-script-how-to-export-specific-sheet-as-csv-format
chatGPT
https://gist.github.com/mrkrndvs/a2c8ff518b16e9188338cb809e06ccf1