Hi there I have a formula in a google sheet ...

[Benoit_Gramond]

Hi there

I have a formula in a google sheet that will count the number of likes on a Post Instagram from a partner.

Here is the formula. The link is in column C and the formula in column D.

IF(ISBLANK(A2),"",value(LEFT(RIGHT(REGEXREPLACE(CONCATENATE(IMPORTDATA(C2)),"(^.display_url: {"“count”": )(\d+)(}.)","$2"),LEN(REGEXREPLACE(CONCATENATE(IMPORTDATA(C2)),"(^.display_url: {"“count”": )(\d+)(}.)","$2"))-SEARCH(“meta content=”,REGEXREPLACE(CONCATENATE(IMPORTDATA(C2)),"(^.display_url: {"“count”": )(\d+)(}.)","$2"),1)-13),SEARCH(" “,RIGHT(REGEXREPLACE(CONCATENATE(IMPORTDATA(C2)),”(^.display_url: {"“count”": )(\d+)(}.)","$2"),LEN(REGEXREPLACE(CONCATENATE(IMPORTDATA(C2)),"(^.display_url: {"“count”": )(\d+)(}.)","$2"))-SEARCH(“meta content=”,REGEXREPLACE(CONCATENATE(IMPORTDATA(C2)),"(^.display_url: {"“count”": )(\d+)(}.)","$2"),1)-13),1)-1)))

It is kind of slow formula.

My data is now on a MySQL database, is there a way in appsheet to link with the Instagram API or to use this kind of formulas?

[Simon_Robinson]

Hi, +@Benoit_Gramond

This isn’t related to your question.

But I’m intrigued

about wether changing from Google Sheets to SQL made your Apps better or quicker in any way.

I’ve a few that are getting really big and was considering swappping them over.

[Benoit_Gramond]

@Simon_Robinson Hi, Indeed it made them a lot quicker. I uses a Google Cloud MySQL server for few reasons:

1 - I could selcet the location of my Instance (I have a part of the app in china anther in EU so 2 instances linked together) 2 - I can use scripts 3 - I can use views instead of slices for huge tables. It is the same effect but a bit faster 4 - I can use triggers for certain things that are slowing the app within appsheet

[Steven_Coile]

I’ll bet that’s a slow formula! Wow! Regular expressions alone are expensive. RegEx with replace even moreso. You do the same REGEXREPLACE() 6 times! And IMPORTDATA() 6 times!

[Steven_Coile]

Reformatted for clarity:

IF(

ISBLANK(A2),

“”,

value(

LEFT(

RIGHT(

REGEXREPLACE(

CONCATENATE(

IMPORTDATA(C2)

),

“(^.display_url: {”“count”": )(\d+)(}.)",

“$2”

),

LEN(

REGEXREPLACE(

CONCATENATE(

IMPORTDATA(C2)

),

“(^.display_url: {”“count”": )(\d+)(}.)",

“$2”

)

)

-SEARCH(

“meta content=”,

REGEXREPLACE(

CONCATENATE(

IMPORTDATA(C2)

),

“(^.display_url: {”“count”": )(\d+)(}.)",

“$2”

),

1

)

-13

),

SEARCH(

" ",

RIGHT(

REGEXREPLACE(

CONCATENATE(

IMPORTDATA(C2)

),

“(^.display_url: {”“count”": )(\d+)(}.)",

“$2”

),

LEN(

REGEXREPLACE(

CONCATENATE(

IMPORTDATA(C2)

),

“(^.display_url: {”“count”": )(\d+)(}.)",

“$2”

)

)

-SEARCH(

“meta content=”,

REGEXREPLACE(

CONCATENATE(

IMPORTDATA(C2)

),

“(^.display_url: {”“count”": )(\d+)(}.)",

“$2”

),

1

)

-13

),

1

)

-1

)

) )

[Steven_Coile]

You’d probably help yourself a lot by performing the entire REGEXREPLACE() to the side–once–and using that result, instead of recomputing it 6 times.

Suppose you defined X2 as:

=REGEXREPLACE(

CONCATENATE(IMPORTDATA(C2)),

“(^.display_url: {”“count”": )(\d+)(}.)",

“$2” )

then your formula becomes:

IF(

ISBLANK(A2),

“”,

VALUE(

LEFT(

RIGHT(

X2,

LEN(X2)

• SEARCH(“meta content=”, X2, 1)

• 13

),

SEARCH(

" ",

RIGHT(

X2,

LEN(X2)

• SEARCH(“meta content=”, X2, 1)

• 13

),

1

)

• 1

)

) )

[Steven_Coile]

And define Y2 as:

=RIGHT(

X2,

(LEN(X2) - SEARCH(“meta content=”, X2, 1) - 13) )

making your formula:

IF(

ISBLANK(A2),

“”,

VALUE(LEFT(Y2, (SEARCH(" ", Y2, 1) - 1))) )

Wow!

[Benoit_Gramond]

+Steve Coile indeed it is perfect ! so much quicker!