How to wait for GAS custom function to finish and then continue using google sheets api?

Question

I am using Google Sheets api for creating a spreadsheet with multiple sheets, all of which have custom function, which is written in Google App Script. This function fetches data from my server, and usually there are at least 25 function calls.

Now I want to download the file using google sheets api, once all the custom functions are finished properly.

How do I wait for all custom functions to finish and then proceed with my google sheets api calls?

@StefanvanAalst Yes I have, and it has not helped me. Sheets API execution keeps on going without waiting for custom functions. — Lakhan, Feb 21 '18 at 09:07
Is this helpful https://stackoverflow.com/questions/11142109/how-to-use-utilities-sleep-function — Stefan van Aalst, Feb 22 '18 at 15:54
@StefanvanAalst sleep function will halt the execution of custom function, it won't halt the execution of Google Sheets API, I am afraid. — Lakhan, Feb 23 '18 at 08:29

Lakhan · Accepted Answer · 2018-09-21T19:49:53.077

If anyone else is still looking for an answer, I found a way that may not be ideal, but it works.

def check_if_ready(spreadsheet_id):
    no_of_retry = 5
    response = service.spreadsheets().get(spreadsheetId=spreadsheet_id,fields='sheets(data.rowData.values(effectiveValue))').execute()
    i = 1
    while 'errorValue' in str(response):
        if i > no_of_retry:
            return Response({'message': 'Error!'}, status=status.HTTP_404_NOT_FOUND)
        response = service.spreadsheets().get(spreadsheetId=spreadsheet_id,fields='sheets(data.rowData.values(effectiveValue))').execute()
        time.sleep(5)
        i += 1

since errorValue will be returned for the custom function which is not calculated properly or is still being calculated at the time of the API call.

How to wait for GAS custom function to finish and then continue using google sheets api?

1 Answers1