Script copying from tab A to B

[raf sauvillers]

Hello,

Link of my Google Sheet is following:

https://docs.google.com/spreadsheets/d/1k3gAIuv4x1laTpo_K98x4tmLLSzHE7e14FbpYnppg70/edit?usp=sharing

I am trying to make a script that copies the text from tab BK: (for example) cell E2:I4 to a new tab named Stage1 when in cell A2 is set 'Ja' (means yes in dutch)

My script seems to work but now I should repeat the same again and again but do not know how exactly ...

(Cell with Ja    -   Copy from:to)

A2  - E2:I4

A5   - E5:I7           

A8 - E8:I10

A11 - E11:I14

A15 - E15:I17

A18 - E18:I20

A21 - E21:I23

A24 - E24:I26

A27 - E27:I30

A31 - E31:I33

A34 - E34:I36

A37 - E37:I39

A40 -E40:I42

A43 -E43:I45

A46 - E46:I48

A49 - E49:I51

A52 - E52:I54

A55 -E55:I57

A58 - E58:I60

A61 - E61:I63

A64 - E64:I67

A68 - E68:I70

A71 -E71:I73

A74 - E74:I78

A79 - E79:I81

A82 - E82:I84

A85 - E85:I87

A88 -E88:I90

A91 - E91:I93

A94 - E94:I95

A96 - E96:I98

A99 - E99:I101

A102 - E102:I103

A104 - E104:I105

A106-E106:I107

A108-E108:I110

A111-E111I:111

A112-E112:I112

A113-E113:I113

A115-E115:I115

A117-E117:I117

A118-E118:I118

A120-E120:I120

A122-E122:I122

A124-E124:I124

A126-E126:I126

If this works I want exactly the same but when there is 'Ja' in cell B2 it should be copied to tab 'Stage2'

And if there is 'Ja' in cell C2, it should be copied to tab 'Stage3'

And if there is 'Ja' in cell D2, it should be copied to tab 'Stage4'

*****

If this all works then I need in tab 'Stage1' for example in colom A to count points (Punten) so for example if I select the cell C3 it has to count 5 in A3, if there was selected D3 in cell A3 should be standing 10, if selected E3 then in A3 must stand 15 and if selected F3 in A3 must be standing 20

Hope someone can help me go trough this?

Thanks a lot already for help or usefull tips

[Keith Andersen]

*Place in Cell B3 of tab Stage1
=QUERY( BK!A2:I, "SELECT E, F, G, H, I WHERE WHERE A = 'Ja' ")

*Place in Cell B3 of tab Stage2
=QUERY( BK!A2:I, "SELECT E, F, G, H, I WHERE WHERE B = 'Ja' ")

*Place in Cell B3 of tab Stage3
=QUERY( BK!A2:I, "SELECT E, F, G, H, I WHERE WHERE C = 'Ja' ")

*Place in Cell B3 of tab Stage4
=QUERY( BK!A2:I, "SELECT E, F, G, H, I WHERE WHERE D = 'Ja' ")

Test by putting Ja in various columns

I'm working on the 2nd problem with punten.

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/3b2b7dad-5ac3-41f1-aa67-73f3d830f518n%40googlegroups.com.

[Edward Friedman (Eddie)]

For the points, you could set custom values for each checkbox. The unchecked value would be 0 and the checked values would be  5, 10, 15,  or 20. Then use the MAX formula to get the points value. =MAX(C3:F3)

[Keith Andersen]

You asked....

If this all works then I need in tab 'Stage1' for example in colom A to count points (Punten) so for example if I select the cell C3 it has to count 5 in A3, if there was selected D3 in cell A3 should be standing 10, if selected E3 then in A3 must stand 15 and if selected F3 in A3 must be standing 20

Where are you selecting C3, D3 or E3?  A different tab?

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/f43c77f4-3f47-4b01-a231-b06e018b3890n%40googlegroups.com.

[Edward Friedman (Eddie)]

Hi Raf,

Sorry for the delay. If you're still having the same problem you mentioned before try this:

I don't know the Dutch version of the formula names, but it should work if you copy & paste the formula below into your sheet.

Change your formula to use the IFS function:

=IFNA(IFS(F4;20;E4;15;D4;10;C4;5);0)

IFNA ensures that if no checkboxes are selected, 0 is displayed. You could make it an empty string ("") as well.

IFS, with the conditions in descending (F4 E4 D4 C4) order, ensures that you give the most points possible if someone checks more than one box. 

If you prefer to give the LEAST points reverse the order of the arguments and use:

=IFNA(IFS(C4;5;D4;10;E4;15;F4;20);0)

I hope that helps!

-Eddie

[raf sauvillers]

Thanks a lot, I'll try as soon as I am back from holiday :-)

Op ma 8 jul 2024 om 18:26 schreef Edward Friedman (Eddie) <e...@eddtechk12.com>:

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/CcCTCrUlvTc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/4868c3ee-1bdd-4048-837a-abc306d2c98cn%40googlegroups.com.

--

met vriendelijke groet,

Raf