script sort first column with data (blank rows)

[Arne Ballegeer]

How can I sort the first column alphabetically via script while keeping the format of the document the same? In other words, the data to the right of the names must also be sorted. It's for an attendance list. I can't figure it out. Hope my question is clear.

Anyone a tip?

thks

[Clark Lind]

The main problem is you have combined your data, with the visualization of the data. That being said, what I would probably do is add a column to the left of A, shifting everything to the right. Then in the new column, I would give every row a value. 
So Column A would look like:
Stewart1

Stewart2

Stewart3

Astaire1

Astaire2

Astaire3
etc

Then I would select the whole sheet and sort on Col A. Then Hide the column. Keep it simple :)

[Arne Ballegeer]

Thks for the tip! I'll give it a go!

grt

Arne

Op woensdag 13 oktober 2021 om 23:02:56 UTC+2 schreef cwl...@gmail.com:

[Arne Ballegeer]

Can you give me a hint? I don't find the logic to copy the name twice in column A. I'm probably looking too far.
Here's a short vb attached how I see it, but my data from the sheet are all separate arrays. I think I should merge all those arrays and then use my vb script? If yes, how do I concatenate so many arrays? I hope I'm a bit clear ;)

Op donderdag 14 oktober 2021 om 05:17:47 UTC+2 schreef Arne Ballegeer:

[Clark Lind]

Because you want to keep the "sections" together, treat them like a subset. So instead of 'Murphy' x3, use 'Murphy1', 'Murphy2', 'Murphy3'. Then it will keep the subset in the correct order also. 

So,   const naam = ['Murphy1', 'Murphy2', 'Murphy3', 'Davis1', 'Davis2' ..etc]

[Clark Lind]

I was trying to keep it pretty simple, but there are probably more elegant ways to do this that would make more sense. Let me give it some thought.

[Arne Ballegeer]

That would be my next step, adding the numbers to the names. I was just testing how to get the names in column A 3 times. I'm still learning javascript, so forgive me if I say stupid things.. ;)
Of course if you could see a better scenario for what i want, be sure to let me know.
Thanks for the help!

Op zaterdag 16 oktober 2021 om 15:12:23 UTC+2 schreef cwl...@gmail.com:

[Clark Lind]

Arne, your coding skills (from what little you shared) are already well beyond mine. I know enough to be dangerous! This is what I was working on. You can probably clean it up and refactor it:

function sortNamedRanges() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();

  const ws = ss.getSheetByName('SHEETNAME'); //

  const dataRange = ws.getRange(2, 1, ws.getLastRow() - 1, 1);

  //"flat" stolen-borrowed from: https://1loc.dev/ (great site!  1loc = one line of code)

  const flat = (arr) => arr.reduce((a, b) => (Array.isArray(b) ? [...a, ...flat(b)] : [...a, b]), []);

  let names = flat(dataRange.getValues());

  const myFunction = (value, index, array) => {  //I'm trying to learn to use this style

    if (value) {

      return value + 1;

    }

    if (!value && !array[index + 1] && array[index - 1]) {

      return array[index] = array[index - 1] + 2

    }

    if (!value && array[index + 1] && array[index - 1]) {

      return array[index] = array[index - 2] + 3

    }

  }

  const names2 = names.map(myFunction);

  console.log(names2)

}

--
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/a23R9ONw788/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/6a051f16-7234-41b4-a8aa-53faf6060cdcn%40googlegroups.com.

[Arne Ballegeer]

Thanks! I'll check it out and keep you posted. Now I will have an aperitif with my wife. ;) Enjoy your weekend!

Op zaterdag 16 oktober 2021 om 17:54:00 UTC+2 schreef cwl...@gmail.com:

[Arne Ballegeer]

good morning

Sorry to bother you again.
Your code works, but I only have some problems with the last row. He only adds the last name once. If I then add a new name at the bottom to sort, it causes problems. Already tried to modify sheet.getLastRow() with no desired effect.

Tip?

Btw, thks for this tip -> https://1loc.dev/ , great side

function sortNamedRanges() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const maanden = ss.getNumSheets();

for (let i = 0; i < maanden; i++) {

const sheet = ss.getSheets()[i];

const dataRange = sheet.getRange(2, 2, sheet.getLastRow() - 1, 1);

//"flat" stolen-borrowed from: https://1loc.dev/ (great site! 1loc = one line of code)

const flat = (arr) => arr.flat();

let names = flat(dataRange.getValues());

const myFunction = (value, index, array) => { //I'm trying to learn to use this style

if (value) {

return value + 1;

}

if (!value && !array[index + 1] && array[index - 1]) {

return array[index] = array[index - 1] + 2

}

if (!value && array[index + 1] && array[index - 1]) {

return array[index] = array[index - 2] + 3

}

}

const names2 = names.map(myFunction).map(x => [x]); // 2e map dient om array om te zetten naar een 2D array

console.log(names2)

const invoegen_kolomA = sheet.getRange(2, 1, names2.length).setValues(names2);

sheet.hideColumns(1);

}

}

Op zaterdag 16 oktober 2021 om 18:12:31 UTC+2 schreef Arne Ballegeer:

[Clark Lind]

When you add the name, ensure there are three rows above it: 

If you do have three rows, then you may have to share your code or (sanitized) sheet so I can take a look at it.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/8019b6a8-107d-4d50-8598-de4315e81783n%40googlegroups.com.

[Arne Ballegeer]

I will rethink the format for this attendance list. One thing leads to another and causes problems. Definitely thanks for your help! And I will definitely call back when I need help again.. ;)

Op zaterdag 23 oktober 2021 om 16:51:22 UTC+2 schreef cwl...@gmail.com: