Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Chains - Error in Overwrite Sheet Data

Answered
Request Demo
  • Edited

I'm building a chain to automate pushing updated query results to a spreadsheet. I'm encountering an error in the Overwrite Sheet Data node that I can't solve. Please see structure of the chain below:

Below is the error I am receiving:

     🔌 Workiva BizApp1.0.0

      ⚙️ Overwrite Sheet Data Configuration:
      Admin Host Override:
      Use Async: true
      Client ID: #####
      Client Secret: ENCRYPTED 🔐
      Data: query_result_1632516058836.csv
      Delimiter (default is ','): ,
      Graph DB Host Override:
      Sheet ID/Name: ddc096595379424d89d8568c2c28cfed
      Spreadsheet ID: 08ceb7c07c6346cb80e1d62f90709a4b/-1
      Start Cell: A1

      Command description: Clears all cells in a sheet and replaces the sheet with the values provided in the file.

      Loading data into cell region: A1:
      Error: Unable to update sheet data.

      Result: Error updating sheet data: [PUT /async/spreadsheets/{SpreadsheetId}/sheets/{SheetId}/data/{Region}][404]       updateDataAsyncNotFound &{Errors:{Errors:[] ErrorString:""} Message: RequestID:37c19c09-433e-4477-8947-60d27f6edb31}

Has anyone experienced something similar or know how to resolve?

0

Comments

3 comments

Date Votes
  • Jeff Hickey

    Hi Jared, the Spreadsheet ID has "/-1" at the end of it which is invalidating the ID. Assuming that "08ceb7c07c6346cb80e1d62f90709a4b" is the correct Spreadsheet ID, could you please try again.

    0
  • Permanently deleted user

    Thank's Jeff. I've tried it with and without the "/-1", but neither seems to work. I just ran it again without the negative one, this is the results:

    🔌 Workiva BizApp1.0.0

    ⚙️ Overwrite Sheet Data Configuration:
    Admin Host Override:
    Use Async: true
    Client ID: #####
    Client Secret: ENCRYPTED 🔐
    Data: query_result_1632517419732.csv
    Delimiter (default is ','): ,
    Graph DB Host Override:
    Sheet ID/Name: ddc096595379424d89d8568c2c28cfed
    Spreadsheet ID: 08ceb7c07c6346cb80e1d62f90709a4b
    Start Cell: A1

    Command description: Clears all cells in a sheet and replaces the sheet with the values provided in the file.



    Loading data into cell region: A1:
    Job queued. Polling until complete.
    Job still processing job with ID = 'am9iOjA4Y2ViN2MwN2M2MzQ2Y2I4MGUxZDYyZjkwNzA5YTRiOjQ0MTE4' checking again in 3 seconds
    Job still processing job with ID = 'am9iOjA4Y2ViN2MwN2M2MzQ2Y2I4MGUxZDYyZjkwNzA5YTRiOjQ0MTE4' checking again in 3 seconds
    Error: Unable to update sheet data.

    Result: Error updating sheet data: [GET /queue-jobs/{JobId}][500] getQueueJobInternalServerError &{Errors:{Errors:[] ErrorString:} Message:Internal server error RequestID:}

    0
  • Jeff Hickey

    Hi Jared, I was able to see errors for the Sheet IDs 5ae08022288d4bebb0dac522e528487e and 14818b50014949af9ea61e601ace03e1. Do you have a timestamp of the chain run when the error occurred for Sheet ddc096595379424d89d8568c2c28cfed?

    The errors for Sheets 5ae08022288d4bebb0dac522e528487e and 14818b50014949af9ea61e601ace03e1 is caused because you're attempting to write more data than the Spreadsheet has remaining cells available. Each Spreadsheet has a maximum of two million cells available shared across all Sheets within. This includes actively used cells and historically used cells. It looks like a recent run of a chain is attempting to write output from the Query with ID 5f50c692c0db4f5fa44c6083c2c8917b. This query produces over 1.1 million cells of data (while the other query connected to the other Sheet produces over 602,000 cells of data) but your Spreadsheet has less than 119,000 cells of data remaining at the time of this writing. I suspect that the same issue is happening for Sheet ddc096595379424d89d8568c2c28cfed but I'd be happy to look closer if you can share a failed chain run timestamp.

    There are a couple of solutions. First, you could write the data to a new Spreadsheet which has its own allocation of 2 million cells. Second, you could delete entire rows and/or columns and Sheets that are no longer used to free up cell count within your Spreadsheet. Third, reduce the total Query output to fit within the remaining cell count.

    0

Please sign in to leave a comment.