import googlesheet through insert fro URL

June 25, 20205 yr

Newbies

hi,

I have a googlesheet through which I collect data, submitted with googleforms.

example: https://docs.google.com/spreadsheets/d/1tg1CkSOrY-zFb9eGI2pHEH5JnxfHKvCoatwpI56ThgM/edit?usp=sharing

By using Insert from URL with URL https://docs.google.com/spreadsheets/d/1tg1CkSOrY-zFb9eGI2pHEH5JnxfHKvCoatwpI56ThgM/export?format=tsv&gid=916562697 (same as the original link except that you replace edit? with export?format=tsv&, I can import the file content as a tab separated text into a text field, export it again as at tsv file and import it in the correct fields into Filemaker.

Insert from URL [ import::temp file; "https://docs.google.com/spreadsheets/d/ .... export?format=tsv&gid= ..." ]
[ Do not automatically encode URL; Select; No dialog; Verify SSL Certificates ]

Set Variable [ $loc; Value:"file:/tempCO.tsv" ]
Export Field Contents [ import::temp file; “$loc”; Create folders:No ]

Go to Layout [ “tempFISW_C” (tempFISW_C) ]
Import Records [ Source: “$loc” ; Fields Name Row: 0; Predefined: Yes; Target: “tempFISW_C”; Method: Update; Add remaining; Character Set: “Unicode”; Field Mapping: Source field 1 import to tempFISW_C::Timestamp;

I have 2 webforms for which this works perfectly. I created them a few months ago. I am now making a 3rd one and keep getting as import file a redirect "<H1>Temporary Redirect</H1>
The document has moved <A HREF="https: ... " instead of the actual googlesheet data.

Anyone has any idea what is causing this? All settings are the same as on the googlesheets I managed to get imported. The ones I did prteviously stiull work as clockwork. And I know I had the problem then also but can;t remember how I solved it .... If anyone has the solution, I would be very grateful 🙂

Ciao< jeroen