Google sheet calculations following data insert
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a static site with a form which I am hosting on netlify . Using the netlify to sheets zap , when the form is submitted on the site, the data shows up as a row in my sheet, as expected.
After the insert which adds the last row. I want to do some apps script post processing, I'm triggering this using the onchange event. Before I do the post processing I want to fill in a couple of blank cells which are not submitted by the form, such as a date field and a TOTAL field. which takes the price cell to the left of it and adds 1500.
I originally thought of just using a formula:
=(cell on left)+1500
and copying this down the row. The problem is that this would mess up my ability to get the last submitted row.
I need it to look like:
before I start additional processing.
Is there another approach to doing calculations within a specific row?
javascript google-apps-script google-sheets
asked Nov 16 '18 at 21:32
user61629user61629
9,24638145273
|
show 1 more comment
I have a static site with a form which I am hosting on netlify . Using the netlify to sheets zap , when the form is submitted on the site, the data shows up as a row in my sheet, as expected.
After the insert which adds the last row. I want to do some apps script post processing, I'm triggering this using the onchange event. Before I do the post processing I want to fill in a couple of blank cells which are not submitted by the form, such as a date field and a TOTAL field. which takes the price cell to the left of it and adds 1500.
I originally thought of just using a formula:
=(cell on left)+1500
and copying this down the row. The problem is that this would mess up my ability to get the last submitted row.
I need it to look like:
before I start additional processing.
Is there another approach to doing calculations within a specific row?
javascript google-apps-script google-sheets
asked Nov 16 '18 at 21:32
user61629user61629
9,24638145273
Does theonChangetrigger actually trigger on row insert by a thing that is not human? Test and see.
– TheMaster
Nov 16 '18 at 21:37
Yes it is triggered by the sheet row insert. I'm using it to activate the post processing. How to trigger is not my question. I'm asking how best to do some intra row calculations?
– user61629
Nov 16 '18 at 21:47
Total field can be easily done with just formula. What other fields are you expecting to fill up?
– TheMaster
Nov 16 '18 at 21:51
Yes but I want to keep the submitted row as the last row. Adding a column of formulas breaks this.Please see screenshot.
– user61629
Nov 16 '18 at 21:58
I got that before your edit. I could show you a way with formula for the total field. But, if you want other fields, you need to show some scripts and explain other fields, positions and what have you tried so far...
– TheMaster
Nov 16 '18 at 22:08
|
show 1 more comment
I have a static site with a form which I am hosting on netlify . Using the netlify to sheets zap , when the form is submitted on the site, the data shows up as a row in my sheet, as expected.
After the insert which adds the last row. I want to do some apps script post processing, I'm triggering this using the onchange event. Before I do the post processing I want to fill in a couple of blank cells which are not submitted by the form, such as a date field and a TOTAL field. which takes the price cell to the left of it and adds 1500.
I originally thought of just using a formula:
=(cell on left)+1500
and copying this down the row. The problem is that this would mess up my ability to get the last submitted row.
I need it to look like:
before I start additional processing.
Is there another approach to doing calculations within a specific row?
javascript google-apps-script google-sheets
asked Nov 16 '18 at 21:32
user61629user61629
9,24638145273
I have a static site with a form which I am hosting on netlify . Using the netlify to sheets zap , when the form is submitted on the site, the data shows up as a row in my sheet, as expected.
After the insert which adds the last row. I want to do some apps script post processing, I'm triggering this using the onchange event. Before I do the post processing I want to fill in a couple of blank cells which are not submitted by the form, such as a date field and a TOTAL field. which takes the price cell to the left of it and adds 1500.
I originally thought of just using a formula:
=(cell on left)+1500
and copying this down the row. The problem is that this would mess up my ability to get the last submitted row.
I need it to look like:
before I start additional processing.
Is there another approach to doing calculations within a specific row?
javascript google-apps-script google-sheets
javascript google-apps-script google-sheets
asked Nov 16 '18 at 21:32
user61629user61629
9,24638145273
asked Nov 16 '18 at 21:32
user61629user61629
9,24638145273
edited Nov 16 '18 at 22:25
user61629
asked Nov 16 '18 at 21:32
user61629user61629
9,24638145273
asked Nov 16 '18 at 21:32
user61629user61629
9,24638145273
asked Nov 16 '18 at 21:32
user61629user61629
9,24638145273
9,24638145273
Does theonChangetrigger actually trigger on row insert by a thing that is not human? Test and see.
– TheMaster
Nov 16 '18 at 21:37
Yes it is triggered by the sheet row insert. I'm using it to activate the post processing. How to trigger is not my question. I'm asking how best to do some intra row calculations?
– user61629
Nov 16 '18 at 21:47
Total field can be easily done with just formula. What other fields are you expecting to fill up?
– TheMaster
Nov 16 '18 at 21:51
Yes but I want to keep the submitted row as the last row. Adding a column of formulas breaks this.Please see screenshot.
– user61629
Nov 16 '18 at 21:58
I got that before your edit. I could show you a way with formula for the total field. But, if you want other fields, you need to show some scripts and explain other fields, positions and what have you tried so far...
– TheMaster
Nov 16 '18 at 22:08
|
show 1 more comment
Does theonChangetrigger actually trigger on row insert by a thing that is not human? Test and see.
– TheMaster
Nov 16 '18 at 21:37
Yes it is triggered by the sheet row insert. I'm using it to activate the post processing. How to trigger is not my question. I'm asking how best to do some intra row calculations?
– user61629
Nov 16 '18 at 21:47
Total field can be easily done with just formula. What other fields are you expecting to fill up?
– TheMaster
Nov 16 '18 at 21:51
Yes but I want to keep the submitted row as the last row. Adding a column of formulas breaks this.Please see screenshot.
– user61629
Nov 16 '18 at 21:58
I got that before your edit. I could show you a way with formula for the total field. But, if you want other fields, you need to show some scripts and explain other fields, positions and what have you tried so far...
– TheMaster
Nov 16 '18 at 22:08
Does the
onChange trigger actually trigger on row insert by a thing that is not human? Test and see.– TheMaster
Nov 16 '18 at 21:37
Does the
onChange trigger actually trigger on row insert by a thing that is not human? Test and see.– TheMaster
Nov 16 '18 at 21:37
Yes it is triggered by the sheet row insert. I'm using it to activate the post processing. How to trigger is not my question. I'm asking how best to do some intra row calculations?
– user61629
Nov 16 '18 at 21:47
Yes it is triggered by the sheet row insert. I'm using it to activate the post processing. How to trigger is not my question. I'm asking how best to do some intra row calculations?
– user61629
Nov 16 '18 at 21:47
Total field can be easily done with just formula. What other fields are you expecting to fill up?
– TheMaster
Nov 16 '18 at 21:51
Total field can be easily done with just formula. What other fields are you expecting to fill up?
– TheMaster
Nov 16 '18 at 21:51
Yes but I want to keep the submitted row as the last row. Adding a column of formulas breaks this.Please see screenshot.
– user61629
Nov 16 '18 at 21:58
Yes but I want to keep the submitted row as the last row. Adding a column of formulas breaks this.Please see screenshot.
– user61629
Nov 16 '18 at 21:58
I got that before your edit. I could show you a way with formula for the total field. But, if you want other fields, you need to show some scripts and explain other fields, positions and what have you tried so far...
– TheMaster
Nov 16 '18 at 22:08
I got that before your edit. I could show you a way with formula for the total field. But, if you want other fields, you need to show some scripts and explain other fields, positions and what have you tried so far...
– TheMaster
Nov 16 '18 at 22:08
|
show 1 more comment
1 Answer
1
active
oldest
votes
Assumptions:
PRICEis in A1- No blank cells in between rows
Strategy:
- Clear B1:B
- COUNT the number of cells in ColA
- Use INDEX/INDIRECT to create a dynamic range with COUNT
- Use Arrayformula with array literals to fill up Col B just up to the last filled ColA
Sample Formula:
B1:
=ARRAYFORMULA({"TOTAL";A2:INDEX(A:A,COUNTA(A:A))+1500})
answered Nov 16 '18 at 22:29
TheMasterTheMaster
10.9k3937
Thanks for the example . I'm not familiar with these spreadsheet formulas, so I will have to look them up. Is there only a single formula in b1 needed?
– user61629
Nov 17 '18 at 16:05
@user yes.......
– TheMaster
Nov 17 '18 at 16:07
add a comment |
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%2f53345701%2fgoogle-sheet-calculations-following-data-insert%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Assumptions:
PRICEis in A1- No blank cells in between rows
Strategy:
- Clear B1:B
- COUNT the number of cells in ColA
- Use INDEX/INDIRECT to create a dynamic range with COUNT
- Use Arrayformula with array literals to fill up Col B just up to the last filled ColA
Sample Formula:
B1:
=ARRAYFORMULA({"TOTAL";A2:INDEX(A:A,COUNTA(A:A))+1500})
answered Nov 16 '18 at 22:29
TheMasterTheMaster
10.9k3937
Thanks for the example . I'm not familiar with these spreadsheet formulas, so I will have to look them up. Is there only a single formula in b1 needed?
– user61629
Nov 17 '18 at 16:05
@user yes.......
– TheMaster
Nov 17 '18 at 16:07
add a comment |
Assumptions:
PRICEis in A1- No blank cells in between rows
Strategy:
- Clear B1:B
- COUNT the number of cells in ColA
- Use INDEX/INDIRECT to create a dynamic range with COUNT
- Use Arrayformula with array literals to fill up Col B just up to the last filled ColA
Sample Formula:
B1:
=ARRAYFORMULA({"TOTAL";A2:INDEX(A:A,COUNTA(A:A))+1500})
answered Nov 16 '18 at 22:29
TheMasterTheMaster
10.9k3937
Thanks for the example . I'm not familiar with these spreadsheet formulas, so I will have to look them up. Is there only a single formula in b1 needed?
– user61629
Nov 17 '18 at 16:05
@user yes.......
– TheMaster
Nov 17 '18 at 16:07
add a comment |
Assumptions:
PRICEis in A1- No blank cells in between rows
Strategy:
- Clear B1:B
- COUNT the number of cells in ColA
- Use INDEX/INDIRECT to create a dynamic range with COUNT
- Use Arrayformula with array literals to fill up Col B just up to the last filled ColA
Sample Formula:
B1:
=ARRAYFORMULA({"TOTAL";A2:INDEX(A:A,COUNTA(A:A))+1500})
answered Nov 16 '18 at 22:29
TheMasterTheMaster
10.9k3937
Assumptions:
PRICEis in A1- No blank cells in between rows
Strategy:
- Clear B1:B
- COUNT the number of cells in ColA
- Use INDEX/INDIRECT to create a dynamic range with COUNT
- Use Arrayformula with array literals to fill up Col B just up to the last filled ColA
Sample Formula:
B1:
=ARRAYFORMULA({"TOTAL";A2:INDEX(A:A,COUNTA(A:A))+1500})
answered Nov 16 '18 at 22:29
TheMasterTheMaster
10.9k3937
answered Nov 16 '18 at 22:29
TheMasterTheMaster
10.9k3937
answered Nov 16 '18 at 22:29
TheMasterTheMaster
10.9k3937
answered Nov 16 '18 at 22:29
TheMasterTheMaster
10.9k3937
10.9k3937
Thanks for the example . I'm not familiar with these spreadsheet formulas, so I will have to look them up. Is there only a single formula in b1 needed?
– user61629
Nov 17 '18 at 16:05
@user yes.......
– TheMaster
Nov 17 '18 at 16:07
add a comment |
Thanks for the example . I'm not familiar with these spreadsheet formulas, so I will have to look them up. Is there only a single formula in b1 needed?
– user61629
Nov 17 '18 at 16:05
@user yes.......
– TheMaster
Nov 17 '18 at 16:07
Thanks for the example . I'm not familiar with these spreadsheet formulas, so I will have to look them up. Is there only a single formula in b1 needed?
– user61629
Nov 17 '18 at 16:05
Thanks for the example . I'm not familiar with these spreadsheet formulas, so I will have to look them up. Is there only a single formula in b1 needed?
– user61629
Nov 17 '18 at 16:05
@user yes.......
– TheMaster
Nov 17 '18 at 16:07
@user yes.......
– TheMaster
Nov 17 '18 at 16:07
add a comment |
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%2f53345701%2fgoogle-sheet-calculations-following-data-insert%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
Does the
onChangetrigger actually trigger on row insert by a thing that is not human? Test and see.– TheMaster
Nov 16 '18 at 21:37
Yes it is triggered by the sheet row insert. I'm using it to activate the post processing. How to trigger is not my question. I'm asking how best to do some intra row calculations?
– user61629
Nov 16 '18 at 21:47
Total field can be easily done with just formula. What other fields are you expecting to fill up?
– TheMaster
Nov 16 '18 at 21:51
Yes but I want to keep the submitted row as the last row. Adding a column of formulas breaks this.Please see screenshot.
– user61629
Nov 16 '18 at 21:58
I got that before your edit. I could show you a way with formula for the total field. But, if you want other fields, you need to show some scripts and explain other fields, positions and what have you tried so far...
– TheMaster
Nov 16 '18 at 22:08