# Delivering data from Yandex Forms to Yandex DataLens using Yandex Cloud Functions and Yandex Query via the management console

# Delivering data from Yandex Forms to Yandex DataLens using Yandex Cloud Functions and Yandex Query via the management console


To set up integration of Forms and DataLens via the management console, follow these steps:

1. [Set up your infrastructure](#prepare-infrastructure).
1. [Create a function in Yandex Cloud Functions](#create-function).
1. [Create a form in Forms](#create-form).
1. [Configure a connection and binding to data in Yandex Query](#yq-integration).
1. [Set up fetching data in DataLens](#set-up-datalens).
1. [Test the new resource integration](#test-forms-integration).

If you no longer need the resources you created, [delete them](#clear-out).

## Get your cloud ready {#prepare-cloud}

Sign up for Yandex Cloud and create a [billing account](../../../billing/concepts/billing-account.md):
1. Navigate to the [management console](https://console.yandex.cloud) and log in to Yandex Cloud or create a new account.
1. On the **[Yandex Cloud Billing](https://center.yandex.cloud/billing/accounts)** page, make sure you have a billing account linked and it has the `ACTIVE` or `TRIAL_ACTIVE` [status](../../../billing/concepts/billing-account-statuses.md). If you do not have a billing account, [create one](../../../billing/quickstart/index.md) and [link](../../../billing/operations/pin-cloud.md) a cloud to it.

If you have an active billing account, you can create or select a [folder](../../../resource-manager/concepts/resources-hierarchy.md#folder) for your infrastructure on the [cloud page](https://console.yandex.cloud/cloud).

[Learn more about clouds and folders here](../../../resource-manager/concepts/resources-hierarchy.md).

### Required paid resources {#paid-resources}

The infrastructure support cost includes:

* Fee for using the Yandex Object Storage bucket (see [Object Storage pricing](../../../storage/pricing.md)).
* Fee for storing and requesting Yandex Lockbox secrets (see [Yandex Lockbox pricing](../../../lockbox/pricing.md)).
* Fee for function calls and computing resources allocated for executing the functions (see [Cloud Functions pricing](../../../functions/pricing.md)).
* Fee for the amount of data read from sources when executing Query queries (see [Query pricing](../../../query/pricing.md)).
* Fee for using DataLens under the service plan (see [DataLens pricing](../../pricing.md)).

## Set up your infrastructure {#prepare-infrastructure}

### Create a service account {#create-service-account}

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), select the relevant folder.
  1. Navigate to **Identity and Access Management**.
  1. Click **Create service account**.
  1. Name the [service account](../../../iam/concepts/users/service-accounts.md): `forms-integration-sa`.
  1. Click ![image](../../../_assets/console-icons/plus.svg) **Add role** and select the following roles: 
      * [lockbox.payloadViewer](../../../iam/roles-reference.md#lockbox-payloadViewer): For reading Yandex Lockbox secrets.
      * [functions.functionInvoker](../../../iam/roles-reference.md#functions-functionInvoker): For invoking Cloud Functions.
      * [storage.editor](../../../iam/roles-reference.md#storage-editor): For reading from and writing to an Object Storage bucket.
      * [yq.viewer](../../../iam/roles-reference.md#query-viewer) and [yq.invoker](../../../iam/roles-reference.md#query-invoker): For integrating DataLens and Query.
  1. Click **Create**.

{% endlist %}

### Create an Object Storage bucket {#create-s3-bucket}

The Object Storage [bucket](../../../storage/concepts/bucket.md) will store data from Forms. 

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), select the relevant folder.
  1. Navigate to **Object Storage**.
  1. Click **Create bucket**.
  1. ** Name** the bucket.
  1. Click **Create bucket**.

{% endlist %}

### Assign access permissions for the bucket to the service account {#assign-access-rights-for-sa}

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), select the relevant folder.
  1. Navigate to **Object Storage**.
  1. Open the bucket you [created](#create-s3-bucket).
  1. Navigate to **Objects**.
  1. Click ![image](../../../_assets/console-icons/ellipsis.svg) and select **Configure ACL**.
  1. In the **ACL editing** window that opens:

      1. Start typing the service account name, `forms-integration`, and select it from the drop-down list.
      1. Select the READ and WRITE  access permissions.
      1. Click **Add**.
      1. Click **Save**.

{% endlist %}

### Create a static key for the service account {#create-static-key-for-sa}

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), select the relevant folder.
  1. Navigate to **Identity and Access Management**.
  1. Select the `forms-integration` service account.
  1. Navigate to **Overview**.
  1. Click **Create new key** and select **Create static access key**.  
  1. Click **Create**.
  1. In the **New key** window that opens, you will see **Key ID** and **Your secret key**. Save them for later.

{% endlist %}

### Create a Yandex Lockbox secret for the static key {#create-lockbox-secret}

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), select the relevant folder.
  1. Navigate to **Lockbox**.
  1. Click **Create secret**.
  1. **Name** the secret: `static-key`.
  1. Under **Secret data**:
      1. Select **Secret type** for **Custom**.
      1. Create the following key-value pairs:
          * Static key ID:

              * **Key**: `static-key-id`.
              * **Value**: Select **Text** and enter the ID of the static key you created earlier.

          * Static key value:

              * **Key**: `static-key-value`.
              * **Value**: Select **Text** and enter the value of the static key you created earlier.

{% endlist %}

## Create and configure a function in Cloud Functions {#set-up-function}

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), select the relevant folder.
  1. Navigate to **Cloud Functions**.
  1. Create a function:
      1. Click **Create function** in the top-right corner.
      1. **Name** the function: `forms-function`.  
      1. Click **Create**.

  1. Create a function version:  
      1. In the **Editor** window that opens, select the runtime: **Python**.
      1. Disable **Add files with code examples**.
      1. Click **Continue**.
      1. Select the function creation **Code source**: **Code editor**.
      1. In the function editor window, click **Create file**.
      1. In the window that opens, enter the file **Name**: `forms-integration.py`. Click **Create**.
      1. Paste the function code to the `forms-integration.py` file:
          
          ```python
          import boto3
          import os
          import json
          from botocore.config import Config

          def get_answers(raw_answers: json) -> json:
              result = {}
              for name, value in raw_answers.get('answer').get('data').items():
                  if name.startswith('answer_choices_'):
                      choices = []
                      for c in value.get('value'):
                          choices.append(c.get('text'))
                      result[name] = ','.join(choices)
                  else:
                      result[name] = value.get('value')
              return result

          def handler(event, context):
              headers = event.get('headers')
              form_id = headers.get('X-Form-Id')
              answer_id = headers.get('X-Form-Answer-Id')

              body_json = json.loads(event.get('body'))
              answers_json = get_answers(body_json)

              aws_access_key_id = os.environ['KEY']
              aws_secret_access_key = os.environ['SECRET_KEY']
              bucket_name = os.environ['BUCKET']

              s3 = boto3.client(service_name='s3',
                                aws_access_key_id=aws_access_key_id,aws_secret_access_key=aws_secret_access_key,
                                endpoint_url='https://storage.yandexcloud.net', region_name='ru-central1',
                                config=Config(request_checksum_calculation="when_required"))

              s3.put_object(Bucket=bucket_name, Key=f"{form_id}/{answer_id}.json", Body=json.dumps(answers_json))

              return {
                  'statusCode': 200,
                  'body': "OK",
                  'headers': {
                      'Content-Type': 'text/plain',
                  }
              }

          ```
          
      1. In the function editor window, click **Create file**.
      1. In the window that opens, enter the file **Name**: `requirements.txt`. Click **Create**.

      1. In `requirements.txt`, specify the `boto3` library for accessing Object Storage from your function:

         ```
         boto3
         ```

      1. In the **Entry point** field, specify `forms-integration.handler`.

      1. In the **Service account** field, select the `forms-integration-sa` account you created.

      1. Under **Environment variables**, create a variable with the bucket name for storing function execution results:

          1. **Key**: `BUCKET`.
          1. **Value**: Name of the bucket you [created earlier](#create-s3-bucket).

      1. Under **Lockbox secrets**:

          1. Create a `KEY` variable for the static key ID. Set the parameters as follows:

              * **Environment variable**: `KEY`.
              * **Secret ID**: Select the `static-key-id` secret.
              * **Version ID**: Select the version tagged `latest`.
              * **Secret key**: Select `static-key-id`.

          1. Create a `SECRET_KEY` variable for the static key ID. Set the parameters as follows:

              * **Environment variable**: `SECRET_KEY`.
              * **Secret ID**: Select the `static-key-value` secret.
              * **Version ID**: Select the version tagged `latest`.
              * **Secret key**: Select `static-key-value`.  

      1. Once your function is created, on the **Overview** tab, enable **Public function**:
          

{% endlist %}

## Create and set up a form in Yandex Forms {#set-up-form}

### Create an API key {#create-api-key}

You need an [API key](../../../iam/concepts/authorization/api-key.md) to set up integration with Forms.

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), select the relevant folder.
  1. Navigate to **Identity and Access Management**.
  1. Select the `forms-integration` service account.
  1. Navigate to **Overview**.
  1. Click **Create new key** and select **Create API key**.
  1. Click **Create**.
  1. In the **New key** window that opens, you will see **Key ID** and **Your secret key**. Save them for later.

{% endlist %}

### Create a form {#create-form}

{% list tabs group=instructions %}

- Forms UI {#forms}

  1. Go to [Forms](https://forms.yandex.com/cloud/admin).

  1. Click **Create form**.

  1. Add at least two fields to the form and set up any questions in them.

  1. Click **Publish**.

  1. Save the link to the published form: you will need it later.

{% endlist %}

### Set up integration with a Cloud Functions function {#set-up-integration}

{% list tabs group=instructions %}

- Forms UI {#forms}

  1. Go to [Forms](https://forms.yandex.com/cloud/admin).

  1. Open the form you created earlier.

  1. Go to the **Settings** tab.

  1. Open the **Additional** section.

  1. Under **Cloud function key**, enter the data of the [previously created API key](#create-api-key) and click **Save**.

  1. Open the **Integrations** tab.

  1. Click **Cloud Functions** to add a condition for the integration.

  1. In the **Function code** field, enter the Cloud Functions function ID. You can copy the ID in the **Overview** section of the Cloud Functions function in the management console.

  1. In the **Show outcome notification** field, select **Show**.

  1. Click **Save**.

{% endlist %}

### Test the integration you created {#test-integration}

1. Follow the public link to the previously created form.

1. Complete the form and click **Submit**.

1. Go to [Forms](https://forms.yandex.com/cloud/admin).

1. Open the form you created earlier.

1. Open the **Integrations** tab.

1. Go to the **Completed integrations** page.

1. Open the completed action record and make sure that the **Response** section contains the `200 — OK` response.

1. Navigate to the [management console](https://console.yandex.cloud) and open the [bucket created earlier](#create-s3-bucket). Make sure the bucket now contains a JSON file with the data from the form you completed.

   The name of the directory the file will reside in matches the form's internal ID. Save this ID for the next steps.

   All the future form completion outcomes will be saved in this directory.

1. Complete the form several times more, and make sure you fill one of the fields in the same way in at least two forms. This will improve data visualization when testing the integration.

## Configure a connection and binding to data in Query {#yq-integration}

{% list tabs group=instruction %}

- Query UI {#yquery}

  1. Open the [management console](https://console.yandex.cloud).
  1. Navigate to **Yandex Query**.
  1. In the **Connections** tab, click ![info](../../../_assets/console-icons/plus.svg) **Create new**.
  1. Create a connection with the following parameters:
      * **Name**: `forms-connection`.
      * **Type**: `Object Storage`.
      * **Bucket**: Name of the bucket you [created earlier](#create-s3-bucket).
      * **Service account**: `forms-integration-sa`.
  1. In the window that opens, set the data binding parameters:
      * **Type**: `Object Storage`.
      * **Connection**: `forms-connection`.
      * **Path**: `/<form_ID>/`.
        You can copy the ID:
          * Under **Objects** in the Object Storage bucket. The name of the folder containing the file with results of the completed form matches the form ID.
          * In the address bar of the [Forms interface](https://forms.yandex.com/cloud/admin), on the form viewing or editing page.        
      * **Format**: `json_each-row`.
      * **Columns**: Create columns for the fields you specified in the form.
        If you want Query to define columns automatically, click **Detect columns**.

{% endlist %}

## Set up fetching data in DataLens {#set-up-datalens}

{% list tabs group=instructions %}

- DataLens UI {#datalens}

  1. Navigate to [DataLens](https://datalens.ru/promo).
  1. Click **Start in cloud**.
  1. In the left-hand panel, select ![image](../../../_assets/console-icons/thunderbolt.svg) **Connections** and click **Create connection**.
  1. Navigate to **Yandex Query** and set the following in the window that opens:
      * **Cloud and folder**: Select the cloud and folder where you created the rest of the resources.
      * **Service account**: `forms-integration-sa`.
  1. Click **Save changes**. In the window that opens, specify `forms-datalens-connection` as the connection name, then click **Create**.
  1. Go back to the [DataLens](https://datalens.ru/?skipPromo=true) main page and click **Create dataset**.
  1. In the connection panel, click ![icon](../../../_assets/console-icons/plus.svg) **Add** and select the `forms-datalens-connection` connection.
  1. Under **Tables**, select the table and drag it to your DataLens workspace.
      After loading, the data from the table will appear in the **Preview** panel.
  1. Click **Save** and enter `forms-integration-dataset` as the dataset name, then click **Create**.

{% endlist %}

## Test the new resource integration {#test-forms-integration}

To check how the created resources work together, open the `forms-integration-dataset` dataset and click **Create chart**. In the window that opens, create a chart using the data from the form. For better visualization, create a chart based on a field with repeating values.

## Delete the resources you created {#clear-out}

Some resources are not free of charge. Delete the resources you no longer need to avoid paying for them:

1. DataLens chart.
1. DataLens dataset.
1. DataLens connection.
1. [Query binding](../../../query/operations/binding.md#delete).
1. [Query connection](../../../query/operations/connection.md#delete).
1. [Cloud Functions function](../../../functions/operations/function/function-delete.md).
1. [Yandex Lockbox secret](../../../lockbox/operations/secret-delete.md).
1. [Object Storage buckets](../../../storage/operations/buckets/delete.md).