How to get `Nth` to return `Blank` if index is not found

Connor_McCormick1 · July 29, 2021, 2:52pm

In my actual use case that won’t work. Actual formula is this horrible thing:

RunActions(
  [All Outputs].AddRow(
    [All Outputs].[CSV Source], thisRow,
    [All Outputs].Header, true,
    [All Outputs].[Column 1], thisRow.Table.Nth(1).Nth(1),
    [All Outputs].[Column 2], thisRow.Table.Nth(1).Nth(2),
    [All Outputs].[Column 3], thisRow.Table.Nth(1).Nth(3),
    [All Outputs].[Column 4], thisRow.Table.Nth(1).Nth(4),
    [All Outputs].[Column 5], thisRow.Table.Nth(1).Nth(5),
    [All Outputs].[Column 6], thisRow.Table.Nth(1).Nth(6),
    [All Outputs].[Column 7], thisRow.Table.Nth(1).Nth(7),
    [All Outputs].[Column 8], thisRow.Table.Nth(1).Nth(8),
    [All Outputs].[Column 9], thisRow.Table.Nth(1).Nth(9),
    [All Outputs].[Column 10], thisRow.Table.Nth(1).Nth(10),
    [All Outputs].[Column 11], thisRow.Table.Nth(1).Nth(11),
    [All Outputs].[Column 12], thisRow.Table.Nth(1).Nth(12),
    [All Outputs].[Column 13], thisRow.Table.Nth(1).Nth(13),
    [All Outputs].[Column 14], thisRow.Table.Nth(1).Nth(14),
    [All Outputs].[Column 15], thisRow.Table.Nth(1).Nth(15),
    [All Outputs].[Column 16], thisRow.Table.Nth(1).Nth(16),
    [All Outputs].[Column 17], thisRow.Table.Nth(1).Nth(17),
    [All Outputs].[Column 18], thisRow.Table.Nth(1).Nth(18),
    [All Outputs].[Column 19], thisRow.Table.Nth(1).Nth(19),
    [All Outputs].[Column 20], thisRow.Table.Nth(1).Nth(20),
    [All Outputs].[Column 21], thisRow.Table.Nth(1).Nth(21),
    [All Outputs].[Column 22], thisRow.Table.Nth(1).Nth(22),
    [All Outputs].[Column 23], thisRow.Table.Nth(1).Nth(23),
    [All Outputs].[Column 24], thisRow.Table.Nth(1).Nth(24),
    [All Outputs].[Column 25], thisRow.Table.Nth(1).Nth(25),
    [All Outputs].[Column 26], thisRow.Table.Nth(1).Nth(26),
    [All Outputs].[Column 27], thisRow.Table.Nth(1).Nth(27),
    [All Outputs].[Column 28], thisRow.Table.Nth(1).Nth(28),
    [All Outputs].[Column 29], thisRow.Table.Nth(1).Nth(29),
    [All Outputs].[Column 30], thisRow.Table.Nth(1).Nth(30),
    [All Outputs].[Column 31], thisRow.Table.Nth(1).Nth(31),
    [All Outputs].[Column 32], thisRow.Table.Nth(1).Nth(32),
    [All Outputs].[Column 33], thisRow.Table.Nth(1).Nth(33),
    [All Outputs].[Column 34], thisRow.Table.Nth(1).Nth(34),
    [All Outputs].[Column 35], thisRow.Table.Nth(1).Nth(35),
    [All Outputs].[Column 36], thisRow.Table.Nth(1).Nth(36),
    [All Outputs].[Column 37], thisRow.Table.Nth(1).Nth(37),
    [All Outputs].[Column 38], thisRow.Table.Nth(1).Nth(38),
    [All Outputs].[Column 39], thisRow.Table.Nth(1).Nth(39),
    [All Outputs].[Column 40], thisRow.Table.Nth(1).Nth(40),
    [All Outputs].[Column 41], thisRow.Table.Nth(1).Nth(41),
    [All Outputs].[Column 42], thisRow.Table.Nth(1).Nth(42),
    [All Outputs].[Column 43], thisRow.Table.Nth(1).Nth(43),
    [All Outputs].[Column 44], thisRow.Table.Nth(1).Nth(44),
    [All Outputs].[Column 45], thisRow.Table.Nth(1).Nth(45),
    [All Outputs].[Column 46], thisRow.Table.Nth(1).Nth(46),
    [All Outputs].[Column 47], thisRow.Table.Nth(1).Nth(47),
    [All Outputs].[Column 48], thisRow.Table.Nth(1).Nth(48),
    [All Outputs].[Column 49], thisRow.Table.Nth(1).Nth(49),
    [All Outputs].[Column 50], thisRow.Table.Nth(1).Nth(50),
    [All Outputs].[Column 51], thisRow.Table.Nth(1).Nth(51),
    [All Outputs].[Column 52], thisRow.Table.Nth(1).Nth(52),
    [All Outputs].[Column 53], thisRow.Table.Nth(1).Nth(53),
    [All Outputs].[Column 54], thisRow.Table.Nth(1).Nth(54),
    [All Outputs].[Column 55], thisRow.Table.Nth(1).Nth(55),
    [All Outputs].[Column 56], thisRow.Table.Nth(1).Nth(56),
    [All Outputs].[Column 57], thisRow.Table.Nth(1).Nth(57),
    [All Outputs].[Column 58], thisRow.Table.Nth(1).Nth(58),
    [All Outputs].[Column 59], thisRow.Table.Nth(1).Nth(59),
    [All Outputs].[Column 60], thisRow.Table.Nth(1).Nth(60),
    [All Outputs].[Column 61], thisRow.Table.Nth(1).Nth(61),
    [All Outputs].[Column 62], thisRow.Table.Nth(1).Nth(62),
    [All Outputs].[Column 63], thisRow.Table.Nth(1).Nth(63),
    [All Outputs].[Column 64], thisRow.Table.Nth(1).Nth(64),
    [All Outputs].[Column 65], thisRow.Table.Nth(1).Nth(65),
    [All Outputs].[Column 66], thisRow.Table.Nth(1).Nth(66),
    [All Outputs].[Column 67], thisRow.Table.Nth(1).Nth(67),
    [All Outputs].[Column 68], thisRow.Table.Nth(1).Nth(68),
    [All Outputs].[Column 69], thisRow.Table.Nth(1).Nth(69),
    [All Outputs].[Column 70], thisRow.Table.Nth(1).Nth(70),
    [All Outputs].[Column 71], thisRow.Table.Nth(1).Nth(71),
    [All Outputs].[Column 72], thisRow.Table.Nth(1).Nth(72),
    [All Outputs].[Column 73], thisRow.Table.Nth(1).Nth(73),
    [All Outputs].[Column 74], thisRow.Table.Nth(1).Nth(74),
    [All Outputs].[Column 75], thisRow.Table.Nth(1).Nth(75),
    [All Outputs].[Column 76], thisRow.Table.Nth(1).Nth(76),
    [All Outputs].[Column 77], thisRow.Table.Nth(1).Nth(77),
    [All Outputs].[Column 78], thisRow.Table.Nth(1).Nth(78),
    [All Outputs].[Column 79], thisRow.Table.Nth(1).Nth(79),
    [All Outputs].[Column 80], thisRow.Table.Nth(1).Nth(80),
    [All Outputs].[Column 81], thisRow.Table.Nth(1).Nth(81),
    [All Outputs].[Column 82], thisRow.Table.Nth(1).Nth(82),
    [All Outputs].[Column 83], thisRow.Table.Nth(1).Nth(83),
    [All Outputs].[Column 84], thisRow.Table.Nth(1).Nth(84),
    [All Outputs].[Column 85], thisRow.Table.Nth(1).Nth(85),
    [All Outputs].[Column 86], thisRow.Table.Nth(1).Nth(86),
    [All Outputs].[Column 87], thisRow.Table.Nth(1).Nth(87),
    [All Outputs].[Column 88], thisRow.Table.Nth(1).Nth(88),
    [All Outputs].[Column 89], thisRow.Table.Nth(1).Nth(89),
    [All Outputs].[Column 90], thisRow.Table.Nth(1).Nth(90),
    [All Outputs].[Column 91], thisRow.Table.Nth(1).Nth(91),
    [All Outputs].[Column 92], thisRow.Table.Nth(1).Nth(92),
    [All Outputs].[Column 93], thisRow.Table.Nth(1).Nth(93),
    [All Outputs].[Column 94], thisRow.Table.Nth(1).Nth(94),
    [All Outputs].[Column 95], thisRow.Table.Nth(1).Nth(95),
    [All Outputs].[Column 96], thisRow.Table.Nth(1).Nth(96),
    [All Outputs].[Column 97], thisRow.Table.Nth(1).Nth(97),
    [All Outputs].[Column 98], thisRow.Table.Nth(1).Nth(98),
    [All Outputs].[Column 99], thisRow.Table.Nth(1).Nth(99),
    [All Outputs].[Column 100], thisRow.Table.Nth(1).Nth(100),
    [All Outputs].[Column 101], thisRow.Table.Nth(1).Nth(101),
    [All Outputs].[Column 102], thisRow.Table.Nth(1).Nth(102),
    [All Outputs].[Column 103], thisRow.Table.Nth(1).Nth(103),
    [All Outputs].[Column 104], thisRow.Table.Nth(1).Nth(104),
    [All Outputs].[Column 105], thisRow.Table.Nth(1).Nth(105),
    [All Outputs].[Column 106], thisRow.Table.Nth(1).Nth(106),
    [All Outputs].[Column 107], thisRow.Table.Nth(1).Nth(107),
    [All Outputs].[Column 108], thisRow.Table.Nth(1).Nth(108),
    [All Outputs].[Column 109], thisRow.Table.Nth(1).Nth(109),
    [All Outputs].[Column 110], thisRow.Table.Nth(1).Nth(110),
    [All Outputs].[Column 111], thisRow.Table.Nth(1).Nth(111),
    [All Outputs].[Column 112], thisRow.Table.Nth(1).Nth(112),
    [All Outputs].[Column 113], thisRow.Table.Nth(1).Nth(113),
    [All Outputs].[Column 114], thisRow.Table.Nth(1).Nth(114),
    [All Outputs].[Column 115], thisRow.Table.Nth(1).Nth(115),
    [All Outputs].[Column 116], thisRow.Table.Nth(1).Nth(116),
    [All Outputs].[Column 117], thisRow.Table.Nth(1).Nth(117),
    [All Outputs].[Column 118], thisRow.Table.Nth(1).Nth(118),
    [All Outputs].[Column 119], thisRow.Table.Nth(1).Nth(119),
    [All Outputs].[Column 120], thisRow.Table.Nth(1).Nth(120),
    [All Outputs].[Column 121], thisRow.Table.Nth(1).Nth(121),
    [All Outputs].[Column 122], thisRow.Table.Nth(1).Nth(122),
    [All Outputs].[Column 123], thisRow.Table.Nth(1).Nth(123),
    [All Outputs].[Column 124], thisRow.Table.Nth(1).Nth(124),
    [All Outputs].[Column 125], thisRow.Table.Nth(1).Nth(125),
    [All Outputs].[Column 126], thisRow.Table.Nth(1).Nth(126),
    [All Outputs].[Column 127], thisRow.Table.Nth(1).Nth(127),
    [All Outputs].[Column 128], thisRow.Table.Nth(1).Nth(128),
    [All Outputs].[Column 129], thisRow.Table.Nth(1).Nth(129),
    [All Outputs].[Column 130], thisRow.Table.Nth(1).Nth(130)
  ),
  thisRow.Table.Slice(2).FormulaMap(
    [All Outputs].AddRow(
      [All Outputs].[CSV Source], thisRow,
      [All Outputs].[Column 1], thisRow.Table.Nth(1),
      [All Outputs].[Column 2], thisRow.Table.Nth(2),
      [All Outputs].[Column 3], thisRow.Table.Nth(3),
      [All Outputs].[Column 4], thisRow.Table.Nth(4),
      [All Outputs].[Column 5], thisRow.Table.Nth(5),
      [All Outputs].[Column 6], thisRow.Table.Nth(6),
      [All Outputs].[Column 7], thisRow.Table.Nth(7),
      [All Outputs].[Column 8], thisRow.Table.Nth(8),
      [All Outputs].[Column 9], thisRow.Table.Nth(9),
      [All Outputs].[Column 10], thisRow.Table.Nth(10),
      [All Outputs].[Column 11], thisRow.Table.Nth(11),
      [All Outputs].[Column 12], thisRow.Table.Nth(12),
      [All Outputs].[Column 13], thisRow.Table.Nth(13),
      [All Outputs].[Column 14], thisRow.Table.Nth(14),
      [All Outputs].[Column 15], thisRow.Table.Nth(15),
      [All Outputs].[Column 16], thisRow.Table.Nth(16),
      [All Outputs].[Column 17], thisRow.Table.Nth(17),
      [All Outputs].[Column 18], thisRow.Table.Nth(18),
      [All Outputs].[Column 19], thisRow.Table.Nth(19),
      [All Outputs].[Column 20], thisRow.Table.Nth(20),
      [All Outputs].[Column 21], thisRow.Table.Nth(21),
      [All Outputs].[Column 22], thisRow.Table.Nth(22),
      [All Outputs].[Column 23], thisRow.Table.Nth(23),
      [All Outputs].[Column 24], thisRow.Table.Nth(24),
      [All Outputs].[Column 25], thisRow.Table.Nth(25),
      [All Outputs].[Column 26], thisRow.Table.Nth(26),
      [All Outputs].[Column 27], thisRow.Table.Nth(27),
      [All Outputs].[Column 28], thisRow.Table.Nth(28),
      [All Outputs].[Column 29], thisRow.Table.Nth(29),
      [All Outputs].[Column 30], thisRow.Table.Nth(30),
      [All Outputs].[Column 31], thisRow.Table.Nth(31),
      [All Outputs].[Column 32], thisRow.Table.Nth(32),
      [All Outputs].[Column 33], thisRow.Table.Nth(33),
      [All Outputs].[Column 34], thisRow.Table.Nth(34),
      [All Outputs].[Column 35], thisRow.Table.Nth(35),
      [All Outputs].[Column 36], thisRow.Table.Nth(36),
      [All Outputs].[Column 37], thisRow.Table.Nth(37),
      [All Outputs].[Column 38], thisRow.Table.Nth(38),
      [All Outputs].[Column 39], thisRow.Table.Nth(39),
      [All Outputs].[Column 40], thisRow.Table.Nth(40),
      [All Outputs].[Column 41], thisRow.Table.Nth(41),
      [All Outputs].[Column 42], thisRow.Table.Nth(42),
      [All Outputs].[Column 43], thisRow.Table.Nth(43),
      [All Outputs].[Column 44], thisRow.Table.Nth(44),
      [All Outputs].[Column 45], thisRow.Table.Nth(45),
      [All Outputs].[Column 46], thisRow.Table.Nth(46),
      [All Outputs].[Column 47], thisRow.Table.Nth(47),
      [All Outputs].[Column 48], thisRow.Table.Nth(48),
      [All Outputs].[Column 49], thisRow.Table.Nth(49),
      [All Outputs].[Column 50], thisRow.Table.Nth(50),
      [All Outputs].[Column 51], thisRow.Table.Nth(51),
      [All Outputs].[Column 52], thisRow.Table.Nth(52),
      [All Outputs].[Column 53], thisRow.Table.Nth(53),
      [All Outputs].[Column 54], thisRow.Table.Nth(54),
      [All Outputs].[Column 55], thisRow.Table.Nth(55),
      [All Outputs].[Column 56], thisRow.Table.Nth(56),
      [All Outputs].[Column 57], thisRow.Table.Nth(57),
      [All Outputs].[Column 58], thisRow.Table.Nth(58),
      [All Outputs].[Column 59], thisRow.Table.Nth(59),
      [All Outputs].[Column 60], thisRow.Table.Nth(60),
      [All Outputs].[Column 61], thisRow.Table.Nth(61),
      [All Outputs].[Column 62], thisRow.Table.Nth(62),
      [All Outputs].[Column 63], thisRow.Table.Nth(63),
      [All Outputs].[Column 64], thisRow.Table.Nth(64),
      [All Outputs].[Column 65], thisRow.Table.Nth(65),
      [All Outputs].[Column 66], thisRow.Table.Nth(66),
      [All Outputs].[Column 67], thisRow.Table.Nth(67),
      [All Outputs].[Column 68], thisRow.Table.Nth(68),
      [All Outputs].[Column 69], thisRow.Table.Nth(69),
      [All Outputs].[Column 70], thisRow.Table.Nth(70),
      [All Outputs].[Column 71], thisRow.Table.Nth(71),
      [All Outputs].[Column 72], thisRow.Table.Nth(72),
      [All Outputs].[Column 73], thisRow.Table.Nth(73),
      [All Outputs].[Column 74], thisRow.Table.Nth(74),
      [All Outputs].[Column 75], thisRow.Table.Nth(75),
      [All Outputs].[Column 76], thisRow.Table.Nth(76),
      [All Outputs].[Column 77], thisRow.Table.Nth(77),
      [All Outputs].[Column 78], thisRow.Table.Nth(78),
      [All Outputs].[Column 79], thisRow.Table.Nth(79),
      [All Outputs].[Column 80], thisRow.Table.Nth(80),
      [All Outputs].[Column 81], thisRow.Table.Nth(81),
      [All Outputs].[Column 82], thisRow.Table.Nth(82),
      [All Outputs].[Column 83], thisRow.Table.Nth(83),
      [All Outputs].[Column 84], thisRow.Table.Nth(84),
      [All Outputs].[Column 85], thisRow.Table.Nth(85),
      [All Outputs].[Column 86], thisRow.Table.Nth(86),
      [All Outputs].[Column 87], thisRow.Table.Nth(87),
      [All Outputs].[Column 88], thisRow.Table.Nth(88),
      [All Outputs].[Column 89], thisRow.Table.Nth(89),
      [All Outputs].[Column 90], thisRow.Table.Nth(90),
      [All Outputs].[Column 91], thisRow.Table.Nth(91),
      [All Outputs].[Column 92], thisRow.Table.Nth(92),
      [All Outputs].[Column 93], thisRow.Table.Nth(93),
      [All Outputs].[Column 94], thisRow.Table.Nth(94),
      [All Outputs].[Column 95], thisRow.Table.Nth(95),
      [All Outputs].[Column 96], thisRow.Table.Nth(96),
      [All Outputs].[Column 97], thisRow.Table.Nth(97),
      [All Outputs].[Column 98], thisRow.Table.Nth(98),
      [All Outputs].[Column 99], thisRow.Table.Nth(99),
      [All Outputs].[Column 100], thisRow.Table.Nth(100),
      [All Outputs].[Column 101], thisRow.Table.Nth(101),
      [All Outputs].[Column 102], thisRow.Table.Nth(102),
      [All Outputs].[Column 103], thisRow.Table.Nth(103),
      [All Outputs].[Column 104], thisRow.Table.Nth(104),
      [All Outputs].[Column 105], thisRow.Table.Nth(105),
      [All Outputs].[Column 106], thisRow.Table.Nth(106),
      [All Outputs].[Column 107], thisRow.Table.Nth(107),
      [All Outputs].[Column 108], thisRow.Table.Nth(108),
      [All Outputs].[Column 109], thisRow.Table.Nth(109),
      [All Outputs].[Column 110], thisRow.Table.Nth(110),
      [All Outputs].[Column 111], thisRow.Table.Nth(111),
      [All Outputs].[Column 112], thisRow.Table.Nth(112),
      [All Outputs].[Column 113], thisRow.Table.Nth(113),
      [All Outputs].[Column 114], thisRow.Table.Nth(114),
      [All Outputs].[Column 115], thisRow.Table.Nth(115),
      [All Outputs].[Column 116], thisRow.Table.Nth(116),
      [All Outputs].[Column 117], thisRow.Table.Nth(117),
      [All Outputs].[Column 118], thisRow.Table.Nth(118),
      [All Outputs].[Column 119], thisRow.Table.Nth(119),
      [All Outputs].[Column 120], thisRow.Table.Nth(120),
      [All Outputs].[Column 121], thisRow.Table.Nth(121),
      [All Outputs].[Column 122], thisRow.Table.Nth(122),
      [All Outputs].[Column 123], thisRow.Table.Nth(123),
      [All Outputs].[Column 124], thisRow.Table.Nth(124),
      [All Outputs].[Column 125], thisRow.Table.Nth(125),
      [All Outputs].[Column 126], thisRow.Table.Nth(126),
      [All Outputs].[Column 127], thisRow.Table.Nth(127),
      [All Outputs].[Column 128], thisRow.Table.Nth(128),
      [All Outputs].[Column 129], thisRow.Table.Nth(129),
      [All Outputs].[Column 130], thisRow.Table.Nth(130)
    )
  )
)

As you can no doubt tell, a related issue is that I don’t have programmatic control over the number of columns in a table.

Paul_Danyliuk · July 29, 2021, 2:56pm

Ah, the Excel export…

Well, if I had to create such a formula, I would descend to black magic as I call it, and construct an action programmatically instead.

Connor_McCormick1 · July 29, 2021, 2:58pm

Ooh, tell me about that

Paul_Danyliuk · July 29, 2021, 2:59pm

Give me 10 min to finish my lunch and I’ll record a quick video for ya

Connor_McCormick1 · July 29, 2021, 3:03pm

Worthy of note, this isn’t exactly an excel export, it’s a csv conversion.

But I still want to learn about black magic

Paul_Danyliuk · July 29, 2021, 4:02pm

There you go:

Connor_McCormick1 · July 29, 2021, 4:57pm

Oh this is sick. Black magic should be a native feature of the language. Very lispy

Ok follow up question: is there a way to formulaicly access column names and or create new columns?

Paul_Danyliuk · July 29, 2021, 6:14pm

No. This is not the Coda way of organizing the data.

Connor_McCormick1 · July 29, 2021, 6:16pm

Is there any sort of hacky approach?

Paul_Danyliuk · July 30, 2021, 11:59am

Well, the only approach that comes to mind is this: make a button, reference the columns there, and out of action code extract column names using RegexExtract. However this won’t automatically pick up column name changes — you have to “touch” the button action (e.g. open the formula editor and close it) so that names are refreshed

Scott_Collier-Weir · July 30, 2021, 3:41pm

What was the regexextract formula you used to isolate the column names amidst that text?

Connor_McCormick1 · July 30, 2021, 4:42pm

Here’s the javascript that Paul uses to get the column ids:

data = []
temp1.querySelectorAll('.columnHeaderRoot[data-column-id]').forEach(x => data.push(x.getAttribute('data-column-id')))

The way that you get temp1 as a variable is you use the select element tool on a column, then scroll up until you find a section called data-reference-type: "grid", then right click to copy that as global variable. It will automatically be named temp1

Connor_McCormick1 · July 30, 2021, 5:05pm

Can you drop a link to your doc?

Topic		Replies	Views
Referencing the cell above Tips and Hacks	43	22370	November 4, 2023
😬 What was hard for you to discover in Coda?	40	7031	October 14, 2021
How do i end my row with sum off all previous columns?	18	4143	March 5, 2022
Visual Position of the row in table Tips and Hacks	33	11652	June 26, 2023
RIGHT() formula help and already exists?	23	2950	May 26, 2019

How to get `Nth` to return `Blank` if index is not found

Related topics