plober

Google Sheets Connector: How to create, update and delete multiple objects at once

Blog Post created by plober Employee on Sep 4, 2018

It’s easy to create, update, and delete a single Google Sheet, but do you know how to make changes to multiple objects? In this article, we’ll show you how to build this functionality into any process. Check out this previous post for the basics of working with the Google Sheets connector.

 

 

 

General process

Every process in this article shares a similar pattern. The only difference is in the Input Data needed and the Operation performed by the Google Sheets shape.

The three relevant shapes are:

  • "INPUT DATA" Message shape. This shape varies for each operation, providing the spreadsheetId, Sheet IDs and other relevant input data for running the operation.
  • The "Split Documents" Data process shape. This shape uses the same configuration in every example:

  • The Google Sheets Connector shape. This shape specifies the Action, Connection, and Operation.
    • The Action is selected as: Create, Delete, Get, Query, or Update.
    • The Connection ties it to a Google Drive account that has permission to perform the desired operations. Community article March 2017 Release Deep Dive: Google Sheets Connector provides the steps needed to configure the connection.
    • The Operation defines what the connector shape should do and its configuration is provided for every scenario.

 

How to get the spreadsheetId and sheetId

We need the ID of each spreadsheet we use (named spreadsheetId). The ID is the alphanumeric (including underscores and dashes) string between /d/ and /edit. The sheetId is the numeric string following "gid=" that identifies the particular sheet. The ‘Sheet Title’ is the custom name a user can assign to each Sheet.

 

Create, Update, and Delete spreadsheets through documents

This section covers three scenarios: creating spreadsheets, changing spreadsheet names, and deleting spreadsheets.

 

Create multiple spreadsheets with multiple input documents (Scenario 1)

In this scenario, our goal is to create three different spreadsheets sending three input documents.

  1. Copy the generic Process and set the Message shape to the following text. Make sure to add the quotes properly:
    '{"spreadsheetTitle": "SpreadSheet 1"}
    {"spreadsheetTitle": "SpreadSheet 2"}
    {"spreadsheetTitle": "SpreadSheet 3"}'
  2. Modify the Google Sheet Connector shape:
    1. Set the Action to CREATE.
    2. Select the Connection you want to work with.
    3. Select or create an Operation ("BATCH Create Spreadsheets" is a sensible name)
      1. Click the Import button and set these values:
      2. Click Next.
      3. Select the object type: Spreadsheet.
      4. Click Next, Finish, and Save and Close.
    4. Click OK to close the Connector Shape panel.
  3. Click Save, Test, select your desired Atom, and Run Test.

 

Update spreadsheets titles sending multiple input documents (Scenario 2)

In this scenario, our goal is to update three spreadsheet titles sending three input documents.

  1. Copy the generic Process and set the Message shape to the following text. Change each spreadsheetId to the ID you want to rename. Make sure to add the quotes:
    '{"spreadsheetId": "1TOrhqVJJuwzvxTmmLL178grqUdn0xQSa2-4mc-7l10I", "spreadsheetTitle": "Spreadsheet 1 Updated"}
    {"spreadsheetId": "1dA5OHwTER8jBe3D_RSSumDcvZSDH-1L7w8bhqHquK28","spreadsheetTitle": "Spreadsheet 2 Updated"}
    {"spreadsheetId": "1cdgPl9F5jrQnT38Sk1N2fm4Jns4FT8fDnGdjj9bdoDU","spreadsheetTitle": "Spreadsheet 3 Updated"}'
  2. Modify the Google Sheet Connector shape:
    1. Set the Action to UPDATE.
    2. Choose the Connection you want to work with.
    3. Choose or create an Operation ("BATCH Update Spreadsheets Title" is a sensible name)
      1. Click the Import button and set these values:
      2. Click Next.
      3. Select the object type: Spreadsheet.
      4. Click Next, Finish, and Save and Close.
    4. Click OK to close the Connector Shape panel.
  3. Click Save, Test, select your desired Atom, and Run Test.

 

Delete spreadsheets sending multiple input documents (Scenario 3)

In this scenario, our goal is to delete three spreadsheets sending three input documents.

  1. Copy the generic Process and set the Message shape to the following text. Change each spreadsheetId to the ID that you want to rename. Make sure to add the quotes:
    '<DeleteProfileConfig><id>13UzGmDs0JCirMg7h1CK93Qyr02AxPM3dKvmPTrIb1rk</id></DeleteProfileConfig>
    <DeleteProfileConfig><id>1dA5OHwTER8jBe3D_RSSumDcvZSDH-1L7w8bhqHquK28</id></DeleteProfileConfig>
    <DeleteProfileConfig><id>1cdgPl9F5jrQnT38Sk1N2fm4Jns4FT8fDnGdjj9bdoDU</id></DeleteProfileConfig>'
  2. Modify the Google Sheet Connector shape:
    1. Set the Action to DELETE.
    2. Choose the Connection you want to work with.
    3. Choose or create an Operation ("BATCH Delete Spreadsheet" is a sensible name)
      1. Click the Import button and set these values:
      2. Click Next.
      3. Select the object type: Spreadsheet.
      4. Click Next, Finish, and Save and Close.
    4. Click OK to close the Connector Shape panel.
  3. Click Save, Test, select your desired Atom, and Run Test.

 

Create and Update data through documents

This section covers two scenarios that send multiple input documents: one scenario creates record data, and the other updates record data.

 

Create RecordData sending multiple input documents (Scenario 1)

In this scenario, our goal is to add three rows to a sheet, sending three input documents.

  1. Copy the generic Process and set the Message shape to the following text. Change the spreadsheetId, Sheet Title and the data you want to record. Make sure to add the quotes:
    '{"Last Name": "Trotter", "Name": "Michael", "sheetTitle": "Sheet1", "spreadsheetId": "1TOrhqVJJuwzvxTmmLL178grqUdn0xQSa2-4mc-7l10I"}
    {"Last Name": "Wallace", "Name": "Vanessa", "sheetTitle": "Sheet1", "spreadsheetId": "1TOrhqVJJuwzvxTmmLL178grqUdn0xQSa2-4mc-7l10I"}
    {"Last Name": "Summons", "Name": "William", "sheetTitle": "Sheet1","spreadsheetId": "1TOrhqVJJuwzvxTmmLL178grqUdn0xQSa2-4mc-7l10I"}'
  2. Modify the Google Sheet Connector shape:
    1. Set the Action to CREATE.
    2. Choose the Connection you want to work with.
    3. Choose or create an Operation ("BATCH Record New Data" is a sensible name)
      1. Click the Import button and set these values:

        Note: The option "Has 1st Row of headers?" can be left unchecked. In that case, the fields must be referenced by the column letter in the Message shape.
      2. Click Next.
      3. Select the object type: RecordData.
      4. Click Next, Finish, and Save and Close.
    4. Click OK to close the Connector Shape panel.
  3. Click Save, Test, select your desired Atom, and Run Test.

 

Update RecordData sending multiple input documents (Scenario 2)

In this scenario, our goal is to update three rows in a sheet sending three input documents, as follows:

  1. Copy the generic Process and set the Message shape to the following text. Change the spreadsheetId, Sheet Title, and the data you want to record. Make sure to add the quotes:
    '{"rowIndex": 2 , "A":"Jamie", "B":"Vance", "sheetTitle": "Sheet1", "spreadsheetId": "1TOrhqVJJuwzvxTmmLL178grqUdn0xQSa2-4mc-7l10I"}
    {"rowIndex": 3 , "A":"Julio", "B":"Akers", "sheetTitle": "Sheet1","spreadsheetId": "1TOrhqVJJuwzvxTmmLL178grqUdn0xQSa2-4mc-7l10I"}
    {"rowIndex": 4 , "A":"Ethan", "B":"Verne", "sheetTitle": "Sheet1","spreadsheetId": "1TOrhqVJJuwzvxTmmLL178grqUdn0xQSa2-4mc-7l10I"}'
  2. Modify the Google Sheet Connector shape:
    1. Set the Action to UPDATE.
    2. Choose the Connection you want to work with.
    3. Choose or create an Operation ("BATCH Update Data" is a sensible name)
      1. Click the Import button and set these values:

        Note: The option "Has 1st Row of headers?" can be checked. In that case, the fields must be referenced by their header in the Message shape.
      2. Click Next.
      3. Select the Object Type: RecordData.
      4. Click Next, Finish, and Save and Close.
    4. Click OK to close the Connector Shape panel.
  3. Click Save, Test, select your desired Atom, and Run Test.

 

Create, Update and Delete sheets through documents

This section covers four scenarios: two for creating sheets, and one each for updating and deleting sheets.

 

Create sheets sending multiple input documents (Scenario 1)

In this scenario, our goal is to create three sheets sending three input documents, starting with a spreadsheet that only has Sheet1:

  1. Copy the generic Process and set the Message shape to the following text. Change the spreadsheetId and the desired Sheet Title. Make sure to add the quotes:
    '{"sheetTitle": "Sheet0", "spreadsheetId":"1TOrhqVJJuwzvxTmmLL178grqUdn0xQSa2-4mc-7l10I"}
    {"sheetTitle": "Sheet2", "spreadsheetId":"1TOrhqVJJuwzvxTmmLL178grqUdn0xQSa2-4mc-7l10I"}
    {"sheetTitle": "Sheet3", "spreadsheetId":"1TOrhqVJJuwzvxTmmLL178grqUdn0xQSa2-4mc-7l10I"}'
  2. Modify the Google Sheet Connector shape:
    1. Set the Action to CREATE.
    2. Choose the Connection you want to work with.
    3. Choose or create an Operation ("BATCH Create Sheets" is a sensible name)
      1. Click the Import button and set these values:
      2. Click Next.
      3. Select the object type: Sheet.
      4. Click Next, Finish, and Save and Close.
    4. Click OK to close the Connector Shape panel.
  3. Click Save, Test, select your desired Atom, and Run Test.

 

Create sheets in a specific order sending multiple input documents (Scenario 2)

In this scenario, our goal is to create three sheets sending three input documents in a specific order.

  • The spreadsheet’s status before the process execution is:
  • The spreadsheet’s status after the process execution is:
  1. Copy the generic Process and set the Message shape to the following text. Change the spreadsheetId, Sheet Title, and the place you want them to be created. Make sure to add the quotes:
    '{"sheetIndex": 3, "sheetTitle": "Sheet 2.1", "spreadsheetId": "1TOrhqVJJuwzvxTmmLL178grqUdn0xQSa2-4mc-7l10I"}
    {"sheetIndex": 2, "sheetTitle": "Sheet 1.1", "spreadsheetId": "1TOrhqVJJuwzvxTmmLL178grqUdn0xQSa2-4mc-7l10I"}
    {"sheetIndex": 1, "sheetTitle": "Sheet 0.1", "spreadsheetId": "1TOrhqVJJuwzvxTmmLL178grqUdn0xQSa2-4mc-7l10I"}'

    NOTE: Since the index of each sheet depends upon its position, each new sheet displaces those with a greater or equal index, increasing their indexes by one. It is recommended to avoid creating several sheets at a time, since they may interfere with the others’ indexes. If that is unavoidable, it is recommended to create the sheets from greater to lesser indexes to avoid confusion.
    Here is the initial state of the spreadsheet:

    Here is the state of the spreadsheet when the first sheet is created in the sheetIndex 3:

    Here is the state of the spreadsheet when the second sheet is created in the sheetIndex 2:

    Here is the state of the spreadsheet when the third sheet is created in the sheetIndex 1:
  2. Modify the Google Sheet Connector shape:
    1. Set the Action to CREATE.
    2. Choose the Connection you want to work with.
    3. Choose or create an Operation ("BATCH Insert Sheets" is a sensible name)
      1. Click the Import button and set these values:
      2. Click Next.
      3. Select the Object Type: Sheet.
      4. Click Next, Finish, and Save and Close.
    4. Click OK to close the Connector Shape panel.
  3. Click Save, Test, select your desired Atom, and Run Test.

 

Delete sheets sending multiple input documents (Scenario 3)

In this scenario, our goal is to delete three sheets sending three input documents.

  1. Copy the generic Process and set the Message shape to the following text. Change the spreadsheetId and sheetId to the one you want to delete. The required input format for the ID to delete is spreadsheetId|sheetId. Make sure to add the quotes:
    '<DeleteProfileConfig><id>1TOrhqVJJuwzvxTmmLL178grqUdn0xQSa2-4mc-7l10I|266267403</id></DeleteProfileConfig>
    <DeleteProfileConfig><id>1TOrhqVJJuwzvxTmmLL178grqUdn0xQSa2-4mc-7l10I|83541841</id></DeleteProfileConfig>
    <DeleteProfileConfig><id>1TOrhqVJJuwzvxTmmLL178grqUdn0xQSa2-4mc-7l10I|1490210889</id></DeleteProfileConfig>'
  2. Modify the Google Sheet Connector shape:
    1. Set the Action to DELETE.
    2. Choose the Connection you want to work with.
    3. Choose or create an Operation ("BATCH Delete Sheet" is a sensible name)
      1. Click the Import button and set these values:
      2. Click Next.
      3. Select the Object Type: Sheet.
      4. Click Next, Finish, and Save and Close.
    4. Click OK to close the Connector Shape panel.
  3. Click Save, Test, select your desired Atom, and Run Test.

Congratulations! You have successfully defined operations using the Google Sheets connector to create, update, and delete multiple objects at once.

Common Errors

 

"Row Index needs to be set as positive number"

Full text:

Test execution of Google Sheets Sample completed with errors. Embedded message: Row Index needs to be set as positive number.

This error message will happen if a required "rowIndex" property is not found in one or more objects. If any single object is properly formatted, it will be processed accordingly. Since the name is a case sensitive string, verify it is properly written and its value is a positive integer.

 

Glossary

  • Google Sheets is a web-based application that you can use to create, update and modify spreadsheets and share the data live . For more information, see the Google Sheets website: https://www.google.com/sheets/about
  • Object is an unordered set of name/value pairs. An object begins with { (left brace) and ends with } (right brace). Each name is followed by : (colon) and the name/value pairs are separated by , (comma).
  • Spreadsheet is an  file that contains one or more sheets.
  • SpreadsheetID is the alphanumeric identifier: https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=sheetId
  • Sheet is a matrix (of column and row dimensions) of cells.
  • SheetId is the numeric gid identifier. When positioned at the desired Sheet, it can be found as https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=sheetId. If no gid is shown, you can assume 0 as the default.
  • Cell is the rectangle in a sheet that is the intersection of a column and a row.
  • Column is a group of cells that runs vertically from top-to-bottom. Columns are identified by letters with values from A to ZZZ.
  • Row is a group of cells that runs horizontally from side to side. Rows are identified by numbers.
  • Record Data is an internal concept in the Google Sheets connector which represents the data in a group of cells that runs horizontally from an initial column to a final column. Record data outside of the identified columns is not included in the profile.

Outcomes