-1

Within a SQL Server 2017 Integration Services project, using the CozyRoc REST Connection Manager, I'm trying to use their JavaScript Task to Delete a Sheet in one Google Spreadsheet then copy the same-named Sheet from another Spreadsheet into that first spreadsheet using "CopyTo", then rename the copied sheet to remove the "Copy of . . ." portion of the title.

Sheet 1 is called "CompanyInfo". It has 7 Sheets, one of which is called "Daily Report".

Sheet 2 is called "Daily Report" which has only one sheet called 'Daily Report".

The overall task is simply to automate the process from within SSIS of overwriting the "Daily Report" sheet of the "Company Info" spreadsheet with the "Daily Report" sheet from the "Daily Report" spreadsheet. This is simple enough to do manually from Google Sheets, but it needs to be automated through SSIS.

Looking at the API, this will probably involve deleting the original "Daily Report" sheet from "Company Info," copying the Daily Report sheet from the "Daily Report" spreadsheet into a new tab in the "Company Info" spreadsheet, and then renaming that sheet to remove the "copy of " portion. Three simple BatchUpdate commands is my guess. But everywhere I look all I see are complicated updates to individual sheets, not batch updates.

I've contacted Cdata Software, but their SSIS GoogleSheet task doesn't do a "Copyto". That would make things simple.

I've tried desperately to get some guidance from CozyRoc, but generally all I'm told is to go look at the API reference for Sheet Operations, and that I need to write a web request, which I don't understand how to do.

As a start, I've googled all over for a simple complete little full piece of sample JavaScript code that specifies a Google Spreadsheet ID, a Google Sheet ID, and then deletes the specified sheet from within a web request. All I find anywhere are the little snippets from the API reference, and that's not helping me write the full script.

To be clear, For a number of reasons, I can't do this directly from within Google Sheets using an app script.

I totally get it that this is wrong:

task.run = function () {
    var connection
        variables = this.variables;
    var SheetID = 1145890987;
    var SpreadsheetID = "1r0tsxZ_nKT7EGG5qU6_6JPJ3dLOsZfF8pmdfsdgfagdahjI"
    if (this.parameters.RestConnection.value) {
        connection = this.connections[this.parameters.RestConnection.value].acquire();
        connection.connect();
    }
    else {
        connection = new RestConnection();
    }
    {
        "requests"; [
            {
                "deleteSheet": {
                    "sheetId": SheetID
                }
            }
        ]
    }

I expect the "Daily Report" sheet of the "Company info" spreadsheet to be overwritten by the "Daily Report" sheet of the "Daily Report" spreadsheet whenever I run the SSIS project called "Update Daily Report."

How do I solve this problem?

Emma
  • 27,428
  • 11
  • 44
  • 69
Karen Grube
  • 1
  • 1

1 Answers1

0

As it turns out, this was quite a process.

I ended up creating three basic CozyRoc Javascript tasks within SSIS referencing the API, each task containing just a very simple, short piece of code.

One step that copies the single Google Sheet into the Larger Google Sheet using "copyTo". The result is a sheet in the larger Google Spreadsheet called "Copy of . . ."

A second step that deletes the original Sheet from the larger Google Spreadsheet.

A third step that renames the new "Copy of" in the larger Spreadsheet to the original name.

Basically, the problems I had were simply that the API documentation has no decent real simple, clear examples of any of the three of these functions.

I knew how to construct the Body and URL strings for these three functions, but had a great deal of difficulty with the syntax. It was unclear in the documentation where brackets, braces, and quotation marks were needed in all three of the steps, particularly when you're using SSIS variables for the SpreadsheetId and SheetID. It took me over a period of DAYS to get this all working, when a few better examples of the Batchupdatate and copyTo functions would have helped.

Karen Grube
  • 1
  • 1