Via macro een row in een andere blad transponeren (Google)

[lobo405]

Voor een werkgroep heb ik in Google formulieren een Rapportageformulier gemaakt.
De antwoorden komen direct online binnen na het invullen en van daaruit in een Google spreadsheet.
Van uit die spreadsheet met alle reacties moet ik per regel (per reactie dus) een rapport kunnen maken (transponeren van die regel) en als PDF versturen.
Alles werkt goed qua formulier en spreadsheet en handmatig dat rapport maken gaat ook.
Dat doe ik dan zo;
- selecteren van de row,
- kopieer (Ctl C bijv.)
- naar het rapportblad gaan van de spreadsheet op de juist rij/kolom (altijd C2)
- speciaal plakken - getransponeerd.
Handmatig lukt dit allemaal dus prima en ik heb dan precies het opgemaakte rapport dat wij willen.

Macro zo gemaakt (opgenomen wat ik dus ook handmatig doe) en aan een sneltoets gekoppeld;
- Start opnemen macro.
- ga naar blad 1, daar staan de brongegevens.
- klik links op de rijnummer waarin alle formuliergegevens van een ingevuld formulier staan.
- kopieer die rijgegevens
- ga naar blad 2 en klik op de cel waar alles naartoe moet als startpositie (altijd dezelfde start cel)
- klik op speciaal plakken - getransponeerd
- en vervolgens stop ik de macro.

Nu verwachtte ik dus dat via die macro alles ook goed gaat maar dat gaat dus mis.
De boel wordt willekeurig geplakt in dat rapportage blad en ik kom er niet uit hoe dit komt.
Ik ben gewend met Excel VBA te werken en daar heb ik ook naslagwerken van, maar dit lukt mij niet.

Dit is de macro zoals die is opgenomen.

/** @OnlyCurrentDoc */

function Formulier_Rapportage() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Rapportage Aanvrager'), true);
spreadsheet.getCurrentCell().offset(0, 0).activate();
spreadsheet.getRange('\'Formulierreacties 1\'!3:3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true);
};

Geen idee waar het mis gaat en ik weet ook niet hoe ik dan dat plakken speciaal gewoon direct naar een gedefinieerde cel gaat (altijd C2)
Het transponeren gaat wel als zodanig goed, maar het is een gok waar het terecht komt, soms zelfs op de plek van de begeleidende standaard teksten.
Wat mij betreft mag ook vast in het script staan dat de getransponeerde data komt vanaf C2 tnaar beneden geplakt wordt.

Graag jullie help, we zijn allemaal vrijwilligers
Bob

[dixet]

lobo405 schreef op donderdag 8 februari 2024 @ 18:15:


Dit is de macro zoals die is opgenomen.

/** @OnlyCurrentDoc */

function Formulier_Rapportage() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Rapportage Aanvrager'), true);
spreadsheet.getCurrentCell().offset(0, 0).activate();
spreadsheet.getRange('\'Formulierreacties 1\'!3:3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true);
};

Ik ben niet heel bekend met de scripts achter google sheets maar met

code:

spreadsheet.getCurrentCell().offset(0, 0).activate();

activeer je de huidige geselecteerde cel. Als je zeker wilt weten dat er altijd geplakt wordt op C2 lijkt me dit beter

code:

spreadsheet.getRange('C2').activate()

daarmee activeer je expliciet C2, ongeacht wat op dat moment de actieve cel was en plak je dus altijd op die plek.

[lobo405]

hallo 'dixet'
Bedankt voor het snelle antwoord.
Qua getransponeerd krijgen naar de juiste locatie werkt jouw voorstel prima !

Maar nu merk ik dat wat ik verwacht had te kunnen nog niet goed werkt.
De bedoeling is dat de op dat moment geselecteerde row van de eerste sheet "Formulierreacties 1" wordt getransponeerd
Nu blijkt dat meestal gewoon de eerste toegestane regel wordt getransponeerd vanaf "Formulierreacties 1".

Het is dus de bedoeling dat van al die rapportages die binnenkomen van Google forms en die vanuit forms worden toegevoegd in de spreadsheet, de 'dienstdoende' coördinator de regel waarvan een rapport moet worden gemaakt selecteert en daarna op de macro klikt om die geselecteerde regel in het rapport op die sheet Rapportage Aanvrager te transponeren.

Helaas is het dus altijd de eerste regel voor zover ik kan zien

Dus hiervoor moet ook nog een oplossing gevonden worden.
Als je daar ook iets op weet, graag, ik zoek sowieso ook verder.
Ik denk zelf dat het zit in dit stukje script:
spreadsheet.getRange('\'Formulierreacties 1\'!3:3')
Dat zou dus niet fixed moeten zijn maar de row die geselecteerd is.

Maar dat andere werkt prima.

[ Voor 8% gewijzigd door lobo405 op 09-02-2024 12:42 ]

[dixet]

Je kopieert nu inderdaad hard rij 3 (de verwijzing "!3:3"

Met spreadsheet.getActiveCell().getRow() kom je al een eind in de buurt, daarmee verwijs de naar de huidige actieve rij

[lobo405]

dixet, ik zal het complete script even herzien want hij heeft een probleem met het opslaan voor gebruik van de bronsheet row locatie.
Het verschil tussen MS VBA en Google scripts is groter dan ik dacht
ik heb nu even geen tijd om er verder naar te kijken, dat komt morgen.
bedankt ik ben in ieder geval weer een stapje verder.

[lobo405]

Helaas dixet, ik kom er niet uit.
Ik zou willen dat ik een goede handleiding had die ik echt kon begrijpen, maar ik wordt een dagje ouder en dus gaat dat niet meer zo soepel.

Op dit moment heb ik dus dit staan en daar zit de oude regel als commentaar en de nieuwe regel ervoor, maar die is fout.
Ik kom er niet goed achter wat er fout is.

function FormulierRapportage() {
var spreadsheet = SpreadsheetApp.getActive();
spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Rapportage Aanvrager'), true);
/** spreadsheet.getCurrentCell().activate(); */
spreadsheet.getRange('C2').activate()
spreadsheet.getActiveCell().getRow().copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true);
/** spreadsheet.getRange('\'Formulierreacties 1\'!3:3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true); */
spreadsheet.getCurrentCell().offset(0, 0, 22, 1).activate();
spreadsheet.getActiveRangeList().setHorizontalAlignment('left');
spreadsheet.getCurrentCell().offset(22, 0).activate();
spreadsheet.getActiveRangeList().setHorizontalAlignment('left');
spreadsheet.getCurrentCell().offset(-1, 0).activate();
spreadsheet.getActiveRangeList().setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);
};

[lobo405]

dixet schreef op vrijdag 9 februari 2024 @ 15:14:
Met spreadsheet.getActiveCell().getRow() kom je al een eind in de buurt, daarmee verwijs de naar de huidige actieve rij

Als je me misschien wat verder kunt en wilt helpen dan zou dat top zijn.
Aanstaande donderdag moeten we gaan starten en zou het mooi zijn als dit werkt.
Uiteraard ga ik zelf me zo goed mogelijk verdiepen in de materie, maar ik loop nog steeds vast op dit stuk.

Bob

[dixet]

disclaimer, ik heb totaal geen ervaring met google sheets scripting. Gelukkig is de taal zelf gewoon javascript, en lijkt het objectenmodel verdacht veel op dat van Excel. Dus ik ga m'n best doen

Even samenvattend wat je wilde bereiken

• De gebruiker selecteert een cel
• De macro moet van de geselecteerde cel de gehele rij kopieren
• De gekopieerde gegevens moeten op een ander tabblad getransponeerd worden geplakt op een vaste plek (C2)

Zelf vind ik het altijd leesbaarder om de bron- en doelbereiken op te slaan in een variabele. Dan heb je niet in ieder commando de hele verwijzing vanaf "spreadsheet." staan.

Zonder toegang tot google sheets op dit moment en zonder verdere kennis van Google scripting zou ik verwachten dat dit zou moeten werken:

code:

function Formulier_Rapportage() {
// huidige spreadsheet vaststellen
var spreadsheet = SpreadsheetApp.getActive();

// huidige cel vaststellen 
var bronCel = spreadsheet.getCurrentCell();
// Actieve rij vaststelllen 
var bronRij = bronCel.getRow();

// doelcel vaststellen
var doelCel = spreadsheet.getRange('\'Formulierreacties 1\'!C2');

// de boel kopieren en getransponeerd plakken 
bronRij.copyTo(doelCel, SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true);
};

je laatste paar regels code zijn alleen voor de opmaak van je doelreeks, die heb ik voor de leesbaarheid maar even achtergelaten.

[lobo405]

dixet schreef op maandag 12 februari 2024 @ 10:28:
Even samenvattend wat je wilde bereiken

• De gebruiker selecteert een cel
• De macro moet van de geselecteerde cel de gehele rij kopieren
• De gekopieerde gegevens moeten op een ander tabblad getransponeerd worden geplakt op een vaste plek (C2)

Dat klopt inderdaad, sommige vrijwilligers kan ik niet van verwachten dat ze voldoende ervaring hebben om dit handmatig te doen.

Zelf vind ik het altijd leesbaarder om de bron- en doelbereiken op te slaan in een variabele. Dan heb je niet in ieder commando de hele verwijzing vanaf "spreadsheet." staan.

Mee eens, maar zover was ik nog lang niet.
Ik gebruikte de basis van de macro die ik had opgenomen en die 'schreef' het zoals het nu is.
Uiteraard ga ik nu jouw manier gebruiken want met VBA in Excel, waar ik meer ervaring mee heb, doe ik dat ook liever.

Jouw voorstel ga ik nu verwerken en ik laat het weten.
Als alles werkt zal ik eerlijk in de werkgroep vertellen dat ik hulp van deze kant heb gehad.

Voor nu alvast wederom, thanks

[lobo405]

Dixet, er gaan dingen fout in deze regel in jouw script.
Google sheets is best lastig vind ik.

// doelcel vaststellen
var doelCel = spreadsheet.getRange('\'Formulierreacties 1\'!C2');

Als ik Logger.log gebruik dan krijg ik voor doelCel als waarde Range in plaats van de cel C2
De bronrij is wel correct

Dit is het complete logboek
12:48:07 Informatie Bronsheet is Spreadsheet
12:48:07 Informatie Broncel is Range
12:48:07 Informatie Bronrij is 2
12:48:07 Informatie Doelcel is Range

Ik ga zelf ook even kijken of ik de reden kan vinden

[dixet]

lobo405 schreef op maandag 12 februari 2024 @ 12:56:
Als ik Logger.log gebruik dan krijg ik voor doelCel als waarde Range in plaats van de cel C2
De bronrij is wel correct

Dit is het complete logboek
12:48:07 Informatie Bronsheet is Spreadsheet
12:48:07 Informatie Broncel is Range
12:48:07 Informatie Bronrij is 2
12:48:07 Informatie Doelcel is Range

Ik ga zelf ook even kijken of ik de reden kan vinden

Dat klopt toch. Een range is een verzameling van 1 of meer cellen. We stoppen de spreadsheet.getCurrentCell(); in bronCel. Dat is een range van 1 cel.
Bij doelCel net zo. Daarin stoppen we de range spreadsheet.getRange('\'Formulierreacties 1\'!C2'). Ook weer een range die verwijst naar 1 cel. Dus zowel bronCel als doelCel bevatten een object van het type Range. Net zoals bronSheet een object van het type Spreadsheet bevat.

Dit werkt niet anders dan in VBA, waar je objecten in variabelen kan stoppen om er later naar te verwijzen.

De functie copyTo verwacht ook een range als eerste parameter dus dat zou goed moeten gaan. Wat krijg je voor foutmelding?

[lobo405]

De foutmelding is
TypeError: bronRij.copyTo is not a function

Ik vermoed dat voor .copyTo geen directe variabele (bronRij) op die manier mag staan

[dixet]

Ik had verwacht dat ronCel.getRow(); een Range zou teruggeven. De copyTo is namelijk een methode op het Range object. Dat heb ik trouwens voor je gegoogled

Maar getRow() geeft alleen het rijnummer. Hier zal je dus nog een Range mee moeten definiëren, bijvoorbeeld via (er is vast een betere manier, want nu is het voor mijn gevoel wel heel omslagtig om de huidige rij te selecteren)

code:

// Actieve rijnummer vaststelllen 
var bronRijNummer = bronCel.getRow();

// Actieve rij ophalen
var bronRij = spreadsheet.getRange('\'Rapportage Aanvrager\'!'&bronRijNummer&":"&bronRijNummer);

Een beetje inlezen in Google Script kan ook helpen. Ik heb namelijk nog nooit in Google Script iets gebouwd. Als je bekend met VBA met zijn de fundamenten echt het zelfde. Met de documentatie er bij zou je toch echt zelf wat verder moeten kunnen komen. Hoe leerzaam ik het ook vindt met je mee te denken

Dat klopt inderdaad, sommige vrijwilligers kan ik niet van verwachten dat ze voldoende ervaring hebben om dit handmatig te doen.

Hou er rekening mee dat dit script volledig idiot-proof moet zijn als jouw vrijwilligers dit met een macro moeten doen omdat je niet kan verwachten dat ze een simpele handmatige instructie kunnen volgen.
Misschien is een handmatige instructie toch een zekerder manier om de juiste uitkomst te garanderen.

Aan de andere kant, je hebt nog tot donderdag om het op deze manier te proberen

[Sethro]

Snap even niet zo goed wat de toegevoegde waarde van de macro en de copy/paste acties zijn.

Ik zou lekker dat rapport zo maken dat je ergens in een enkele cell een naam of ID in kan vullen en de rest van middels index/match of vlookup automatisch wordt opgezocht vanuit de andere spreadsheet.

Dus met normale formules en zonder macro. Macro’s zijn altijd ellendig. Zeker copy/paste.

As het om veel rapporten gaat dan zou je een hele simpele macro kunnen maken die dan door de rijen loopt, voor ieder rij dat ene veld invult en dan formulier als pdf opslaat. Dat zijn 5 regels code max.

[ Voor 38% gewijzigd door Sethro op 12-02-2024 18:41 ]

[lobo405]

dixet schreef op maandag 12 februari 2024 @ 17:08:

Een beetje inlezen in Google Script kan ook helpen. Ik heb namelijk nog nooit in Google Script iets gebouwd. Als je bekend met VBA met zijn de fundamenten echt het zelfde. Met de documentatie er bij zou je toch echt zelf wat verder moeten kunnen komen. Hoe leerzaam ik het ook vindt met je mee te denken

Ik was/ben ook in VBA geen kei, maar heb me er altijd weer in kunnen redden.
Daar is het intussen ook alweer 4 jaar geleden dat ik er wat dingen mee gedaan heb.
Het lijkt dat ik misschien te oud aan het worden ben ( >71 ) om nog goed iets nieuws te begrijpen/leren als het echt ingewikkeld wordt, zeker als je er lang niets mee hebt gedaan (Google nog nooit)

Dixet bedankt voor alle moeite, ook dit werkt niet, nog steeds foutmelding zelfde regel.
Ik ga tot donderdagavond verder met zoeken, ook met de links die ik van jou heb en die ik zelf heb gevonden.
Mocht ik er uit zijn dan laat ik het jou weten.
Groet,
Bob

[lobo405]

Sethro schreef op maandag 12 februari 2024 @ 17:46:
Snap even niet zo goed wat de toegevoegde waarde van de macro en de copy/paste acties zijn.

Ik zou lekker dat rapport zo maken dat je ergens in een enkele cell een naam of ID in kan vullen en de rest van middels index/match of vlookup automatisch wordt opgezocht vanuit de andere spreadsheet.

Dus met normale formules en zonder macro. Macro’s zijn altijd ellendig. Zeker copy/paste.

Het probleem, althans dat gevoel heb ik, is dat het om veel Google formulieren gaat die door vrijwilligers worden ingevuld. Dit gaat om onderzoeken die op verzoek bij iemand thuis worden gedaan.
Google spreadsheet maakt dan automatisch een overzicht van die online ingevulde formulieren.
Daarvan moet dan per formulier op verzoek van een aanvrager een rapport worden gemaakt.
Dat rapport is voor het gemak eveneens een voorop gemaakte 2e sheet waar de horizontale informatie van een regel moet worden getransponeerd naar een verticale vorm passend in die rapportage sheet.

Ik zou zo 1-2-3 geen manier weten dit op korte termijn anders te doen.
Vooral niet omdat het allemaal online gedaan moet worden, vandaar de keuze voor Google formulieren en Google spreadsheets.
Ook de coördinatoren die alles aansturen werken online en de bedoeling is dat ze niet de hoofdformulieren kunnen vernielen.

In Excel had ik het allemaal prima voor elkaar, alles werkte.
Alleen had ik daar niet die extra stappen nodig omdat alles vanuit Excel kon met alleen de opslag van alle formulieren (met automatisch een unieke naam) die eerst lokaal is, komt dan wel in de Cloud.
Maar niet iedereen die er bij betrokken was bleek Excel te hebben en Excel is een beetje te kostbaar om vrijwilligers te dwingen aan te schaffen.

Vandaar deze keuze.

Jouw voorstel klinkt leuk, maar ik zou echt niet weten hoe dat te realiseren

Maar ik ga dus verder kijken naar oplossingen.

Ook jou wil ik bedanken!

[lobo405]

Toch nog heel even een korte reactie.
Alles op 1 ding na ging in het originele script goed.
Dit was;
spreadsheet.getRange('\'Formulierreacties 2\'!3:3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true);

Alleen dat kleine stukje ->

'!3:3'

>- is het probleem want dat moet dus een dynamisch stuk worden.
Afhankelijk welke row in het overzicht formulier (Formulierreacties 2) is geselecteerd moet hier een waarde staan (te transponeren rij).

Ik ga dus in eerste instantie weer even uit van het originele script

[ Voor 5% gewijzigd door lobo405 op 12-02-2024 20:51 ]

[lobo405]

dixet schreef op maandag 12 februari 2024 @ 17:08:
Aan de andere kant, je hebt nog tot donderdag om het op deze manier te proberen

dixet, misschien dat je nog 1 keer een poging wilt wagen.
Ik heb me rot gezocht op internet maar moet bekennen dat mijn kennis/begrip het niet redt.
Zou jij misschien toch nog een keer met jouw grotere ervaring kunnen helpen

Ik heb een stuk van jouw script ingepast in hetgeen ik al had en dat werkt bijna helemaal goed.
Alleen de bron werkt niet qua inpassen in het copy/paste gebeuren.
Het lukt mij niet om de variabele bronRij (die dus echt de juiste rij aangeeft) op de juiste manier in de regel die het transponeren uitvoert te krijgen.
d.w.z. dit stuk gaat fout :

In de scriptregel hieronder moet dat stukje !3:3 worden vervangen door de juiste gegevens zodat in plaats van die 2 keer een 3 , 2 keer de variabele bronRij wordt gebruikt (die !3:3 is de bron van dus altijd rij 3 t/m 3)
Dus op de een of andere manier moet in plaats van die vaste waardes 3 de waarde van de variabele bronRij komen

spreadsheet.getRange('\'Formulierreacties 2\'!3:3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true);

[dixet]

lobo405 schreef op dinsdag 13 februari 2024 @ 21:30:
[...]

In de scriptregel hieronder moet dat stukje !3:3 worden vervangen door de juiste gegevens zodat in plaats van die 2 keer een 3 , 2 keer de variabele bronRij wordt gebruikt (die !3:3 is de bron van dus altijd rij 3 t/m 3)
Dus op de een of andere manier moet in plaats van die vaste waardes 3 de waarde van de variabele bronRij komen

spreadsheet.getRange('\'Formulierreacties 2\'!3:3').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true);

Dat zou op deze manier moeten werken:

JavaScript:

spreadsheet.getRange('\'Formulierreacties 2\'!'+bronRij+':'+bronRij).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, true);

[lobo405]

dixet schreef op dinsdag 13 februari 2024 @ 21:37:
[...]

Dat zou op deze manier moeten werken:

Geweldig dixet, het werkt nu.
Nu zie ik ook wat ik verkeerd had gedaan.
Ik had bijna hetzelfde, maar de quotes stonden niet goed (verkeerd en te weinig)

Je bent een kanjer , nu is het voor morgenavond klaar

Groet,
Bob