Streaming live at 10am (PST)

Get text value from published google sheets csv and set as value to text element

Trying to get some custom code to fetch a single number from a published google sheet as csv and set this as a text value on my webflow site.

Say I have a google sheet with a single cell containing a number
sheetcell

I publish this cell/sheet/tab as an CSV file

This creates a link that downloads the CSV with the single value
link to download published csv

Now in Webflow I created a text element with the class & id set to “texthere”

And finally I add some custom code before tag that fetches the value from the csv and pushes it as the value from the “texthere” element

but this doesn’t work :frowning:

<script>
fetch('https://docs.google.com/spreadsheets/d/e/2PACX-1vTzPpBUE864SFYuk0WUbhQbvR5VWOVQFhO3yOBYtlkxj_xkYSw1UNUlJWmDwBibMesi4C--H_EkUD19/pub?gid=0&single=true&output=csv')
.then(function(response) { response.text() })
.then(value => {
	const Webflow = Webflow || [];
  Webflow.push(function() {
    $('.texthere').val(value);
  });
});
</script>

Any ideas?

https://preview.webflow.com/preview/googlesheetimport?utm_medium=preview_link&utm_source=designer&utm_content=googlesheetimport&preview=f181de041c3445eee55cb2c78c260b53&workflow=preview

Edit: url formatting

That’s an unusual way to use the CSV export feature of sheets… I can see that because you have no header row, and only the one populated cell, it emits the cell content by itself. Semi-useful, but if you’re doing this to make life easier for a client, consider locking that sheet down tight so that only the one cell is editable. If they accidentally put anything in another cell, your output will be a delimited CSV chunk.

Without setting up a demo, I’d suggest these things;

  1. Your jQuery selector appears incorrect, change it to $('#texthere') You want to select the element with that ID, not the element(s) with the class texthere
  2. .val() is generally used with FORM elements to set their value property. Here it looks like you have a text element, so try using .text() or .html() to set that inner content.
  3. You’re using jQuery, and accessing elements before the page may be fully rendered. Try putting your code in an on-ready function.
<script>
$(function() {
 ... your code here ...
});
</script>
  1. Are you getting the content you want into your value? Verify that, using console.log(...) and check the developer tools output.
  2. If you’re calling your code after the page is loaded (see #1), you shouldn’t need the Webflow.push. Just set your value.
1 Like
<script>
fetch('**https://docs.google.com/...&output=csv**')
.then(function(response) { return response.text() })
.then(value => {
    $('#**ID**').text(value);
});
</script>

This works perfectly, even as an embedbed piece of code
It also works as code before body tag

Thanks!

1 Like

That’s awesome!

Just be cautious, on the web functionality is heavily affected by browser choice, your cached content, and your network situation. Someone on a slower connection could get a different load/execution timing, which would execute your code before the jQuery library is loaded.

That’s great advice to think about.

How would I prevent this issue? Placing the code in body tag? Or maybe hiding the actual text with an animation after page is loaded?

Try …

<script>
var Webflow = Webflow || [];
Webflow.push(function() {
    // DOMready has fired
    // May now use jQuery and Webflow api
    fetch('**https://docs.google.com/...&output=csv**')
        .then(function(response) { return response.text() })
        .then(value => {
            $('#**ID**').text(value);
        });
});
</script>
1 Like

@webdev Very curious- what is the benefit of the Webflow.push() approach vs just a standard jQuery domready event?

@memetican - The code can be run anywhere is the main one.

Danro explains it as

Hi @gvdias :wave:, the Webflow.push wrapper is only necessary if you plan to place your code in the Head Code block, or within a code embed element in the Webflow Designer.

Webflow javscript wrapper - var Webflow = Webflow || []; Webflow.push(function () { - Design help / Custom code - Forum | Webflow

Oh sweet, deferred execution, that guarantees jQuery is loaded. That could come in handy, thanks for the tip.

Thanks everybody!

:green_heart:

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.