Automatize downloading from spreadsheet in external Google Drive

Question

I have a Google spreadsheet that placed in external Google Drive and shared to me by a link. I can use it in view only mode. This spreadsheet contains a lot of lists in it (like usual Excel).

I want to download each list from this spreadsheet as separate csv file, I can use it manually by clicking File -> Download -> Comma Separated Values in each single list.

Now I want to automatize this process, but still didn't figure out how to do it.

I thought about Selenium, but Google doesn't support it. Google API also doesn't help, because it is not my Drive.

Do you have any ideas about it?

Thank you!

Answer 1

You can use Google App Script to save a sheet to a CSV in your G-Drive. You can loop through all the sheets in a particular spreadsheet and save all of them as CSV.

Steps:

1. Create a new Spreadsheet in your Google Account (doesn't matter if its new or existing).

2. In the Ribbon, go to Extensions and click on "App Script"

3. A New window for App Script will be opened with a blank code.gs file and a blank myFunction function

4. Replace function with below code

5. Update the ssID and folder_id rom the code to your respective spreadsheet and folder ID's

6. On the left sidebar in App Script Editor, click on the + button beside Services. Then choose Drive API and click Add

7. In the ribbon at the top of App Script editor - choose the sheetToCsv function in the drop-down beside Debug and click Run

8. All the sheets will be saved as CSV files in the folder in your G-drive.

   

Code

Below is the code (Tried and Tested with a view-only sheet) :

function sheetToCsv()
{
    var ssID = "SpreadsheetID" // SpreadsheetApp.getActiveSpreadsheet().getId();
    var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};
    var sprdSheet = SpreadsheetApp.openById(ssID)
    var allSheets = sprdSheet.getSheets();

    for(var s in allSheets){
    
      var sheet = allSheets[s];
      var sheet_Name = sheet.getName()
      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 folder_id ='ID'; // replace this with your respective folder ID
        
      var resource = {
      title: sheet_Name+".csv",
      mimeType: "application/vnd.csv",
      parents: [{ id: folder_id }]
        }

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

Note:

1. you need to update the ssID and folder_id to your respective spreadsheet and folder ID's
2. You need to enable the Drive API Advanced Service.

TIPS:

1. Using Google App Script, you can also use triggers. This allows you to set a frequency to run this code automatically (if required). So you don't even have to manually download the CSV's.
2. Additionally, you can also create a Sidebar UI with App Script (using some HTML) so that you can use the same code to download any spreadsheet with the click of a button instead of using the App Script Editor every time - Official documentation
3. or instead of a Sidebar UI, you can also use the onOpen function to create a custom button in your Ribbon Menu to run any App Script function from within the Ribbon - like mike mentioned in another answer. And additionally, you can use prompt dialogs to allow the code to download any sheet.

Answer 2

Try this https://docs.google.com/spreadsheets/d/1MH-OPfnE2pjgaXmLGFtOJRcMLUnFdJD1CrQRz656IVo/copy

The function will return csv files names and ids.

function onOpen() {
  SpreadsheetApp.getUi().createMenu('🌟 M E N U 🌟')
    .addItem('👉 Download', 'downloadAsCsv')
    .addToUi();
}
// enable drive API
function downloadAsCsv() {
  var s = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var url = s.getRange('B1').getValue()
  var id = url.split('/')[5]
  Logger.log(id)
  var ss = SpreadsheetApp.openById(id)
  var list = []
  ss.getSheets().forEach(function(sh){
    var csvId = sheetToCsv(id,sh.getName(),sh.getSheetId())
    list.push([sh.getName()+'.csv',csvId])
  })
  s.getRange(2,1,list.length,list[0].length).setValues(list)
}
function sheetToCsv(ssID,sheet_Name,sheetNameId){
  var requestData = {"method": "GET", "headers":{"Authorization":"Bearer "+ScriptApp.getOAuthToken()}};  
  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)
  return fileJson.id
}

enable the Drive API

Answer 3

Google Drive and shared to me by a link

If you only have view access via a link to this file. Then there is no way you can use the Google drive api or any google api. You will need to have proper permissions to the file to access it.

I know of now way that you will be able to rip data from a google drive file that you can only view via the google drive web application. You may be able to do a web scrap but scraping is against googles TOS so I will strongly suggest you do not attempt this as it may result in your account being banned.

I will suggest that you continue reading and get proper permissions on the file.

with proper access

First off sharing the file with yourself as a link isnt going to help here. In order to use the google drive api you need to have permissions on the file. Take your personal email address and share the file with yourself if it isnt already.

If you are intention is to use the Google-drive api then what you can do is a file.export and pass the mime type of csv this will cause your google sheets file to be downloaded as a csv. Google drive downloading large files with C#

var request = service.Files.Export(fileId, "text/csv");

The format of this file is a little unclear to me. Another option would be to use the Google sheets api. This would give you programmatic access to the data within the sheet it self. again you would need to have permissions on the file.

One thing to note here is it sounds like you are accessing a single file which means you should consider using a service account for this. This will make authorization easer but you will still need to share the file with the service account so that it has access. Should you be using a service account.