---
# System prepended metadata

title: Google App Script interact with Google Form and Google Sheet

---

# Google App Script interact with Google Form and Google Sheet

### Step1 Create a Google Form
### Step2-1 Binding to Google Sheet
![](https://hackmd.io/_uploads/rklkJy3kT.png)
### Step2-2 Change Sheet Name to `sheet1`
![](https://hackmd.io/_uploads/ByWNyJ3y6.png)
![](https://hackmd.io/_uploads/SJg81khJ6.png)

### Step3 Create An App Script
select `Apps Script`
![](https://hackmd.io/_uploads/HJkyx1nJ6.png)

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


1. rename the default `.gs`
![](https://hackmd.io/_uploads/Bkfh-121p.png)

3. replace `formId`
you can find formId in `step1 url`
![](https://hackmd.io/_uploads/rkXfQJn1a.png)


```javascript
//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`
![](https://hackmd.io/_uploads/BkwCWknJa.png)

4. replace `csvContent`
```javascript
//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
![](https://hackmd.io/_uploads/HyoVl13Ja.png)
![](https://hackmd.io/_uploads/Skjogy3Jp.png)
![](https://hackmd.io/_uploads/r1RAeyh1p.png)


## 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