2

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
  • 85
  • 1
  • 10

1 Answers1

6

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 }]
     }
soMarios
  • 24,472
  • 8
  • 28
  • 43
  • 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. – soMarios 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 . – soMarios 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.) – soMarios 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
  • If we use mimeType: MimeType.CSV then the file is recognisable by Google Drive as well. – aareeph Apr 01 '22 at 07:38