Arrayformula circular dependency Problem in Sheets

Question

I wanna use this Arrayformula for a row (get the data from that row, and have the input also in it).

=ARRAYFORMULA(IF((LEN($C1:$C100)>240),IMPORTDATA(CONCATENATE("tinyurl.com/api-create.php?" & $C1),"")))

If the link is longer than 240 char. > to make it with tiny URL instead. It works if I put the formula on a different row, but when I try to conditional format the whole row it doesn't work / return anything, or when I use it as arrayformula it has the #REF - circular dependency error.

Is there a way to solve this (apply it to the whole row, take the date from that row and return the output on it also).

< it works on B - when applied for A, but not on row C for C

Thanks. (I couldn't link tiny url, but you can see it full in pic)

Answer 1

Suggestion:

You can try this sample script below that will add a Custom Menu on your spreadsheet file. Just copy & paste the script to your sheet as a bound script:

Script:

Copy the script from here

Note: Codes that contains tiny URL API, see here for more info, can't be posted directly on here. Thus, I have temporarily shared the sample script from this free code sharing site, which will expire after 24 hours

Sample Demonstration:

After saving the custom script, you will have these options:

Option 1 will produce the same result as to your original formula. The option 2 will ignore the URLs below 240 characters.

Sample:

Result:

If you select Tiny URL ver.2, only the URLs with more than 240 characters will be changed and the rest of the URLs remains the same instead of being replaced with FALSE