Google Apps Script how to export specific sheet as csv format

Question

In MIT Inventor II, I use web component to get SpreadsheetID and SheetID through doGet() of google apps script. After I get the information I use another web component to set url as below to get csv-formatted file from specific sheet. My question is how to make GAS to get SpreadsheetID & SheetID and then export csv file at one time, so that I don't have to use 2 web components in Inventor side?

GAS codes is as below. This is to "return" spreadsheetID and sheetID.

function doGet(e) {

filename = e.parameter.account;

fileList = DriveApp.getFilesByName(filename);

while (fileList.hasNext()) {
var fileID = fileList.next().getId()
    }

var file = SpreadsheetApp.openById(fileID) ;

   sheet = file.getSheetByName("Message").activate()

  var messageID = sheet.getSheetId();  

return ContentService.createTextOutput([fileID,messageID]);

After I got SpreadsheetID & SheetID, I have to set 2nd web component from Inventor side to get csv file, see below.

https://docs.google.com/spreadsheets/d/xxxxSpreadsheetIDxxxx/edit#gid=sheetID

Hondo

Can you show your doGet()? – Petromilli

Answer 1

Here is how you can create a csv file of a selected sheet in google drive:

function sheetToCsv()
{
    var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();
    var sheet_Name = "Sheet1"
  
  
    var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};
  
      var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet_Name)
      var sheetNameId = sheet.getSheetId().toString();
  
      params= ssID+"/export?gid="+sheetNameId +"&format=csv"
      var url = "https://docs.google.com/spreadsheets/d/"+ params
      var result = UrlFetchApp.fetch(url, requestData);  
  
   var resource = {
  title: sheet_Name+".csv",
  mimeType: "application/vnd.csv"
     }
   var fileJson = Drive.Files.insert(resource,result)
  
} 

The code creates a csv file that has the content of Sheet1.

In order to run the aforementioned function you need to activate the Advanced Drive Service.

Explanation:

Go to Resources => Advanced Google Services => activate Drive API

Another option is to create the csv file to a particular folder, then you need to replace the resource part of the code with this:

var folder_id ='id';
       
   var resource = {
  title: sheet_Name+".csv",
  mimeType: "application/vnd.csv",
  parents: [{ id: folder_id }]
     }

Forsooth

Where does the file goes? I could not find "Sheet1.csv" file in my drive either Google Drive or Drive in my notebook. – Hondo

It is created in your drive (My Drive), with the name Sheet1.csv. However, see also my updated answer in which I added code to create a csv file to a particular folder. Check again if you have enabled Drive from resources. – Forsooth

Could not find the "Sheet1.csv" in MyDrive. Not sure if it's correct to past your codes after my codes, I also tried to use your codes only.. Can it be save to my Inventor by using like this, "return ContentService.createTextOutput([fileID,messageID]);" – Hondo

See again my updated answer, you can choose the folder you want , to paste the data . – Forsooth

I've input folder ID, but still nothing.. var folder_id ="1gCPxxxxxxxxxxxxxxCirhmbPzq"; var resource = { title: sheet_Name+".csv", mimeType: "application/vnd.csv" } var fileJson = Drive.Files.insert(resource,result); – Hondo

share the error you are getting or any messages (i.a.) – Forsooth

Works now. Found 2 things, 1) when paste your code, it miss some semicolons ";" , 2) My original GAS was added from " + New" of MyDrive, not Tools --> Script editor... – Hondo

If we use mimeType: MimeType.CSV then the file is recognisable by Google Drive as well. – Sublunary

MimeType.CSV is a good tip! – Donnell

Answer 2

My application was how to save a tab of a Google sheets spreadsheet to a CSV file in a shared drive. Doing it to the default "My Drive" was relatively easy based on Marios' answer in this post, but I struggled with this for a shared drive while until I came across ziganotschka's example which solved my problem.

Code for my simple App Script is:

function sheetToCsv()
{
var ssID = SpreadsheetApp.getActiveSpreadsheet().getId();
var sheet_Name = "[Your sheet name goes here]";

var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheet_Name);
var sheetNameId = sheet.getSheetId().toString();

params= ssID+"/export?gid="+sheetNameId +"&format=csv";
var url = "https://docs.google.com/spreadsheets/d/"+ params;
var result = UrlFetchApp.fetch(url, requestData);  

var resource = {
  title: sheet_Name+".csv",
  mimeType: "MimeType.CSV",
  parents:[{
  "id": "[Your Shared Drive Folder ID goes here]"
      }]
    }

var optionalArgs={supportsAllDrives: true};

var fileJson = Drive.Files.insert(resource, result, optionalArgs);

} 

I added a timestamp to the file name and a trigger to cause the script to execute daily via a timer.

Educatory

This seems to result in error: ReferenceError: ScriptApp is not defined – Polytrophic