Javascript to isolate a string from a string turned array
First I'm using "Excel: Extract cell data" to get a range (single row) of data. This is generating a JSON array but from what I've gathered it can be treated as a string when applied to javascript.
I'm then attempting to use Field Formulas to pull out specific values from a csv string.
The string I have in testing is "[["product 1,,,,100,200,A123"]]". I apply .replace a few times to remove the brackets and quotes. I'm then trying to use a split to turn the string into an array and then a slice to get out the specific strings I want into their own fields.
The above formula accurately produces the intended result ("A123").
However the formula above here is producing the entire string rather than just the first index ("product 1,,,,100,200,A123"). Am I doing something wrong?
Is there a better way I can be doing this that doesn't require 7 different actions to get all the values from the Excel file in that row?
Best Answer
-
I can't speak to if there is a better way to do this without requiring 7 different actions, but I can try to explain what is going on with your formula.
Javascript's split method actually returns an array of strings while the slice method returns a copy of a portion of an array. I suspect your first javascript formula actually returns an array with a single string containing "A123" rather than just the string "A123".
For your second formula, you're slicing at index 0, but you don't actually specify an end so it gets all of the elements in the array starting at index 0 (which is all of the elements in the array).
If you only want a the first element from the array you can do
var prod = item.split(",").slice(0, 1)
.Don't forget that
result
is actually going to be an array containing an element so i'm guessing you might need to get just that element from the array which can be done using[0]
like this:var prod = item.split(",").slice(0, 1)[0]
.6
Answers
-
@Adam_636382 A potential alternative approach... Have you looked into the action, Excel: Look up data in columns?
This can return up to 5 cells mapped to field names of your choice. If you need 7 fields, you would need 2 of these actions, but it might get you want you need without needing to get into JavaScript formulas at all.
2