Hey, is there a way to have all dates in between a start and end date in an array. I want to use it for a lookup column. To find all “projects” which are ongoing in the actual week. Thanks
this just works in google sheets. I need that in a table I have in glide tables! How to solve it here?
This is something I want to try but haven’t yet. Currently I do a much more complicated process with VLOOKUPS in a google sheet to get a value that I can use to to build relations.
What you need is probably something a lot simpler. If you can set a start date value somewhere, then you can bring it into a table with a single value column. Then you can add a math column to add 7 days. Then you can use that single value column and math column in an IF column to compare to the date in each row. If it fits within the range, then return a value such as ‘true’. You can use that IF column value to set a filter on your list.
so there is really now solution bevor i try to build something complicated?
As you see in the screenshot, I have a start and End date… I just need like in the last column all dates in between… the two dates you see are placed manually.
Dates are very tricky to work with. Especially if you are trying to use them for relations. You have to consider any underlying times within that you may not see. The formatted version may look the same but, it might not be.
About the absolute simplest way I can think to do it would be to add two Math columns that convert each date into numbers.
Then pass those numbers into a javascript column to calculate and return a range.
function numberRange (start, end) {
return new Array(end - start+1).fill().map((d, i) => i + start);
}
try {
return numberRange(p1,p2).toString();
} catch {
}
Finally Split that result into an array using a Split Text column and use the numeric values for lookups, relations, or whatever else you want.
3 Likes
Hey @Jeff_Hager , this is really helpful. How fast does the JS create ranges between the numbers?
I just wrote up the script (* copied from a google search *), so I haven’t tested it much. I’m not using it in any projects, but I do have a use case I’ll definitely try. So, I’ve only ran it on the 8 rows you see in the screenshot.
Sounds great. The split array just shows the first value. How to do that? I just have a , as a separator… do I have to do something else?
Best
Andi
Can you show a screen shot of what you have?
thanks for asking. I found the failure. Now it works!
1 Like
I am not good with Javascripts at all, so I tried asking ChatGPT to write me a script. When I tried to use it in Glide, it didn’t work. Can you tell me what’s the issue in this script that ChatGPT has written?
What are the values you are using for p1 and p2?
I was just testing this script so I created 2 basic numbers columns. When I added Javascript column, I pasted the Chat GPT code and replaced P1 with Number 1 and P2 with Number 2.
Even though Jeff’s script works perfectly, I just wanted to understand the issue with ChatGPTs script. Might get great ideas using Javascript if I know how it works 
The JavaScript column cannot return an array, which is what your code tries to do.
Try changing your return statement to:
return range.join(',');
2 Likes
There a few things I would modify in that script. First, there is nothing calling the function. Code in a javascript column will be ran when Glide calls upon it, but if you have all of your code inside of a function, then it won’t run until you call it from outside of the function.
One thing I would add is a return getRange(p1, p2);. This both calls the getRange function, passing in the p1 and p2 parameters, and returns the results value to the table.
The only place in the code that should reference p1 or p2, should be the one line of code that I mentioned above. So with that, the second thing I would do is rename p1 and p2 everywhere inside the function. Give it some other name such as strNum and endNum. This is so it doesn’t interfere with the existing P1 P2 parameters.
There’s a million ways to write the code. You could eliminate the function altogether and just use the code inside of it and that would work too.
2 Likes
If I’m someone who doesn’t even know what JavaScript is, what should I type in chatgpt so it generates ready to copy script for me? 
ChatGPT is impressive, but it’s also pretty good at making stuff up that isn’t always true, or doesn’t always fit the context of how you plan to use it. You can only put so much faith into AI. I think if you are going to rely on it to generate code, it helps to at least have a basic understanding of code structure so you can adjust it to your needs. ChatGPT will most likely generate code that is syntactically correct, but it has no idea in what context you will use it. I think you would have to be very specific in your query, but to do that you would probably need to already have a basic idea of what the code should look like. In the end you’ll still probably need to make some adjustments to the code.
In reality most programmers copy and paste code all the time, whether it’s existing code they already have and reuse it for something else, or by searching the web for solutions when they get stuck. The hard part with javascript in Glide, is that there isn’t a good system to debug the code, and the errors don’t give you any line numbers to know where an error occurs. I’m used to development environments where you compile code, which checks for syntax errors, and where you can then debug code line by line as it runs, which makes it much easier to find errors. You can kind of do it in glide, but it’s not easy.
I’d say play around with javascript. Try to write a very simple hello world example that simply returns a value. Then build upon it by passing in parameters, maybe do some simple math or join two values together and return that result. That will give you a general idea of how code works. I think even with a basic understanding, you can fumble your way through the code and see how it works. Then you can start to understand the syntax that makes up the code. Most languages work the same but each one has particular syntax and a certain way to structure it. There’s subtle differences, but once you learn one language, it’s pretty easy to look at another language and understand what it’s doing.
Hope that’s helpful. ChatGPT can give you a good baseline to get started, but you’ll have to learn some of the basics so you can make adjustments as needed.
4 Likes
I find JSFiddle quite handy for debugging when I’m writing JavaScript for Glide.
3 Likes
Agreed. I spent HOURS the other days trying to craft the perfect prompt to generate the code I needed. Chat GPT will rarely give you a working solution on the first try…especially if there are arrays involved. Do what @Jeff_Hager said and learn a bit of JS first.
2 Likes
Thanks for the tip. Much better than throwing in random returns here and there to check different values.
1 Like