AppScript - Custom function update on row value change
Currently my custom AppScript function has an argument that is the row the function is being called. (Because you can't find the location where a function is called apparently).
To have the input be the row, I use a separate column that is used as an input (ranges B2-B29 whom's values are 2-29). Because the auto complete sequence that one usually get's from dragging across cells doesn't work for custom functions (apparently). I.e. you can't have a cell MYFUNC(1) right below it have MYFUNC(2), then select both and drag down to get MYFUNC(3) etc.
But because the input is static (reliant on the unchanging range B2-B29) when the row values change I have to set B2-B29 to something else and then rewrite B2-B29 to the values I want.
It works so I can live with it but I want to know if there's a better way to approach this problem. I would like a way to have my function recalculated on the values. Now since it's all numerical my current solution is to take the sum of the row and then pass that in but that's a hack.
google-apps-script
asked Nov 25 '18 at 13:03
Liam PieriLiam Pieri
1641314
add a comment |
Currently my custom AppScript function has an argument that is the row the function is being called. (Because you can't find the location where a function is called apparently).
To have the input be the row, I use a separate column that is used as an input (ranges B2-B29 whom's values are 2-29). Because the auto complete sequence that one usually get's from dragging across cells doesn't work for custom functions (apparently). I.e. you can't have a cell MYFUNC(1) right below it have MYFUNC(2), then select both and drag down to get MYFUNC(3) etc.
But because the input is static (reliant on the unchanging range B2-B29) when the row values change I have to set B2-B29 to something else and then rewrite B2-B29 to the values I want.
It works so I can live with it but I want to know if there's a better way to approach this problem. I would like a way to have my function recalculated on the values. Now since it's all numerical my current solution is to take the sum of the row and then pass that in but that's a hack.
google-apps-script
asked Nov 25 '18 at 13:03
Liam PieriLiam Pieri
1641314
You could always have a column of the numbers lets say A1:A29 then in B1 =MYFUNC(A1). Now when you drag down it will replace A1 with A2 to A29 and the values in B2 to B29 will reflect the values in A2 to A29.
– TheWizEd
Nov 25 '18 at 14:11
@TheWizEd that's what I did. I will rewrite my answer to make it more clear.
– Liam Pieri
Nov 25 '18 at 14:44
1
Your assumptions seem to be wrong and your question is Unclear. Show your apps script and explain input and outputs and the problem clearly with a Minimal, Complete, and Verifiable example
– TheMaster
Nov 25 '18 at 14:48
add a comment |
Currently my custom AppScript function has an argument that is the row the function is being called. (Because you can't find the location where a function is called apparently).
To have the input be the row, I use a separate column that is used as an input (ranges B2-B29 whom's values are 2-29). Because the auto complete sequence that one usually get's from dragging across cells doesn't work for custom functions (apparently). I.e. you can't have a cell MYFUNC(1) right below it have MYFUNC(2), then select both and drag down to get MYFUNC(3) etc.
But because the input is static (reliant on the unchanging range B2-B29) when the row values change I have to set B2-B29 to something else and then rewrite B2-B29 to the values I want.
It works so I can live with it but I want to know if there's a better way to approach this problem. I would like a way to have my function recalculated on the values. Now since it's all numerical my current solution is to take the sum of the row and then pass that in but that's a hack.
google-apps-script
asked Nov 25 '18 at 13:03
Liam PieriLiam Pieri
1641314
Currently my custom AppScript function has an argument that is the row the function is being called. (Because you can't find the location where a function is called apparently).
To have the input be the row, I use a separate column that is used as an input (ranges B2-B29 whom's values are 2-29). Because the auto complete sequence that one usually get's from dragging across cells doesn't work for custom functions (apparently). I.e. you can't have a cell MYFUNC(1) right below it have MYFUNC(2), then select both and drag down to get MYFUNC(3) etc.
But because the input is static (reliant on the unchanging range B2-B29) when the row values change I have to set B2-B29 to something else and then rewrite B2-B29 to the values I want.
It works so I can live with it but I want to know if there's a better way to approach this problem. I would like a way to have my function recalculated on the values. Now since it's all numerical my current solution is to take the sum of the row and then pass that in but that's a hack.
google-apps-script
google-apps-script
asked Nov 25 '18 at 13:03
Liam PieriLiam Pieri
1641314
asked Nov 25 '18 at 13:03
Liam PieriLiam Pieri
1641314
edited Nov 25 '18 at 16:08
Liam Pieri
asked Nov 25 '18 at 13:03
Liam PieriLiam Pieri
1641314
asked Nov 25 '18 at 13:03
Liam PieriLiam Pieri
1641314
asked Nov 25 '18 at 13:03
Liam PieriLiam Pieri
1641314
1641314
You could always have a column of the numbers lets say A1:A29 then in B1 =MYFUNC(A1). Now when you drag down it will replace A1 with A2 to A29 and the values in B2 to B29 will reflect the values in A2 to A29.
– TheWizEd
Nov 25 '18 at 14:11
@TheWizEd that's what I did. I will rewrite my answer to make it more clear.
– Liam Pieri
Nov 25 '18 at 14:44
1
Your assumptions seem to be wrong and your question is Unclear. Show your apps script and explain input and outputs and the problem clearly with a Minimal, Complete, and Verifiable example
– TheMaster
Nov 25 '18 at 14:48
add a comment |
You could always have a column of the numbers lets say A1:A29 then in B1 =MYFUNC(A1). Now when you drag down it will replace A1 with A2 to A29 and the values in B2 to B29 will reflect the values in A2 to A29.
– TheWizEd
Nov 25 '18 at 14:11
@TheWizEd that's what I did. I will rewrite my answer to make it more clear.
– Liam Pieri
Nov 25 '18 at 14:44
1
Your assumptions seem to be wrong and your question is Unclear. Show your apps script and explain input and outputs and the problem clearly with a Minimal, Complete, and Verifiable example
– TheMaster
Nov 25 '18 at 14:48
You could always have a column of the numbers lets say A1:A29 then in B1 =MYFUNC(A1). Now when you drag down it will replace A1 with A2 to A29 and the values in B2 to B29 will reflect the values in A2 to A29.
– TheWizEd
Nov 25 '18 at 14:11
You could always have a column of the numbers lets say A1:A29 then in B1 =MYFUNC(A1). Now when you drag down it will replace A1 with A2 to A29 and the values in B2 to B29 will reflect the values in A2 to A29.
– TheWizEd
Nov 25 '18 at 14:11
@TheWizEd that's what I did. I will rewrite my answer to make it more clear.
– Liam Pieri
Nov 25 '18 at 14:44
@TheWizEd that's what I did. I will rewrite my answer to make it more clear.
– Liam Pieri
Nov 25 '18 at 14:44
1
1
Your assumptions seem to be wrong and your question is Unclear. Show your apps script and explain input and outputs and the problem clearly with a Minimal, Complete, and Verifiable example
– TheMaster
Nov 25 '18 at 14:48
Your assumptions seem to be wrong and your question is Unclear. Show your apps script and explain input and outputs and the problem clearly with a Minimal, Complete, and Verifiable example
– TheMaster
Nov 25 '18 at 14:48
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53467718%2fappscript-custom-function-update-on-row-value-change%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53467718%2fappscript-custom-function-update-on-row-value-change%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You could always have a column of the numbers lets say A1:A29 then in B1 =MYFUNC(A1). Now when you drag down it will replace A1 with A2 to A29 and the values in B2 to B29 will reflect the values in A2 to A29.
– TheWizEd
Nov 25 '18 at 14:11
@TheWizEd that's what I did. I will rewrite my answer to make it more clear.
– Liam Pieri
Nov 25 '18 at 14:44
1
Your assumptions seem to be wrong and your question is Unclear. Show your apps script and explain input and outputs and the problem clearly with a Minimal, Complete, and Verifiable example
– TheMaster
Nov 25 '18 at 14:48