Re: How To Download a Google Sheet .csv through SAS

cgates · Posted 03-30-2021 09:16 PM

I'm using SAS EG, 7.15 HF9 (7.100.5.6226) (64-bit) powered by SAS 9.4. If I paste this link into my browser, a .csv file is automatically downloaded to my PC: https://docs.google.com/spreadsheets/d/1xFgCLi7cr-_Z_hbhksYIv5jABk6EaBcPNWRlVzH5kxY/export?format=cs.... This is a test document but the real data I need to download through SAS has sensitive data so I can't share that here.

The code below works with the test document because there are no restrictions, but when I run the same code with the document that requires me to be signed in, I get HTML output instead of the csv data (copied below).

Is there a way I can authenticate my Google credentials through SAS?

filename in url "https://docs.google.com/spreadsheets/d/1xFgCLi7cr-_Z_hbhksYIv5jABk6EaBcPNWRlVzH5kxY/export?format=csv";
filename out "%sysfunc(pathname(work))\data.csv"; 
 
data _null_;                                                                                                                                                      
  infile in;                                                                                                                                                      
  input;                                                                                                                                                          
  if _n_> 1;                                                                                                                                                     
  file out;                                                                                                                                                       
  put _infile_;                                                                                                                                                   
run;                                                                                                                                                              
                                                                                                                                                                  
PROC IMPORT OUT= WORK.data                                                                                                                                        
     DATAFILE= "%sysfunc(pathname(work))\data.csv"                                                                                                 
     DBMS=CSV REPLACE;                                                                                                                                            
RUN;

.csv snippet from SAS in HTML

<!DOCTYPE html>

<head>

<title>Google Sheets - create and edit spreadsheets online

<style>

@font-face {

font-family: 'Open Sans';

font-style: normal;

font-weight: 300;

src: url(//fonts.gstatic.com/s/opensans/v15/mem5YaGs126MiZpBA-UN_r8OUuhs.ttf) format('truetype');

}

japelin · Posted 03-30-2021 09:55 PM

I don't think this is a SAS problem.

To test, try accessing the spreadsheet link in another browser without cache or in private mode.
If it doesn't download as a CSV, you will first need to change the sharing range to "Everyone who knows the link" in Google Spreadsheets.
Then you should be able to download it as a csv.

cgates · Posted 03-30-2021 10:10 PM

You're right. The link I provided was just an example but it does work if I remove restrictions. The issue is the production link I need this for does need to be protected. Do you know of a way I can authenticate my Google credentials through SAS? I thought it would work because I was running the SAS program while logged into my Gmail but that doesn't seem to work.

japelin · Posted 03-31-2021 12:04 AM

I thought I could access it with proc http and basic authentication if I had the app password, but it seems that OAuth is required to access it.

The following links may be helpful.

https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2019/3232-2019.pdf

https://support.sas.com/resources/papers/proceedings17/SAS0224-2017.pdf

PhilC · Posted 03-31-2021 08:56 AM

The content sharing access permissions for Google Docs can be set to not require authentication. My memory, you can set (paraphrasing) "Share to whom ever has the link". I don't use it often, and I have to stumble through their security access menus re-re-learning it every time. Something to consider, HTH.

How To Download a Protected Google Sheet .csv through SAS