I'm trying to make a trigger to fetch data based on cell value automatically. the custom formula that I create and use in a sheet (or cell) successfully fetches the data, for example :
=ListPlugins("https://example1.wpengine.com")
the function looks like this :
function ListPlugins(url) {
let sheet_name = 'apps and plugins'
var options = {
"async": true,
"crossDomain": true,
"method" : "GET",
"headers" : {
"Token" : TOKEN_HERE,
"cache-control": "no-cache"
}
};
var urlEncoded = encodeURI(url)
let api_url = urlEncoded "/wp-json/bd/v1/public/plugin-list/"
var jsondata = UrlFetchApp.fetch(api_url,options)
var object = JSON.parse(jsondata.getContentText())
return object;
}
but when I try to automate it by using the trigger, an error happens
Exception: You do not have permission to call ScriptApp.newTrigger. Required permissions: https://www.googleapis.com/auth/script.scriptapp
and this is how my approach to automating:
function onOpen(e) {
return teste()
}
function teste()
{
var all_data = getDataFromPluginList()
/*return all_data
[ { first_cell: 'A2',
last_cell: 'I9',
first_row: 2,
last_row: 9,
total_row: 8,
url: 'https://example1.wpengine.com/' },
{ first_cell: 'A10',
last_cell: 'I15',
first_row: 10,
last_row: 15,
total_row: 6,
url: 'https://example2.wpengine.com/' } ]
*/
for (var i = 0; i < all_data.length; i ) {
trigger_(all_data[i].url);
}
}
function trigger_(url)
{
var new_trigger = ScriptApp.newTrigger(writePluginData).timeBased().everyMinutes(1).create();
var trigger_id = new_trigger.getUniqueId();
PropertiesService.getUserProperties().setProperty(trigger_id, url);
}
function writePluginData(event)
{
var trig = ScriptApp.getProjectTriggers();
let sheet_name = 'trigger_result';
let lowest_row = lowestRow(sheet_name);
for(var i =0; i<trig.length; i )
{
if(trig[i].getUniqueId()== event.triggerUid )
{
var urlParam = PropertiesService.getUserProperties().getProperty(event.triggerUid);
urlParam = urlParam.split(",");
var url = urlParam[0];
var ss = SpreadsheetApp.getActive().getSheetByName(sheet_name); // change accordingly
var data = ListPlugins(URL);
/*return listPlugins
[
[
"Escape Room",
"https://example.com/",
"Advanced Custom Fields PRO",
"advanced-custom-fields-pro",
"5.12.3",
"6.0.7",
false,
6,
"2023-01-30 10:57 AM"
],
[
"",
"",
"Classic Editor",
"classic-editor",
"1.6.2",
"1.6.2",
true,
""
],
...,
]
*/
var target = "A" (lowest_row 1)
Logger.log(data);
ss.getRange(target).setValue(data);
}
}
}
why does this error happen? is there any relation to my API security?
CodePudding user response:
@tanaike first comment on my question is the answer I'm looking