Google Sheets Javascript Function Loop

I'm trying to make a Google Sheets JS function that will read the data in a certain cell, call an API in regards to that value, parse the data and return the result in the cell next to the original.

I can make this happen on a one by one basis, but I'm trying to make it loop through a set of arrays.

When I run the function it's ending with only the last index call results.

Am I going about this entirely wrong, or just missing something?

//Create button on Sheet in dropdown

function onOpen() {

    var ui = SpreadsheetApp.getUi();

    ui.createMenu('Overwatch API Menu')

        .addItem('Get Updated Stats','ovrstat')

        .addToUi();

};

var nameCell = ["A2","A3","A4","A5"];

var rate = ["B2","B3","B4","B5"];

var i;

for (i = 0; i < 4; i = i + 1){

    var rangeA = nameCell[i];

    var rangeB = rate[i];

    function ovrstat() {

        var ss = SpreadsheetApp.getActiveSpreadsheet();

        var sheet = ss.getActiveSheet();

        var name = sheet.getRange(rangeA).getValue();

         // Call the Ovrstat API

        var response = UrlFetchApp.fetch('https://ovrstat.com/stats/psn/'+name);

        var json = response.getContentText();

        var data = JSON.parse(json);

        Logger.log(data.rating);



        var sheet = SpreadsheetApp.getActiveSheet();

        sheet.getRange(rangeB).setValue([data.rating]);

    };

};

edited Nov 15 '18 at 23:33

TheMaster

10.5k3936

asked Nov 15 '18 at 19:30

Robert Duffany

112

You don't call the function ovrstat? I also wonder by you define a function inside a loop. Just have the code that is in that function, and drop the function wrapper.

– trincot
Nov 15 '18 at 19:38

I updated my code, I added a ui button in sheets to call the function

– Robert Duffany
Nov 15 '18 at 19:53

1

But it makes no sense to place function inside a loop, and then call the function from somewhere else. Did you expect the function to be executed in a loop? Because that is not going to happen... You should move the line with function ovrstat() { before the line var nameCell = (and adapt the indentation of your code accordingly).

– trincot
Nov 15 '18 at 19:56

I suggest you look at some JavaScript tutorials to learn the difference between a function definition and a function call.

– Code-Apprentice
Nov 15 '18 at 20:00

Thanks guys, Moving function ovrstat() { before the line var nameCell = worked. My first go at doing anything like this and I was just confused by what I had read I guess.

– Robert Duffany
Nov 15 '18 at 20:15

add a comment |

I'm trying to make a Google Sheets JS function that will read the data in a certain cell, call an API in regards to that value, parse the data and return the result in the cell next to the original.

I can make this happen on a one by one basis, but I'm trying to make it loop through a set of arrays.

When I run the function it's ending with only the last index call results.

Am I going about this entirely wrong, or just missing something?

//Create button on Sheet in dropdown

function onOpen() {

    var ui = SpreadsheetApp.getUi();

    ui.createMenu('Overwatch API Menu')

        .addItem('Get Updated Stats','ovrstat')

        .addToUi();

};

var nameCell = ["A2","A3","A4","A5"];

var rate = ["B2","B3","B4","B5"];

var i;

for (i = 0; i < 4; i = i + 1){

    var rangeA = nameCell[i];

    var rangeB = rate[i];

    function ovrstat() {

        var ss = SpreadsheetApp.getActiveSpreadsheet();

        var sheet = ss.getActiveSheet();

        var name = sheet.getRange(rangeA).getValue();

         // Call the Ovrstat API

        var response = UrlFetchApp.fetch('https://ovrstat.com/stats/psn/'+name);

        var json = response.getContentText();

        var data = JSON.parse(json);

        Logger.log(data.rating);



        var sheet = SpreadsheetApp.getActiveSheet();

        sheet.getRange(rangeB).setValue([data.rating]);

    };

};

edited Nov 15 '18 at 23:33

TheMaster

10.5k3936

asked Nov 15 '18 at 19:30

Robert Duffany

112

You don't call the function ovrstat? I also wonder by you define a function inside a loop. Just have the code that is in that function, and drop the function wrapper.

– trincot
Nov 15 '18 at 19:38

I updated my code, I added a ui button in sheets to call the function

– Robert Duffany
Nov 15 '18 at 19:53

1

But it makes no sense to place function inside a loop, and then call the function from somewhere else. Did you expect the function to be executed in a loop? Because that is not going to happen... You should move the line with function ovrstat() { before the line var nameCell = (and adapt the indentation of your code accordingly).

– trincot
Nov 15 '18 at 19:56

I suggest you look at some JavaScript tutorials to learn the difference between a function definition and a function call.

– Code-Apprentice
Nov 15 '18 at 20:00

Thanks guys, Moving function ovrstat() { before the line var nameCell = worked. My first go at doing anything like this and I was just confused by what I had read I guess.

– Robert Duffany
Nov 15 '18 at 20:15

add a comment |

I'm trying to make a Google Sheets JS function that will read the data in a certain cell, call an API in regards to that value, parse the data and return the result in the cell next to the original.

I can make this happen on a one by one basis, but I'm trying to make it loop through a set of arrays.

When I run the function it's ending with only the last index call results.

Am I going about this entirely wrong, or just missing something?

//Create button on Sheet in dropdown

function onOpen() {

    var ui = SpreadsheetApp.getUi();

    ui.createMenu('Overwatch API Menu')

        .addItem('Get Updated Stats','ovrstat')

        .addToUi();

};

var nameCell = ["A2","A3","A4","A5"];

var rate = ["B2","B3","B4","B5"];

var i;

for (i = 0; i < 4; i = i + 1){

    var rangeA = nameCell[i];

    var rangeB = rate[i];

    function ovrstat() {

        var ss = SpreadsheetApp.getActiveSpreadsheet();

        var sheet = ss.getActiveSheet();

        var name = sheet.getRange(rangeA).getValue();

         // Call the Ovrstat API

        var response = UrlFetchApp.fetch('https://ovrstat.com/stats/psn/'+name);

        var json = response.getContentText();

        var data = JSON.parse(json);

        Logger.log(data.rating);



        var sheet = SpreadsheetApp.getActiveSheet();

        sheet.getRange(rangeB).setValue([data.rating]);

    };

};

edited Nov 15 '18 at 23:33

TheMaster

10.5k3936

asked Nov 15 '18 at 19:30

Robert Duffany

112

I'm trying to make a Google Sheets JS function that will read the data in a certain cell, call an API in regards to that value, parse the data and return the result in the cell next to the original.

I can make this happen on a one by one basis, but I'm trying to make it loop through a set of arrays.

When I run the function it's ending with only the last index call results.

Am I going about this entirely wrong, or just missing something?

//Create button on Sheet in dropdown

function onOpen() {

    var ui = SpreadsheetApp.getUi();

    ui.createMenu('Overwatch API Menu')

        .addItem('Get Updated Stats','ovrstat')

        .addToUi();

};

var nameCell = ["A2","A3","A4","A5"];

var rate = ["B2","B3","B4","B5"];

var i;

for (i = 0; i < 4; i = i + 1){

    var rangeA = nameCell[i];

    var rangeB = rate[i];

    function ovrstat() {

        var ss = SpreadsheetApp.getActiveSpreadsheet();

        var sheet = ss.getActiveSheet();

        var name = sheet.getRange(rangeA).getValue();

         // Call the Ovrstat API

        var response = UrlFetchApp.fetch('https://ovrstat.com/stats/psn/'+name);

        var json = response.getContentText();

        var data = JSON.parse(json);

        Logger.log(data.rating);



        var sheet = SpreadsheetApp.getActiveSheet();

        sheet.getRange(rangeB).setValue([data.rating]);

    };

};

javascript arrays loops google-apps-script google-sheets

edited Nov 15 '18 at 23:33

TheMaster

10.5k3936

asked Nov 15 '18 at 19:30

Robert Duffany

112

edited Nov 15 '18 at 23:33

TheMaster

10.5k3936

asked Nov 15 '18 at 19:30

Robert Duffany

112

edited Nov 15 '18 at 23:33

TheMaster

10.5k3936

edited Nov 15 '18 at 23:33

TheMaster

10.5k3936

edited Nov 15 '18 at 23:33

TheMaster

10.5k3936

asked Nov 15 '18 at 19:30

Robert Duffany

112

asked Nov 15 '18 at 19:30

Robert Duffany

112

asked Nov 15 '18 at 19:30

Robert Duffany

112

You don't call the function ovrstat? I also wonder by you define a function inside a loop. Just have the code that is in that function, and drop the function wrapper.

– trincot
Nov 15 '18 at 19:38

I updated my code, I added a ui button in sheets to call the function

– Robert Duffany
Nov 15 '18 at 19:53

1

But it makes no sense to place function inside a loop, and then call the function from somewhere else. Did you expect the function to be executed in a loop? Because that is not going to happen... You should move the line with function ovrstat() { before the line var nameCell = (and adapt the indentation of your code accordingly).

– trincot
Nov 15 '18 at 19:56

I suggest you look at some JavaScript tutorials to learn the difference between a function definition and a function call.

– Code-Apprentice
Nov 15 '18 at 20:00

Thanks guys, Moving function ovrstat() { before the line var nameCell = worked. My first go at doing anything like this and I was just confused by what I had read I guess.

– Robert Duffany
Nov 15 '18 at 20:15

add a comment |

You don't call the function ovrstat? I also wonder by you define a function inside a loop. Just have the code that is in that function, and drop the function wrapper.

– trincot
Nov 15 '18 at 19:38

I updated my code, I added a ui button in sheets to call the function

– Robert Duffany
Nov 15 '18 at 19:53

1

But it makes no sense to place function inside a loop, and then call the function from somewhere else. Did you expect the function to be executed in a loop? Because that is not going to happen... You should move the line with function ovrstat() { before the line var nameCell = (and adapt the indentation of your code accordingly).

– trincot
Nov 15 '18 at 19:56

I suggest you look at some JavaScript tutorials to learn the difference between a function definition and a function call.

– Code-Apprentice
Nov 15 '18 at 20:00

Thanks guys, Moving function ovrstat() { before the line var nameCell = worked. My first go at doing anything like this and I was just confused by what I had read I guess.

– Robert Duffany
Nov 15 '18 at 20:15

You don't call the function ovrstat? I also wonder by you define a function inside a loop. Just have the code that is in that function, and drop the function wrapper.

– trincot
Nov 15 '18 at 19:38

I updated my code, I added a ui button in sheets to call the function

– Robert Duffany
Nov 15 '18 at 19:53

But it makes no sense to place function inside a loop, and then call the function from somewhere else. Did you expect the function to be executed in a loop? Because that is not going to happen... You should move the line with function ovrstat() { before the line var nameCell = (and adapt the indentation of your code accordingly).

– trincot
Nov 15 '18 at 19:56

I suggest you look at some JavaScript tutorials to learn the difference between a function definition and a function call.

– Code-Apprentice
Nov 15 '18 at 20:00

Thanks guys, Moving function ovrstat() { before the line var nameCell = worked. My first go at doing anything like this and I was just confused by what I had read I guess.

– Robert Duffany
Nov 15 '18 at 20:15

add a comment |

0

active

oldest

votes

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53326690%2fgoogle-sheets-javascript-function-loop%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

0

active

oldest

votes

0

active

oldest

votes

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Vfrdtyky