Re: [Apps-Script] google-apps-script-community@googlegroups.com

[Andrew Roberts]

You could create a new row for each cell returned by the form with something like (as demoed in this sheet):

function reorganise() {

const ss = SpreadsheetApp.getActive()

let data = ss.getSheetByName('Form responses 1').getDataRange().getValues()

const headers = data.shift() // Remove header

let results = []

const students = headers.slice(2)

data.forEach(row => {

let timestamp, teacher, present, student

row.forEach((cell, index) => {

if (index === 0) {

timestamp = row[index]

} else if (index === 1) {

teacher = row[index]

} else {

student = students[index - 2]

present = row[index]

results.push([student, timestamp, teacher, present])

}

})

})

const resultsSheet = ss.getSheetByName('Reorganised')

resultsSheet.getRange('A1:D' + results.length).setValues(results)

}

On Tue, 13 Sept 2022 at 09:10, Sophie Gentis <sge...@escaet.fr> wrote:

Hi everyone,

I really don't know much about coding. I work for a school and I have a Google Form to call the register whose answers arrive in a Google Sheets in this format:

Timestamp,I am...,[Student1],[Student2],[Student3],[Student4],[Student5],[Student6],[Student7],[Student8],[Student9],[Student10]
09/11/2022 16:33:32,Teacher1, Away, Away,Late,,,,,,,
09/12/2022 16:34:32,Teacher2,,,, Away,,,, Away,,

I would like the responses to come in this format:

STUDENT,TIMESTAMP,TEACHER,TYPE
[Student1],09/11/2022 16:33:32,Teacher1,Away
[Student2],09/11/2022 16:33:32,Teacher1,Away
[Student3],11/09/2022 16:33:32,Teacher1,Late
[Student4], 09/12/2022 16:34:32, Teacher2,Away
[Student8], 09/12/2022 16:34:32, Teacher2,Away

I guess I can only achieve this by doing a macro in Apps Script. Could you help me ?

Thanks a lot !

--
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/be051dd9-108b-446c-a15a-d6aac4646305n%40googlegroups.com.

[Laurie J. Nason]

If you are just wanting a dynamic transpose without a script, then check out Ben Collins’ Flatten Advanced Example here - https://www.benlcollins.com/spreadsheets/flatten-function/

------ Original Message ------

From "Andrew Roberts" <and...@roberts.net>

To google-apps-sc...@googlegroups.com

Date 14/09/2022 07:36:20

Subject Re: [Apps-Script] google-apps-sc...@googlegroups.com

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAN1QPJQaPEe_pgKff5JzNGZnt6dtHc%2B7xc_Skvp6o4ksW-deUg%40mail.gmail.com.