How to Loop Until Blank Cell in Sheets (v9.0.6)

I'm working on a macro that will send SMS messages, once every 15 minutes to find out if someone is available for a job. I'm using iMessage and this would be based on a google spreadsheet. It will copy a number, activate iMessage, paste text based on a set variable, then go back and go down the list until it has reached the end.

I feel like I have most of it, but the issue I'm running into is using the While function. My idea was to say while the PhoneNumber variable is not empty, continue to send text messages. What I'm finding though is that if it copies a blank cell, it's not actually empty, it looks like there is a return carriage in it. I'm not quite sure what to enter into KM to match this.

I've posted my unfinished macro in case that's of any help.

How can I say while this cell is not a blank sheets cell, continue to run?

Send SMS Messages from Spreadsheet.kmmacros (22 KB)

Haven’t tried, but can you use while variable does/does not contain a number?

Hmm, how would I write does not contain any number? It only gives me the ability to write text in this action. Tried to read the documentation and it doesn't seem like I can do a regex expression or anything inside to see if it returns a result. Screen Shot 2020-08-09 at 2.51.18 PM

Something like this:

You need to change my settings.

  1. add more variable conditions from 0 to 9.
  2. there should be an end to the loop, I set "Local__Var" to nothing within the loop, in order for the loop to end. Otherwise, it is an endless loop.

Edit: I forgot to mention: need to change "All of the following" to "any of the following".

If the number does not contain country code (such as +1), you can put something like "if PhoneNumber is > 1".

If the number contains a country code, you can get rid of the + sign first, then do a comparison. or you can set two conditions, variable "PhoneNumber" either contains the + sign or is > 1.

This is simpler than the 10 conditions I gave above.

I'm going to have to give this a try. Thanks for the example!

You're welcome.
I think this will also work, using RegEx:

While I was exploring that idea, I realized there was quite a bit of waste in going back to sheets every time to copy the number, should just copy them all at once and paste them one at a time into iMessage.

I'm not sure how to export my macro here again, but here is the action that I ended up using.

Screen Shot 2020-08-09 at 4.55.31 PM

Glad you found a better solution.
BTW, I see you seem to use click found image for sending iMessage. But looks like KM has an action for that:

1 Like