Google Apps Script to import a CSV, stored securely on S3, to a Google Spreadsheet

importS3Csv.gs

var AWS_KEY = '<your key>';
var AWS_SECRET = '<your secret>';

function generateS3Url(bucket, path) {  
  var expiresDt = Math.floor(Date.now() / 1000) + (60 * 60 * 24); // can be up to 7 days from now
  
  var stringToSign = 'GET\n\n\n' + expiresDt + '\n/' + bucket + '/' + encodeURIComponent(path);
  
  var hmac = Utilities.computeHmacSignature(Utilities.MacAlgorithm.HMAC_SHA_1, stringToSign, AWS_SECRET, Utilities.Charset.UTF_8);
  var signed = encodeURIComponent(Utilities.base64Encode(hmac));
  
  return 'https://' + bucket + '.s3.amazonaws.com/' + path + '?AWSAccessKeyId=' + AWS_KEY + '&Expires=' + expiresDt + '&Signature=' + signed;
}

// Use "Resources" > "Current project's triggers" to run this on a schedule
function updateSheet() {
  var csvUrl = generateS3Url('my-bucket', 'my-file.csv');
  
  SpreadsheetApp.openById('<spreadsheet id>').getSheets()[0].getRange('A1').setFormula('=IMPORTDATA("' + csvUrl + '")');
}

Sounds like AWS will no longer accept HMAC-SHA1 but require HMAC-SHA256 for URL signing.

@katylava @swoodford @akhileshmosale I've successfully created function to generate these URLs! You can find it here: https://github.com/kmotrebski/gas-s3-url-generator

It has same functionality as the one here in this gist but it works for current way of signing URLs at S3.

Under the hood it's quite complex compared to this one but AFAIK the signing algorithm has changed. Currently supported version is V4. V2 is no longer supported for S3 - I hope so as I've spent some time to create this function :) My code directly follows exemplary case from AWS documentation here. All variable names are identical.

I hope it is some help!