Skip to Content

Use Google Workspace to create Google Sheets

Intermediate
25 min.
SAP Build Process Automation, Intermediate, Tutorial, SAP Business Technology Platform, Free Tier
Use Google Workspace to create, retrieve the details and set the values of a Google spreadsheet
You will learn
  • How to use Google Workspace to import data from an excel sheet and create a new Google spreadsheet
  • How to retrieve the details of a Google spreadsheet
  • How to set the values of a specified cells range in Google Sheet
celineaudinsapCéline AudinJanuary 19, 2023
Created by
celineaudinsap
December 16, 2022
Contributors
celineaudinsap

Prerequisites

In this tutorial, you will build an automation that will import data from an excel file, create a Google spreadsheet named Order Details with the values from the excel file. The automation will then create a new Google sheet within the spreadsheet named Updated Order Status with values set to a specified cell range.

  • Step 1

    1. In the Lobby, select the Google Suite project that has been done as part of previous tutorials as mentioned in pre-requisites.

    2. In Build Process Automation, choose Create and then select Automation.

      Create Automation

    3. In the Create Automation window, enter Google Workspace-Sheets as name and An automation to use Google Sheets as a description.

      Create Automation

      You will be navigate to the automation editor of the newly created automation.

  • Step 2

    You will add one input parameter.

    1. In the Automation Details panel, under Input/Output, choose Add new input parameter.

    2. Add an input parameter as follows:

    Field Input Parameter 1
    Name excelPath
    Type String
    Input Parameters
  • Step 3

    1. In the Automation Details panel, under Automations, drag and drop the Google Authorization automation you created.

      Google Authorization Automation

      You will import data and create a new Google spreadsheet.

    2. In the Automation Details panel, under Tools, search for the Import and Create (Google Sheet) and drag and drop it into the workflow.

    3. Select the activity. For the location parameter select fileSystem and for the pathOrDriveFileId select 0 excelPath.

      The location parameter refers to the type of location from which to pick the data to import and the path or drive field parameter refers to the path to the excel file stored on your local machine.

      Import and Create

    4. Now search for the Rename Spreadsheet (Google Sheet) and drag and drop it into the workflow.

      This activity will rename the existing Google spreadsheet.

    5. Select the activity. For the spreadsheetId parameter, select the pencil next to the field to open the expression editor.

    6. In the expression editor, copy and paste the following expression: Step2.spreadSheetDetails.spreadSheetId.

      Rename Spreadsheet

    7. In the newTitle parameter field, enter OrderDetails.

      This will rename the Google spreadsheet that you previously created to OrderDetails.

      Rename Spreadsheet

    8. Search for Get Spreadsheet Details (Google Sheet) activity. Drag and drop it into the workflow.

    9. Select the activity. For the spreadSheetId parameter, select the pencil next to the field to open the expression editor.

    10. In the expression editor, copy and paste the following expression: Step2.spreadSheetDetails.spreadSheetId.

      Get Spreadsheet Details

      Now that you retrieved the details of the Google spreadsheet, you can get the details of a specific row.

    11. Search for the Get Last Row (Google Sheet) activity to retrieve the details of last row. Drag and drop it into the workflow.

    12. Select the activity. For the spreadSheetId parameter, select the pencil next to the field to open the expression editor.

    13. In the expression editor, copy and paste the following expression: Step2.spreadSheetDetails.spreadSheetId.

      Get Last Row

    14. For the sheetTitle parameter, select the pencil next to the field to open the expression editor.

    15. In the expression editor, copy and paste the following expression: Step2.spreadSheetDetails.sheets[0].title.

      This specifies the name of the sheet in which you want to get the last row.

      Get Last Row

    16. Search for the Get Cell Values (Google Sheet) activity. Drag and drop it into the workflow.

      This activity returns the values of the specified cell range in Google.

    17. Select the activity. For the spreadSheetId parameter, select the pencil next to the field to open the expression editor.

    18. In the expression editor, copy and paste the following expression: Step2.spreadSheetDetails.spreadSheetId.

      Get Cell Values

    19. Under the range parameter field, select Custom Data.

    20. For the sheetTitle parameter, select the pencil next to the field to open the expression editor.

    21. In the expression editor, copy and paste the following expression: Step2.spreadSheetDetails.sheets[0].title.

      Get Cell Values

    22. For the startRange parameter, enter A1.

    23. For the endRange parameter, select the pencil next to the field to open the expression editor.

    24. In the expression editor, copy and paste the following expression: "A" + Step5.lastRow.

    25. Finally, for the majorDimension parameter, select rows.

      Get Cell Values

    26. You will now add a sheet. To do so, search for the Add Sheet (Google Sheet) activity. Drag and drop it into the workflow.

    27. Select the activity. For the spreadSheetId parameter, select the pencil next to the field to open the expression editor.

    28. In the expression editor, copy and paste the following expression: Step2.spreadSheetDetails.spreadSheetId.

    29. You will rename this new sheet to Updated Order Status. To do so, search for the Rename Sheet (Google Sheet). Drag and drop it into the workflow.

    30. Select the activity. For the spreadSheetId parameter, select the pencil next to the field to open the expression editor.

    31. In the expression editor, copy and paste the following expression: Step2.spreadSheetDetails.spreadSheetId.

    32. For the sheetId parameter, select the pencil next to the field to open the expression editor.

    33. In the expression editor, copy and paste the following expression: Step7.sheetDetails.sheetId.

      Rename Sheet

    34. For the newTitle parameter, enter UpdatedOrderStatusand select the text in quotes.

      Rename Sheet

    35. Search for the Set Cell Values (Google Sheet) activity. Drag and drop it into the workflow.

      With Set Cell Values (Google Sheet) activity you push the values of a specified cell range in to Google sheet.

    36. Select the activity. For the spreadSheetId parameter, select the pencil next to the field to open the expression editor.

    37. In the expression editor, copy and paste the following expression: Step2.spreadSheetDetails.spreadSheetId.

    38. For the range parameter, select Custom Data.

    39. For sheetTitle parameter, enter UpdatedOrderStatus and select the text in quotes.

    40. For the startRange parameter, enter A1 and select the text in quotes.

    41. For the endRange parameter, select the pencil next to the field to open the expression editor.

    42. In the expression editor, copy and paste the following expression: "A" + Step5.lastRow.

    43. For the values parameter, select the pencil next to the field to open the expression editor.

    44. In the expression editor, copy and paste the following expression: Step6.cellsData.values.

      Set Cell Values

    45. For majorDimension parameter, select rows and for valueInputOption parameter, select userEntered.

      Set Cell Values

    46. Drag and drop another Set Cell Values (Google Sheet) activity.

    47. Select the activity. For the spreadSheetId parameter, select the pencil next to the field to open the expression editor.

    48. In the expression editor, copy and paste the following expression: Step2.spreadSheetDetails.spreadSheetId.

    49. For the range parameter, select Custom Data.

    50. For sheetTitle parameter, enter UpdatedOrderStatus and select the text in quotes.

    51. For the startRange parameter, enter B1 and select the text in quotes.

    52. For the endRange parameter, select the pencil next to the field to open the expression editor.

    53. In the expression editor, copy and paste the following expression: "B" + Step5.lastRow.

      Set Cell Values

    54. For the values parameter, select the pencil next to the field to open the expression editor.

    55. In the expression editor, copy and paste the following expression: ['Order Status', 'Delivered', 'Delivered', 'In Time', 'In Time', 'Delivered', 'In Time', 'Delivered', 'In Time', 'In Time', 'Delivered'].

      Set Cell Values

    56. For the majorDimension parameter, select rows.

      Finally, you need to disconnect the Google Account.

    57. Search for the Disconnect (Google) activity and drag and drop it into the workflow.

    58. Save your work.

      Disconnect Google

    Which activity do you use to fetch values of a specified cell range in Google Sheet?

  • Step 4

    1. Choose Test.

    2. Fill in the Input parameters:

      • For excelPath: the full path to the excel file

    3. Fill in the Environment Variables:

      • For userEmail: your Gmail user email
      • For serviceAccountKeyPath: the full path to the json file

    4. Choose Test.

      Test

      The testing was successful.

      Test result

      If you go to your Google Account>Google Apps>Sheets you will see the created OrderDetails spreadsheet. Upon opening it, you will see the values from the excel file were imported on sheet1 and a new sheet was created named updatedOrderStatus with the corresponding values that were set in your automation.

      Test result
Back to Top
Back to top