8

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
LookForward Chuang
  • 145
  • 1
  • 3
  • 12

2 Answers2

15

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 }]
     }
Marios
  • 26,333
  • 8
  • 32
  • 52
  • Where does the file goes? I could not find "Sheet1.csv" file in my drive either Google Drive or Drive in my notebook. – LookForward Chuang Aug 06 '20 at 13:07
  • 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. – Marios Aug 06 '20 at 13:41
  • 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]);" – LookForward Chuang Aug 06 '20 at 14:30
  • See again my updated answer, you can choose the folder you want , to paste the data . – Marios Aug 06 '20 at 14:31
  • 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); – LookForward Chuang Aug 06 '20 at 15:01
  • share the error you are getting or any messages (i.a.) – Marios Aug 06 '20 at 15:02
  • 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... – LookForward Chuang Aug 07 '20 at 12:56
  • 3
    If we use mimeType: MimeType.CSV then the file is recognisable by Google Drive as well. – aareeph Apr 01 '22 at 07:38
  • MimeType.CSV is a good tip! – Subaru Spirit Nov 10 '22 at 11:43
0

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.

bchatham
  • 139
  • 1
  • 4