Problem using FormEmailer with importxml

[Dan Longley]

Hi Henrique

I'd really like some help on this one, if you've got a moment?

I'm using importxml to bring in mileage data from google maps and using that data to calculate distance to event gigs. FYI, here's the formula:

=index(query(importxml("http://maps.google.com/maps?saddr="&G2&"&daddr="&BurdockData!$C$3,"//span[1]");"select Col1 where Col1 contains 'km' or Col1 contains 'mi' ";0);1)

The spreadsheet works fantastically, but I can't let FormEmailer automatically send emails because the data doesn't get processed in time. This is what I would see in the email:

Miles (text): #N/A
2 car travel: #N/A
1 car travel: #N/A
Date taken?: No
Alt date taken?(X):
Enquiry already?: No

Some formulae process in time (eg "Date taken?"), but the top 3 (Miles and car travel) haven't, which makes me think that importxml can't pull in the data fast enough. When I look at the spreadsheet, I'll find that those travel formulae have processed correctly.

If I'm right, could I put a few seconds delay somewhere in your script to allow importxml to process and register a result before the email is sent, or an "email doesn't process until" condition?

Before I lose the remainder of my hair playing around with this, could you give me any advice as to the best way to achieve this? I'm sure I can work this out, but a bit of encouragement would be gratefully received!

Many thanks as usual

Dan

[Henrique Abreu]

Hi Dan,

Yes, there's some functions that are not instantly calculated, importXml is one of them. You can insert a delay, but it's not guaranteed that'll work every time. 

You have to replace line 222 (considering version 3.62), currently this:

var values = all.setFormulasR1C1([c.formulas]).getValues()[0];

 

with these:

all.setFormulasR1C1([c.formulas]);  

SpreadsheetApp.flush();

Utilities.sleep(1000);

var values = all.getValues()[0];

 

Regards,

Henrique Abreu

--
You received this message because you are subscribed to the Google Groups "FormEmailer" group.
To unsubscribe from this group and stop receiving emails from it, send an email to formemailer...@googlegroups.com.
To post to this group, send email to forme...@googlegroups.com.
Visit this group at http://groups.google.com/group/formemailer.
For more options, visit https://groups.google.com/groups/opt_out.

[Dan Longley]

Hi Henrique

That's done the job, thank you for doing the code, that's been annoying me for so long!  It's so magical watching FormEmailer do its magic.

Best wishes

Dan