I am using Google spreadsheets, and in my google spreadsheet i want to import data from my RESTful web service.
I cant use =IMPORTDATA due to the fact that i need to add a header to the url for auth purposes.
This is the custom function i have so far:
function fetchFromUrl() {
var url = "https://disclosedurl";
var response = UrlFetchApp.fetch(url, {
"headers": {
"Authorization": "OAUTH token",
}
});
var result = response.getContentText();
var csvData = CSVToArray(result, ",");
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
for (var i = 0; i < csvData.length; i++) {
sheet.getRange(i+9, 1, 1, csvData[i].length).setValues(new Array(csvData[i]));
}
}
When i call this function from a cell in the spreadsheet i get this error.
Error: You do not have permission to call setValues (line 56).
I've seen this issue pop up a lot but i haven't been able to fix it. Is there a better way to accomplish this or am i missing something in my custom function that grants permissions?
I guess this way cant work, based on this article, others are facing the same issue. I went with the image workaround that assigns the script to an image and gets called when you press the image.
Link for reference: http://jonrh.is/oddities-in-scripting-for-google-docs-spreadsheet/