Lowdefy
v3.17.2/Tutorial/5. Requests/

5. Requests

We will be saving the data from our form in a Google Sheet, using the GoogleSheet connection. To do this, we will first need to set up a Google Cloud Platform project to get credentials to access the API.

Creating a Google Sheet and getting credentials

Step 5.1

Go to Google Sheets and create a new Google Sheet.

Step 5.2

Go to console.cloud.google.com and sign in.

Step 5.3

Click on the "Select a project" dropdown at the top left, and then choose "NEW PROJECT". Give your project a name and click "Create".

Step 5.4

Go to the "APIs and Services" section, and the click "ENABLE APIS AND SERVICES" button.

Step 5.5

Search for "Sheets" and select the Google Sheets API.

Step 5.6

Click "Enable".

Step 5.7

Click "CREATE CREDENTIALS".

Step 5.8

Choose the following:

  • Which API are you using?: Google Sheets API
  • Where will you be calling the API from? : Web server (e.g. node.js, Tomcat)
  • What data will you be accessing?: Application data
  • Are you planning to use this API with App Engine or Compute Engine?: No, I'm not using them

Then click "What credentials do I need?"

Step 5.9

Choose the following:

  • Give the account a name.
  • Chose "Project > Editor" as the role.
  • Choose JSON as the key type.

Click continue.

Step 5.10

You will be asked to save a file. This file contains the credentials to access your sheet, so make sure to handle it securely. Download the file. Do not commit it to source control.

Step 5.11

In the next section we will be deploying our app using Netlify. To use the private key on Netlify, we need to base64 encode it, since the new-line characters cause problems when used in an environment variable.

You can use the converter below to convert your key. This will output a long stream of characters that are your encoded key.

The base64 conversion happens on the web client. We won't store your key. You can look at how the key is encoded here or convert your key in the browser console using btoa().

By base64 encoding your credentials you have not encrypted them. They are still just as sensitive as before.

Step 5.12

Create a file called .env in your project directory with the following:

.env
LOWDEFY_SECRET_SHEETS_CLIENT_EMAIL=__YOUR_CLIENT_EMAIL__
LOWDEFY_SECRET_SHEETS_PRIVATE_KEY=__YOUR_ENCODED_PRIVATE_KEY__

Fill in the client email from the credentials file and your encoded private key values (without quotes). This will give the development server access to these secrets.

The .env file contains your credentials. Do not commit this file to git. It should be listed in your .gitignore file if you used the CLI init command.

Step 5.13

Because we changed the environment variables, we need to restart the dev server. Run npx lowdefy@latest dev again to restart the server.

Step 5.14

We need to give our service account access to our Google Sheet. Go to your Google Sheet and click the "Share" button. Share the sheet with the client email of the service account we just created, with the "Editor" role.

What happened

We created a Google Cloud Platform (GCP) project, and created a service account in that project that we can use to access Google sheets. This gave us the credentials we need for the GoogleSheet connection.

We set up these credentials for our local dev environment using a .env file. The .env file sets environment variables in the process the server is running in.

Because we set environment variables that start with LOWDEFY_SECRET_, these values become part of the secrets object, and can be accessed using the _secret operator.

Using the Google Sheets connection

Step 5.15

To use a Google Sheet with the Lowdefy connection, we first need to define the columns in the sheet. This will be the same fields as the data we will be saving later.

In the first row of your sheet, add the following column headers:

  • ticket_title
  • ticket_type
  • ticket_description
  • confirm_restart
  • created_date

Step 5.16

In your lowdefy.yaml file, add the following:

lowdefy.yaml
name: lowdefy-project-template
version: CURRENT_LOWDEFY_VERSION

################ -------- Copy from here -------- ################
connections:
  - id: google_sheet
    type: GoogleSheet
    properties:
      client_email:
        _secret: SHEETS_CLIENT_EMAIL
      private_key:
        _base64.decode:
          _secret: SHEETS_PRIVATE_KEY
      sheetIndex: 0
      spreadsheetId: __YOUR_SPREADSHEET_ID__
      write: true
################ ------- Copy to here ----------- ################

menus:
  # ...

Step 5.17

Note down your spreadsheetId. You can find this by looking at the url in your browser when you are looking at your sheet. It should look something like:

https://docs.google.com/spreadsheets/d/{spreadsheetId}/edit#gid=0

where the spreadsheetId is a 44 character random string. Fill in your spreadsheetId in the spreadsheet connection.

Step 5.18

In your new-ticket.yaml file, add the following request:

pages/new-ticket.yaml
id: new-ticket
type: PageHeaderMenu
################ -------- Copy from here -------- ################
requests:
  - id: save_data
    type: GoogleSheetAppendOne
    connectionId: google_sheet
    properties:
      row:
        # Get all the values to save from state
        ticket_title:
          _state: ticket_title
        ticket_type:
          _state: ticket_type
        ticket_description:
          _state: ticket_description
        confirm_restart:
          _state: confirm_restart
        # Add the date the row was created using the `_date.now` operator.
        created_date:
          _date: now
################ ------- Copy to here ----------- ################

properties:
  title: New ticket # The title in the browser tab.
layout:
  contentJustify: center # Center the contents of the page
blocks:
  # ...

Step 5.19

In your new-ticket.yaml file, add the following action to the submit button:

pages/new-ticket.yaml
id: new-ticket
# ...
blocks:
  - id: content_card
    # ...
    blocks:
      - id: page_heading
        # ...
        # ...
        # ...
      - id: submit_button
        type: Button
        #...
        events:
          onClick:
            - id: validate
              type: Validate
            ################ -------- Copy from here -------- ################
            - id: save_data # Make a request to Google Sheets
              type: Request
              params: save_data
            - id: reset # Reset the form once data has been submitted
              type: Reset
            ################ ------- Copy to here ----------- ################

Step 5.20

If you click the submit button, you should see your data submitted to your Google Sheet.

What happened

We set up the column names we will be using in our Google Sheet. We need to do this to use the GoogleSheet connection.

We defined the GoogleSheet connection we will be using in our app, using the credentials we obtained earlier.

We also defined a GoogleSheetAppendOne request, to save the data to our sheet, and called that request when clicking the submit button.

You can find the final configuration files for this section here.

Up next

Up next we will be deploying our app using Netlify. Then it will be live, on the internet, for anyone to see 🚀.