a year or two ago, a gentleman helped me create my app for tracking my medication. 6 months into it maybe, I saw that the app said that I only had a certain amount of lines for the free version. My logging was at least half of that so I wondered what was going on. I brought up the data sheet and lines 1-110 were all one after he other - no skipped lines, but it suddenly skipped to something like 285 (I don’t remember the actual number but I believe it was in the upper 200s. The next month, it skipped again and the number of lines skipped has been growing. Now, it’s skipping to 6 hundred something. Sometimes, it won’t allow me to input anymore data. Is there anyone who would be willing to take a look at this and help me to fix it so that it no longer skips? there might be something wrong in the array coding. I don’t know coding but I know enough to understand what things are. I hope someone is willing to help me. Thank you!
You can delete all empty rows, including the ones at the bottom of the sheet (after the last row of data). Glide will automatically add a row when the app adds a new entry.
Your sheet likely uses array formulas, which is probably what is causing the skipped/empty rows. Your app may also make use of a “delete row” action, which only deletes the data from Google Sheet rows, not the row itself (leaves empty rows!).
Hi @Somps , how are you?
- Are the empty rows being created in a Google Sheet?
- If that is the case, does at least one of the columns in your spreadsheet have an =arrayformula() in row 1 or 2?
If such is the case, that could be the culprit: remove any =arrayformula formulas.
Thank You! sorry, I got really busy with things and finally got some time. I suddenly had a big project that had a deadline and I finally finished it. To answer the question , Yes, there is an array in there. If I take it out, my app won 't work properly, right?Should I share a link?
Hi. Sorry I didn’t get back. Had a project with a deadline. The app has an array. The app is to keep track of my medication and to also let me see when my next medication will be.I’ll make, let’s say. 110 entries and suddenly it skips 250 rows and starts the next entry. The way I found out was because it once said that I was out of space and to consider buying the upgrade. That’s when I went and looked. The first time it happened, it skipped something like 100 rows +/- and started again.I have to be careful about deleting the rows because sometimes it will show #REF errors and it won’t calculate the data.
Could you please share a screenshot of the GSheet and highlight the cell with formula
my best guess will be, that your arrayformula is writing empty values, replace
,, that will do it. example…
=arrayformula(if(a1:a="",,b1:b=1)), in your case, the problem is at the end of your formula… do not put
"" at the end… finish with just coma
Thank You! I will try that.
It might take a week or 2 to see if this fixes the problem. Thank You to everyone for helping.
why? create some dummy data to test it… and delete it after
After you change the formula make sure to delete all empty rows… to make sure nothing is affecting new sheet anymore
If I’m following your screenshots correctly, then what I see is that you’re calculating the next dosage date/time based on the last dosage and a lookup that comes from elsewhere (an interval?).
So essentially, a bit of date math - something that’s trivial to do with Glide computed columns.
Do you actually need these calculated values in the spreadsheet, or only in Glide?
If they are not needed in the spreadsheet, then I’d recommend getting rid of these columns in your spreadsheet, and replace them with Glide computed columns.
Then all these problems you have been having will melt away.
Thanks for your response. Sorry. Been dealing with health issues lately, so I’ve been a bit slow to respond. There was a guy who helped build this for me so I’m not sure about what is needed on the spreadsheet to make it work. The key things that I need are to have my list of meds and to be able to insert new meds and they work as needed. I tap on the + and choose a medication from the dropdown list and it puts in the time and date, which is crucial for me to look back and see exactly when I took any said med, and then it puts in the next time I’m supposed to take the dosage. That way, at any time, I can tap the med and see my next dosage for that med.If I made a mistake, I can delete the entry and put in a new entry. Most meds have a time set that I have to wait to take it. I don’t have to see that data but I’m pretty sure the app needs it to do calls. I hope this helps in understanding what I have. This way, maybe you can tell me what Glide needs and what the spreadsheet needs. Thank You!
Okay. Based on that I’d say it’s certainly a good idea to move the logic into Glide.
If you’re willing to give me access to your app, I can do that for you. I won’t charge you anything.
Send me a private message if you’d like some help with that.
Thank You reveryonre. Darren has kindly offered to work with me on this.