Use Google Workspace to create Google Sheets

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.

   

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

   

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

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:

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

   

   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.

   

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.

   

7. In the newTitle parameter field, enter OrderDetails.

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

   

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.

    

    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.

    

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.

    

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.

    

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.

    

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.

    

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.

    

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

    

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.

    

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

    

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.

    

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'].

    

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.

    

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.

   

   The testing was successful.

   

   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.