Clay Icon

Issues with Google Sheet Export Enrichment for EU Companies

·
·

Hello Clay Support team, I’d like to ask about the Google Sheet export enrichment. I have a table EU companies that already had a Google Sheet export, but it stoped working and it only reported errors or the time run out. So I removed the old column and created a new one. But for some reason I cannot map the columns as I did in the previous one. My google sheet file is shared for everyone who has the link and is alwo marked as an editor. Could you please advise? Here is the table: https://app.clay.com/workspaces/306102/workbooks/wb_nChWvdCAJzS5/tables/t_UTYXMapBpeqT/views/gv_dwe4VRoCqYZw

  • Avatar of Channeled
    Channeled
    APP
    ·
    ·

    👋 Hey there! Our support team has got your message - we'll be back in touch soon. If you haven't already, please include the URL of your table in this thread so that we can help you as quickly as possible!

  • Avatar of Bruno R.
    Bruno R.
    ·
    ·

    Hi Helena, thanks for reaching out. Taking a look at this now!

  • Avatar of Bruno R.
    Bruno R.
    ·
    ·

    Could you attempt to reconnect your google sheets account by following the directions in this guide? There seems to be an issue with the authentication of your credentials here: https://www.clay.com/university/guide/google-sheets-integration-overview Let me know if this resolves this issue and if I can help with anything else!

  • Avatar of Helena P.
    Helena P.
    ·
    ·

    Hi Bruno, thank you. I will give it a try. 🙂

  • Avatar of Helena P.
    Helena P.
    ·
    ·

    Hi Bruno, I followed the instructions from Action Lookup, Add, or Update Row section and the issue is that when I want to map the fields there is nothing to map.

  • Avatar of Helena P.
    Helena P.
    ·
    ·

    Is it possible that it is like that because of filter used in the google sheets?

  • Avatar of Bruno R.
    Bruno R.
    ·
    ·

    Hi Helena, I went ahead recorded this video to show you how you can retrieve the individual column values associated with your table's sheets here: https://www.loom.com/share/9d17f24464884504a72b1f7ad6ec8cc8?sid=206a2045-7275-453a-a0c2-323c8bb7c435 Let me know if this makes sense and if I can help with anything else, this should resolve the issue with the upsert action.

  • Avatar of Helena P.
    Helena P.
    ·
    ·

    Hi Bruno, thank you so much. I had done exactly the same as you did in the video since the noon but for some reason the Field mapping did not display for me.

  • Avatar of Helena P.
    Helena P.
    ·
    ·

    Now it finally worked.

  • Avatar of Bruno R.
    Bruno R.
    ·
    ·

    Happy you were able to get this up and running!

  • Avatar of Helena P.
    Helena P.
    ·
    ·

    it is still loading the look up fields tho.

  • Avatar of Helena P.
    Helena P.
    ·
    ·
  • Avatar of Helena P.
    Helena P.
    ·
    ·

    It is loading pretty long.

  • Avatar of Bruno R.
    Bruno R.
    ·
    ·

    Hi Helena, were you able to run the integration? The loading time is due to Google Sheets' rate limits, which unfortunately means there's little we can do to improve it on our end. Let me know if you need further assistance!

  • Avatar of Helena P.
    Helena P.
    ·
    ·

    No I still cannot map the fields. I got to the poinz where it let me to put in the ORG ids as a Lookup value, but after that it froze and I could not map anything. 😕

  • Avatar of Bo (.
    Bo (.
    ·
    ·

    Hey - It really seems to be due to the size of the Google Sheet. Can you do this for me? 1. First, disconnect and reconnect your Google account: - Go to Clay settings - Remove the Google connection - Add it back and reauthorize 2. Create a test sheet with just 10-20 rows and attempt the mapping again. This helps isolate if it's a size/performance issue. If you're still experiencing freezing issues after trying with a smaller sheet, I'll need to report this to our engineering team to investigate further. Let me know

  • Avatar of Helena P.
    Helena P.
    ·
    ·

    Hi Bo, thank you very much. I’ll do it tomorrow as a first thing in the morning

  • Avatar of Helena P.
    Helena P.
    ·
    ·

    Hi Bo, so I tried what you suggested and it works. It works for a new spreadsheet only. I deleted most of the data from the old spreadsheet and it seems working. I just want to know what do I do to avoid the same situation.

  • Avatar of Daniela D.
    Daniela D.
    ·
    ·

    Hey Helena! Thanks for the message. QQ: What was the size of the sheet (number of rows/columns) before you deleted most of the data.

  • Avatar of Daniela D.
    Daniela D.
    ·
    ·

    Hey Helena! Thanks for reaching out. Happy to help.

  • Avatar of Helena P.
    Helena P.
    ·
    ·

    Hi Daniela, it was just below 14000 rows

  • Avatar of Bo (.
    Bo (.
    ·
    ·

    Since the sheet has 14,000 rows, it's hitting timeout issues during loading which causes the Google Sheets connection to drop. This happens with large datasets because they're very resource-intensive to process. Instead of trying to load it directly, you can set up a Google Script to forward the data to your previous sheet. This will handle the data transfer more reliably without timing out. Here's how to set up the Google Script to forward your data: 1. In your new Google Sheet (source), go to Extensions > Apps Script 2. Copy this script: function copyDataToAnotherSheet() { // Source spreadsheet (your new sheet) const sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Target spreadsheet (your old sheet) - replace with your sheet ID const targetSpreadsheet = SpreadsheetApp.openById('TARGET_SHEET_ID'); const targetSheet = targetSpreadsheet.getActiveSheet(); // Get all data from source const data = sourceSheet.getDataRange().getValues(); // Clear target sheet and paste new data targetSheet.clear(); targetSheet.getRange(1, 1, data.length, data[0].length).setValues(data); } ``` 3. Replace TARGET_SHEET_ID with your old sheet's ID (find it in the URL between /d/ and /edit) 4. Click Save and Run 5. First time you run it, authorize the script when prompted You can also set this to run automatically: 1. Click the clock icon (Triggers) 2. Click "Add Trigger" 3. Choose when you want it to run (hourly/daily/etc) Just let me know if you have any issues! :)

  • Avatar of Helena P.
    Helena P.
    ·
    ·

    Hi Bo, I tried it, but for some reason it deleted all the data in the google sheet

  • Avatar of Helena P.
    Helena P.
    ·
    ·

    Can I ask you what do you mean by the new Google Sheet (source)? Shouldn’t it got the data from Clay table?

  • Avatar of Daniela D.
    Daniela D.
    ·
    ·

    Hey Helena! Sorry about this. It looks like the row was deleted because an input in the script (.getRange) was set to overwrite existing data. New Google Sheet: Bo was referring to a second sheet that should be used in Clay as the destination table. This new sheet will receive what is sent without running into timeout issues associated with larger sheets. The Apps script shared was to update the older table (with 14,000 rows) with any new data that is sent from Clay to the new sheet. Here's a corrected version that should help with this: function copyNewRowsToDifferentFile() { var sourceSpreadsheet = SpreadsheetApp.openById("SOURCE_SHEET_ID"); var destinationSpreadsheet = SpreadsheetApp.openById("DESTINATION_SHEET_ID"); var sheetA = sourceSpreadsheet.getSheetByName("Sheet A"); var sheetB = destinationSpreadsheet.getSheetByName("Sheet B"); var dataA = sheetA.getDataRange().getValues(); var lastRowB = sheetB.getLastRow(); if (dataA.length > lastRowB) { var newRows = dataA.slice(lastRowB); sheetB.insertRowsAfter(lastRowB, newRows.length); sheetB.getRange(lastRowB + 1, 1, newRows.length, newRows[0].length).setValues(newRows); } } You'll need to input the sheet Ids (source sheet ID and Destination sheet ID) and the sheet names (where it says Sheet A and Sheet B). Feel free to test this flow for one row and let us know if you run into any issues.

  • Avatar of Helena P.
    Helena P.
    ·
    ·

    Hi Daniela, thank you for your reply. I have a question about if I am supposed to write a Clay table ID in to SOURCE_SHEET_ID?

  • Avatar of Bo (.
    Bo (.
    ·
    ·

    Hi Helena! Let me clarify - for the script, you'll actually need to use Google Sheet IDs, not Clay table IDs. Here's how it works: 1. You'll need two Google Sheets: - A new sheet that Clay will export to (this is your SOURCE_SHEET_ID) - Your existing 14,000-row sheet (this is your DESTINATION_SHEET_ID) You can find each sheet's ID in its URL - it's the long string between /d/ and /edit. Just to re-confirm the workflow: 1. In Google Sheet, create a new sheet, add the headers. 2. Copy that URL inside of Clay in the Google Sheet integration, map the value. 3. Send the data to that new Google sheet. 4. Inside of the new Google sheet, add this add script so it forwards the data from that one to the main one (the big one).

  • Avatar of Channeled
    Channeled
    APP
    ·
    ·

    Hey there - just wanted to check in here to see if you needed anything else! Feel free to reply back here if you do.

  • Avatar of Channeled
    Channeled
    APP
    ·
    ·

    We haven't heard back from you in a bit, so we're going to go ahead and close things out here - feel free to let us know if you still need something!

  • Avatar of Channeled
    Channeled
    APP
    ·
    ·

    Hi Helena P.! This thread was recently closed by our Support team. If you have a moment, please share your feedback:

  • Avatar of Helena P.
    Helena P.
    ·
    ·

    Hi guys, thank all of you for your help. Bo (. Thank you for the explanation. I did what you suggested. So I am transfering the data to the new sheet and I implemented the Google script. But for some reason the data do not transfer between the sheets. Here is the code I used: function copyNewRowsToDifferentFile() { var sourceSpreadsheet = SpreadsheetApp.openById(“1hjGSXleUWUmbps_GTIVHFLjRbUSGuw0BKZEzt5Dbn2w”); var destinationSpreadsheet = SpreadsheetApp.openById(“1F7rMxEB2TK7cbpzGHxhm0mcCamxhR_I410LUKAZN1v4"); var sheetA = sourceSpreadsheet.getSheetByName(“Companies”); var sheetB = destinationSpreadsheet.getSheetByName(“Companies”); var dataA = sheetA.getDataRange().getValues(); var lastRowB = sheetB.getLastRow(); if (dataA.length > lastRowB) { var newRows = dataA.slice(lastRowB); sheetB.insertRowsAfter(lastRowB, newRows.length); sheetB.getRange(lastRowB + 1, 1, newRows.length, newRows[0].length).setValues(newRows); } }

  • Avatar of LuisArturo
    LuisArturo
    ·
    ·

    Hey there Helena thanks for reaching out, jumping in for Bo here, just to confirm is this still for the same table you linked earlier in the thread.

  • Avatar of Helena P.
    Helena P.
    ·
    ·

    Hi Luis, yes it is. :) I can link you the New table as well if needed

  • Avatar of Helena P.
    Helena P.
    ·
    ·

    I don’t know what I am doing wrong.

  • Avatar of Channeled
    Channeled
    APP
    ·
    ·

    Hey there - just wanted to check in here to see if you needed anything else! Feel free to reply back here if you do.

  • Avatar of Channeled
    Channeled
    APP
    ·
    ·

    We haven't heard back from you in a bit, so we're going to go ahead and close things out here - feel free to let us know if you still need something!