Ad

Google Sheets Api : CORS Issue When Copying Sheets From One To Another Spreadsheet

I am facing a quite irritating issue that I can't understand nor resolve. I am using google sheet api to retrieve (in order) 1. retrieve date from a spreadsheet 2. updating this spreadsheet 3. creating a new spreadsheet using this data 4. modifying this freshly created spreadsheet with a batchupdate 5. copying a sheet from another spreadsheet into this freshly created spreadsheet.

Everything works fine as it is, but I need to (5) before (4)ing so I can modify the added sheet in the same time.

BUT, when I do so, I'm getting an "No 'Access-Control-Allow-Origin' header is present on the requested resource" error.

First : Why is that ? It is not causing a probleme if 4 is before 5

Second : How can make use of CORS using google api call provided by this 'gapi' library ? Should I switch to some regular fetching/XMLHttp ? (which I can't do :s. )

Here is my code, please someone can review it and give me some advices.


    const createSpreadsheet = (type) => {

        type.toLowerCase()

        setDialogLoader({ open: true })

        gapi.client.load('drive', 'v3')
            .then(() => {

                // 1.   getting current doc number

                let req = gapi.client.sheets.spreadsheets.values.get({
                    spreadsheetId: config.configSpreadsheet.id,
                    range: config.configSpreadsheet.range[type],
                    valueRenderOption: 'FORMATTED_VALUE'
                })
                return req
            })
            .then(resp => { 

                // 2.    updating doc number

                console.log(resp)
                let number = parseInt(resp.result.values[0])
                let req = gapi.client.sheets.spreadsheets.values.update({
                    spreadsheetId: config.configSpreadsheet.id,
                    range: config.configSpreadsheet.range[type],
                    valueInputOption: 'RAW',
                    resource: {
                        range: config.configSpreadsheet.range[type],
                        values: [[number + 1]],
                    },
                    includeValuesInResponse: true
                })
                return req
            })
            .then(resp => { 

                // 3.    creating the spreadsheet

                console.log(resp)
                let number = parseInt(resp.result.updatedData.values[0])
                let req = gapi.client.drive.files.create({
                    'mimeType': 'application/vnd.google-apps.spreadsheet',
                    'parents': [`${config.folderId[type]}`],
                    "name": type + '-' + number + '/' + selectedClient.nom,
                    "properties": {
                        type: type,
                        description: 'This is a resume of the tasks to do...',
                        date: Date.now(),
                        clientId: selectedClient.id,
                        number: number,
                    },
                    "fields": 'name, properties, id, webViewLink'
                })
                return req
            })
            .then(resp => { 

                // 4.   batchUpdate :modifying the sheet

                console.log(resp)
                if (type === 'devis') {
                    let newDevis = resp.result
                    setDevis([newDevis, ...devis])
                } else if (type === 'facture') {
                    let newFacture = resp.result
                    setFactures([newFacture, ...factures])
                }
                let params = {
                    spreadsheetId: resp.result.id
                }
                let batchUpdateValuesRequestBody = {
                    requests: requestBody
                };
                let req = gapi.client.sheets.spreadsheets.batchUpdate(params, batchUpdateValuesRequestBody)
                return req
            })
            .then(resp => { 

                // 5.   copying sheet from another SS ( CORS ISSUE IF before step 4 ??? )

                console.log(resp)
                var params = {
                    // The ID of the spreadsheet containing the sheet to copy.
                    spreadsheetId: '1_2Atry0sZ9MJ4VRMDRPC8cVIDWfOnC_k66HYKXfdfS0',
                    // The ID of the sheet to copy.
                    sheetId: 0,
                }
                var copySheetToAnotherSpreadsheetRequestBody = {
                    // The ID of the spreadsheet to copy the sheet to.
                    destinationSpreadsheetId: resp.result.spreadsheetId,
                    // TODO: Add desired properties to the request body.
                };
                var request = gapi.client.sheets.spreadsheets.sheets.copyTo(params, copySheetToAnotherSpreadsheetRequestBody);
                return request
            })
            .then((resp) => setDialogLoader({ open: false }))
            .then((resp) => setSnackbarObject({
                open: true,
                message: type === 'devis' ?
                    `Un devis vient d'être créé dans votre dossier 'Devis` :
                    `Une facture vient d'être créée dans votre dossier 'Factures`
            }))
            .then((resp) => setDocChooserObj({ open: false }))
            .catch(reason => {
                console.log(reason)
            })
    }

Now here is the code with the 5 before the 4.


    const createSpreadsheet = (type) => {

        type.toLowerCase()

        setDialogLoader({ open: true })

        gapi.client.load('drive', 'v3')
            .then(() => {

                // 1.   getting current doc number

                let req = gapi.client.sheets.spreadsheets.values.get({
                    spreadsheetId: config.configSpreadsheet.id,
                    range: config.configSpreadsheet.range[type],
                    valueRenderOption: 'FORMATTED_VALUE'
                })
                return req
            })
            .then(resp => {

                // 2.    updating doc number

                console.log(resp)
                let number = parseInt(resp.result.values[0])
                let req = gapi.client.sheets.spreadsheets.values.update({
                    spreadsheetId: config.configSpreadsheet.id,
                    range: config.configSpreadsheet.range[type],
                    valueInputOption: 'RAW',
                    resource: {
                        range: config.configSpreadsheet.range[type],
                        values: [[number + 1]],
                    },
                    includeValuesInResponse: true
                })
                return req
            })
            .then(resp => {

                // 3.    creating the spreadsheet

                console.log(resp)
                let number = parseInt(resp.result.updatedData.values[0])
                let req = gapi.client.drive.files.create({
                    'mimeType': 'application/vnd.google-apps.spreadsheet',
                    'parents': [`${config.folderId[type]}`],
                    "name": type + '-' + number + '/' + selectedClient.nom,
                    "properties": {
                        type: type,
                        description: 'This is a resume of the tasks to do...',
                        date: Date.now(),
                        clientId: selectedClient.id,
                        number: number,
                    },
                    "fields": 'name, properties, id, webViewLink'
                })
                return req
            })
            .then(resp => {

                // 5.   copying sheet from another SS ( CORS ISSUE IF before step 4 ??? )

                console.log(resp)
                var params = {
                    // The ID of the spreadsheet containing the sheet to copy.
                    spreadsheetId: '1_2Atry0sZ9MJ4VRMDRPC8cVIDWfOnC_k66HYKXfdfS0',
                    // The ID of the sheet to copy.
                    sheetId: 0,
                }
                var copySheetToAnotherSpreadsheetRequestBody = {
                    // The ID of the spreadsheet to copy the sheet to.
                    destinationSpreadsheetId: resp.result.id,
                    // TODO: Add desired properties to the request body.
                };
                var request = gapi.client.sheets.spreadsheets.sheets.copyTo(params, copySheetToAnotherSpreadsheetRequestBody);
                return request
            })
            .then(resp => {

                // 4.   batchUpdate :modifying the sheet

                console.log(resp)
                if (type === 'devis') {
                    let newDevis = resp.result
                    setDevis([newDevis, ...devis])
                } else if (type === 'facture') {
                    let newFacture = resp.result
                    setFactures([newFacture, ...factures])
                }
                let params = {
                    spreadsheetId: resp.result.spreadsheetId
                }
                let batchUpdateValuesRequestBody = {
                    requests: requestBody
                };
                let req = gapi.client.sheets.spreadsheets.batchUpdate(params, batchUpdateValuesRequestBody)
                return req
            })
            .then((resp) => { console.log(resp); setDialogLoader({ open: false })})
            .then((resp) => setSnackbarObject({
                open: true,
                message: type === 'devis' ?
                    `Un devis vient d'être créé dans votre dossier 'Devis` :
                    `Une facture vient d'être créée dans votre dossier 'Factures`
            }))
            .then((resp) => setDocChooserObj({ open: false }))
            .catch(reason => {
                console.log(reason)
            })
    }

And the exact error message I get

Access to fetch at 'https://apis.google.com/_/scs/apps-static/_/js/k=oz.gapi.fr.HrYtnuOsJ9o.O/m=client/rt=j/sv=1/d=1/ed=1/am=wQE/rs=AGLTcCOM4asNhhVgOFJKHWvKD0xkG7mu1Q/cb=gapi.loaded_0' from origin 'http://localhost:3000' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource. If an opaque response serves your needs, set the request's mode to 'no-cors' to fetch the resource with CORS disabled.

and then this ...

> index.js:1 GET https://apis.google.com//scs/apps-static//js/k=oz.gapi.fr.HrYtnuOsJ9o.O/m=client/rt=j/sv=1/d=1/ed=1/am=wQE/rs=AGLTcCOM4asNhhVgOFJKHWvKD0xkG7mu1Q/cb=gapi.loaded_0 net::ERR_FAILED

Here is the requestBody (batchUpdate)


let setWidthRequest = {
    "updateDimensionProperties": {
        "range": {
            "dimension": "COLUMNS",
            "startIndex": 1,
            "endIndex": 2
        },
        "properties": {
            "pixelSize": 500 // 575 before new column has been added
        },
        "fields": "pixelSize"
    }
}

let setWidthRequest2 = {
    "updateDimensionProperties": {
        "range": {
            "dimension": "COLUMNS",
            "startIndex": 1,
            "endIndex": 3
        },
        "properties": {
            "pixelSize": 75
        },
        "fields": "pixelSize"
    }
}

//Keep only 3 columns
let deleteColumnsRequest = {
    "deleteDimension": {
        "range": {
            "dimension": "COLUMNS",
            "endIndex": 30,
            "startIndex": 5
        }
    }
}

// add cool formula to each cell
let prodFormulaRequest = {
    "repeatCell": {
        "range": {
            "startColumnIndex": 4,
            "startRowIndex": 0,
            "endColumnIndex": 4,
            "endRowIndex": 1000
        },
        "cell": {
            "userEnteredValue": {
                "formulaValue": "=IF(ISBLANK(C1);;C1*D1)"
            }
        },
        "fields": "*"
    }
}

let freezeRequest = {
    "updateSheetProperties": {
        "properties": {
            "gridProperties": {
                "frozenRowCount": 1
            }
        },
        "fields": "gridProperties.frozenRowCount"
    }
}

let addTitleRequest = {
    "updateCells": {
        "fields": "*",
        "range": {
            "startColumnIndex": 0,
            "startRowIndex": 0,
            "endColumnIndex": 5,
            "endRowIndex": 1
        },
        "rows": [
            {
                "values": [
                    {
                        "userEnteredValue": {
                            "stringValue": "Ref"
                        },
                        "textFormatRuns": [
                            {
                                "format": {
                                    "bold": true
                                }
                            }
                        ],
                        "effectiveFormat": {
                            "backgroundColor": {
                                "blue": 1
                            },
                            "verticalAlignment": "TOP"
                        }
                    },
                    {
                        "userEnteredValue": {
                            "stringValue": "Description"
                        },
                        "textFormatRuns": [
                            {
                                "format": {
                                    "bold": true
                                }
                            }
                        ],
                        "effectiveFormat": {
                            "backgroundColor": {
                                "blue": 1
                            },
                            "verticalAlignment": "TOP"
                        }
                    },
                    {
                        "userEnteredValue": {
                            "stringValue": "Quantité",
                        },
                        "textFormatRuns": [
                            {
                                "format": {
                                    "bold": true
                                }
                            }
                        ],
                        "effectiveFormat": {
                            "horizontalAlignment": "CENTER",
                            "backgroundColor": {
                                "blue": 1
                            },
                            "verticalAlignment": "TOP"
                        }
                    },
                    {
                        "userEnteredValue": {
                            "stringValue": "Prix Unitaire"
                        },
                        "textFormatRuns": [
                            {
                                "format": {
                                    "bold": true
                                }
                            }
                        ],
                        "effectiveFormat": {
                            "horizontalAlignment": "CENTER",
                            "backgroundColor": {
                                "blue": 1
                            },
                            "verticalAlignment": "TOP"
                        }
                    },
                    {
                        "userEnteredValue": {
                            "stringValue": "Montant"
                        },
                        "textFormatRuns": [
                            {
                                "format": {
                                    "bold": true
                                }
                            }
                        ],
                        "effectiveFormat": {
                            "horizontalAlignment": "CENTER",
                            "backgroundColor": {
                                "blue": 1
                            },
                            "verticalAlignment": "TOP"
                        }
                    },

                ]
            }
        ]
    }
}

let protectionRangeRequest1 = {
    "addProtectedRange": {
        "protectedRange": {
            "range": {
                "startRowIndex": 0,
                "endRowIndex": 1,
                "startColumnIndex": 0,
                "endColumnIndex": 10
            },
            "description": "no touch",
            "warningOnly": true
        },

    }
}

let protectionRangeRequest2 = {
    "addProtectedRange": {
        "protectedRange": {
            "range": {
                "startRowIndex": 0,
                "endRowIndex": 1000,
                "startColumnIndex": 3,
                "endColumnIndex": 4
            },
            "description": "no touch",
            "warningOnly": true
        },
    }
}

let numberFormatRequest = {
    "repeatCell": {
        "range": {
            "startRowIndex": 1,
            "endRowIndex": 1000,
            "startColumnIndex": 1,
            "endColumnIndex": 4
        },
        "cell": {
            "userEnteredFormat": {
                "numberFormat": {
                    "type": "NUMBER",
                    "pattern": "####.00"
                }
            }
        },
        "fields": "userEnteredFormat.numberFormat"
    }
}

let dataValidation = {

    "setDataValidation": {
        "range": {
            "sheetId": 0,
            "startRowIndex": 1,
            "endRowIndex": 1000,
            "startColumnIndex": 0,
            "endColumnIndex": 1
        },
        "rule": {
            "condition": {
                "type": "ONE_OF_RANGE",
                "values": [
                    {
                        "userEnteredValue": "=\'Copie de liste\'!A1:A17"
                    }
                ]
            },
            "inputMessage": "Choose some, son of a bitch !",
            "strict": true,
            "showCustomUi": true
        }
    }
}

export let requestBody = [
    deleteColumnsRequest,
    prodFormulaRequest,
    setWidthRequest,
    addTitleRequest,
    freezeRequest,
    protectionRangeRequest1,
    protectionRangeRequest2,
    numberFormatRequest,
    // metaRequest
]
Ad

Answer

  • You want to know the reason that the order of 1, 2, 3, 5 and 4 occurs an error, while the order of 1, 2, 3, 4 and 5 occurs no error.
    • The requests of 1, 2, 3, 4 and 5 are as follows.
      1. gapi.client.sheets.spreadsheets.values.get
      2. gapi.client.sheets.spreadsheets.values.update
      3. gapi.client.drive.files.create
      4. gapi.client.sheets.spreadsheets.batchUpdate
      5. gapi.client.sheets.spreadsheets.sheets.copyTo
  • You want to achieve your goal using gapi with JavaScript.
  • You have already been able to get and put values for Google Spreadsheet using Sheets API, and also you can create a file using Drive API.

If my understanding is correct, how about this answer?

Issue and workaround:

I think that the reason of your issue is due to that gapi.client.sheets.spreadsheets.sheets.copyTo returns no Spreadsheet ID. I think that this might be the specification at Google side. In your script, you use the Spreadsheet ID retrieved from each request. In this case, when the Spreadsheet ID is not returned, an error occurs. When the order is 1, 2, 3, 4 and 5, the method of "sheets.copyTo" is the last request. By this, no error occurs. On the other hand, when the order is 1, 2, 3, 5 and 4, the method of "batchUpdate" is run after the method of "sheets.copyTo" is run. In this case, the method of "sheets.copyTo" returns no Spreadsheet ID. By this, an error occurs. I think that this brings your error.

Under the order of 1, 2, 3, 5 and 4, in order to avoid the error, I would like to propose to return both the response from gapi.client.sheets.spreadsheets.sheets.copyTo and the Spreadsheet ID.

From your additional requestBody, I could understand that the structure of requestBody is correct.

Modified script:

When your script is modified, it becomes as follows.

From:

.then(resp => {

    // 5.   copying sheet from another SS ( CORS ISSUE IF before step 4 ??? )

    console.log(resp)
    var params = {
        // The ID of the spreadsheet containing the sheet to copy.
        spreadsheetId: '1_2Atry0sZ9MJ4VRMDRPC8cVIDWfOnC_k66HYKXfdfS0',
        // The ID of the sheet to copy.
        sheetId: 0,
    }
    var copySheetToAnotherSpreadsheetRequestBody = {
        // The ID of the spreadsheet to copy the sheet to.
        destinationSpreadsheetId: resp.result.id,
        // TODO: Add desired properties to the request body.
    };
    var request = gapi.client.sheets.spreadsheets.sheets.copyTo(params, copySheetToAnotherSpreadsheetRequestBody);
    return request
})
.then(resp => {

    // 4.   batchUpdate :modifying the sheet

    console.log(resp)
    if (type === 'devis') {
        let newDevis = resp.result
        setDevis([newDevis, ...devis])
    } else if (type === 'facture') {
        let newFacture = resp.result
        setFactures([newFacture, ...factures])
    }
    let params = {
        spreadsheetId: resp.result.spreadsheetId
    }
    let batchUpdateValuesRequestBody = {
        requests: requestBody
    };
    let req = gapi.client.sheets.spreadsheets.batchUpdate(params, batchUpdateValuesRequestBody)
    return req
})

To:

.then(resp => {

    // 5.   copying sheet from another SS ( CORS ISSUE IF before step 4 ??? )

    console.log(resp)
    var params = {
        // The ID of the spreadsheet containing the sheet to copy.
        spreadsheetId: '1_2Atry0sZ9MJ4VRMDRPC8cVIDWfOnC_k66HYKXfdfS0',
        // The ID of the sheet to copy.
        sheetId: 0,
    }
    var copySheetToAnotherSpreadsheetRequestBody = {
        // The ID of the spreadsheet to copy the sheet to.
        destinationSpreadsheetId: resp.result.id,
        // TODO: Add desired properties to the request body.
    };
    var request = gapi.client.sheets.spreadsheets.sheets.copyTo(params, copySheetToAnotherSpreadsheetRequestBody);
    return [request, resp.result.id];  // <--- Modified
})
.then(([resp, spreadsheetId]) => {  // <--- Modified

    // 4.   batchUpdate :modifying the sheet

    console.log(resp)
    if (type === 'devis') {
        let newDevis = resp.result
        setDevis([newDevis, ...devis])
    } else if (type === 'facture') {
        let newFacture = resp.result
        setFactures([newFacture, ...factures])
    }
    let params = {
        spreadsheetId: spreadsheetId  // <--- Modified
    }
    let batchUpdateValuesRequestBody = {
        requests: requestBody
    };
    let req = gapi.client.sheets.spreadsheets.batchUpdate(params, batchUpdateValuesRequestBody)
    return req
})

Note:

  • In this modification, it supposes that requestBody has already been given.

Reference:

Ad
source: stackoverflow.com
Ad