CODEWITHSUNDEEP
node.jsgoogle-sheetsgoogle-workspacegoogle-api-nodejs-client
Vikram Tiwari
Vikram Tiwari

Reputation: 3905

Sheets API values.get sporadically returns Loading... when I try to get values for a cell

Scenario:

Using API, do the following:

Sporadically the value for C1 will end up being Loading... which is a placeholder value until the custom formula returns a value. As I look on the spreadsheet UI, the value there is correct but API can return the Loading... value. I have tried adding some delay before making the call for getting the value from C1 but to no avail.

Am I missing something here? Should there be longer wait time?

Upvotes: 2

Views: 373

Answers (2)

meelszz
meelszz

Reputation: 160

I fixed this problem by creating an appscript project that activates each sheet, sleeps for a bit, then gets the data. Deploy the appscript project as a web app and then you can call it from python with a request.

In my case, I'm getting a range of data from each sheet on a spreadsheet.

Appscript:

function doGet(e) {
var spreadsheetId = e.parameter.spreadsheetId;
var range = e.parameter.range;

if (!spreadsheetId || !range) {
    return ContentService.createTextOutput(JSON.stringify({ "error": "Missing parameters" }))
        .setMimeType(ContentService.MimeType.JSON);
}

var ss = SpreadsheetApp.openById(spreadsheetId);
var sheets = ss.getSheets();
var result = {};

for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    var sheetName = sheet.getName();
    ss.getSheetByName(sheetName).activate();
    Utilities.sleep(100)
    var data = sheet.getRange(range).getValues();
    
    result[sheetName] = data;
}

return ContentService.createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON);

}

Python:

import requests
SCRIPT_URL = "COPY AND PASTE THE URL FROM THE APPSCRIPT WEB APP DEPLOYMENT"

def get_values_on_spreadsheet_from_appscript(spreadsheet_id, range):

params = {
    "spreadsheetId": spreadsheet_id,
    "range": range
}

try:
    response = requests.get(SCRIPT_URL, params=params)
    response.raise_for_status()  # Raise an error for bad responses
    
    if response.text.strip():  # Ensure response is not empty
        return response.json()  # Convert response to dictionary
    else:
        print("Error: Empty response from Apps Script")
        return None

except requests.exceptions.RequestException as e:
    print(f"HTTP Request Failed: {e}")
    return None
except requests.exceptions.JSONDecodeError:
    print(f"Invalid JSON response: {response.text}")
    return None

Upvotes: 0

Fillyjonk
Fillyjonk

Reputation: 176

In your spreadsheet formula, try to set absolute references for either columns $C1 or rows C$1 or both $C$1. For me, it worked just fine and instantly. Moreover, my problem doesn't seem to reemerge.

Just FYI, in my case, a custom app script formula =columnToJSON('Categ Lists'!H$4:H$103) was fetching 100 rows and compiling a JSON list out of array of non-empty values. The formula was repeated over some 50 columns. Setting columns as absolute references took an immediate effect and my Node.js app started reading the return values of GoogleSheets formulas with no sporadic interruptions.

Hope it helps you.

Upvotes: -1

Related Questions