I'm having some difficulty with the following formula in Google Scripts. What I would like to achieve, is:
1- get Number from A1
2- from Number, have multiple returns
3- set those returns in the same row (A) as original Number, in neighboring cells (A2, A3)
What it looks like now:
function GETALLTWO(bid) {
if (input.map) {
return input.map(GETALLTWO)
} else {
return input;
}
var api_str = "client_id=randomstringofnumbers&client_secret=anotherrandomstringofnumbers";
var url= "https://api.untappd.com/v4/beer/info/" + bid + "?" + api_str;
var response = UrlFetchApp.fetch(url);
if (response.getResponseCode() == 200) {
var respObj = JSON.parse(response.getContentText());
var beer = respObj.response.beer;
if (beer.bid == bid) {
return beer.beer_name;
return beer.rating_score;
return beer.stats.total_user_count;
return beer.rating_count;
return beer.stats.monthly_count;
return beer.created_at;
}
}
}
From Code.gs, I can get the first Return to work, but am I combining the different functions correctly? And how would I set the location to return?
Hope someone can help, would love to learn more about scripting.
// Edit for future reference:
function getAll(bid) {
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName('Overview')
var row=sh.getActiveCell().getRow();
var bid=sh.getRange(row,3).getValue();
var api_str = "client_id=randomstring&client_secret=randomstring";
var url= "https://api.untappd.com/v4/beer/info/" + bid + "?" + api_str;
var response = UrlFetchApp.fetch(url);
if (response.getResponseCode() == 200) {
var respObj = JSON.parse(response.getContentText());
var beer = respObj.response.beer;
if (beer.bid == bid) {
var rObj= {name:beer.beer_name,rating:beer.rating_score,totalcount:beer.stats.total_user_count,ratingcount:beer.rating_count,monthlycount:beer.stats.monthly_count,createddate:beer.created_at};
values=[[rObj.name,rObj.ratingcount,rObj.rating,rObj.totalcount,rObj.monthlycount,rObj.createddate]];
return values;
}
}
}
With this function you just need to put your cursor on the row that you want to get the bid from. It will put the data back in that row starting at column D
function getAllTwo() {
var ss=SpreadsheetApp.getActive();
var sh=ss.getSheetByName('Your Sheet Name')
var row=sh.getActiveCell().getRow();
var bid=sh.getRange(row,3).getValue();
var api_str = "client_id=randomstringofnumbers&client_secret=anotherrandomstringofnumbers";
var url= "https://api.untappd.com/v4/beer/info/" + bid + "?" + api_str;
var response = UrlFetchApp.fetch(url);
if (response.getResponseCode() == 200) {
var respObj = JSON.parse(response.getContentText());
var beer = respObj.response.beer;
if (beer.bid == bid) {
var rObj= {name:beer.beer_name,rating:beer.rating_score,totalcount:beer.stats.total_user_count,ratingcount:beer.rating_count,monthlycount:beer.stats.monthly_count,createdate:beer.created_at};
values=[[rObj.name,rObj.ratingcount,rObj.rating,rObj.totalcount,rObj.monthlycount,rObj.createddate]];
sh.getRange(row,4,values.length,values[0].length).setValues(values);
}
}
}