Google Sheets Extension ======================= Actable AI enables analysis of data with our provided models directly in your Google Sheets file. This documentation will cover how this can be done, as follows: #. :ref:`How to install the extension` #. :ref:`A Case Study: use Actable AI to predict a continuous value` #. :ref:`Review the prediction model and associated results` #. :ref:`Using the trained model in Google Sheets` #. :ref:`Troubleshooting` .. _gdoc_install: Install the Extension --------------------- .. warning:: Before using the plugin, please ensure that your browser is only linked with one Google account. There is a `known issue `_ with regards to Google add-ons authentication when you have multiple accounts linked in the browser. The Actable AI extension is available on the official `Google Workspace Marketplace `_. To install our extension, follow these steps: #. Open the sheet you want to analyse, then click on *Extensions > Add-ons > Get add-ons*: .. image:: images/gdoc_addon/install-plugin-menu.png :scale: 50% :align: center #. The Google Workspace Marketplace window pops up on top of your sheet. Search for *Actable AI*: .. image:: images/gdoc_addon/install-page.png :scale: 50% :align: center #. Click the install button and grant permission to our plugin. .. note:: We respect users' data and will not keep any of your sheets on our servers. #. Choose the account that you would like to use and fow which access will be allowed: .. image:: images/gdoc_addon/addon-login.png :scale: 35% :align: center #. Congratulations, you have successfully installed the plugin! .. image:: images/gdoc_addon/install-finish.png :scale: 80% :align: center | Once the installation is complete, you should be able to see the Actable AI plugin listed in your Extensions drop-down menu *(Extensions > Actable AI)*. .. image:: images/gdoc_addon/addon-dropdown-menu2.png :scale: 55% :align: center By clicking the *Home* button, the side panel will open and the Actable AI plugin is ready to use. .. image:: images/gdoc_addon/plugin_home.png :scale: 55% :align: center Supported Analytics ------------------- The Actable AI Google Sheet add-on supports multiple analytics: - :ref:`Data Cleanse & Imputation` - :ref:`Correlational Analysis` - :ref:`Causal Interference` - :ref:`Segmentation Analysis` - :ref:`Classification` - :ref:`Regression` - :ref:`Time Series Forecasting` - :ref:`Sentiment Analysis` .. _gdoc_case: Case Study ---------- .. note:: 📺 Video tutorial also available `here `_. For example, we have a sheet that describes apartment rental prices as follows: +-----------------+------+----------+----------------+---------------+----------------+--------------+ | NUMBER_OF_ROOMS | SQFT | LOCATION | DAYS_ON_MARKET | INITIAL_PRICE | NEIGHBORHOOD | RENTAL_PRICE | +=================+======+==========+================+===============+================+==============+ | 2 | 878 | great | 8 | 3861 | berkeley_hills | 3863.323486 | +-----------------+------+----------+----------------+---------------+----------------+--------------+ | 2 | 771 | good | 8 | 3305 | downtown | 3296.687988 | +-----------------+------+----------+----------------+---------------+----------------+--------------+ | 1 | 333 | great | 6 | 2284 | east_elmwood | 2273.651611 | +-----------------+------+----------+----------------+---------------+----------------+--------------+ | 1 | 500 | poor | 54 | 1448 | northwest | 1332.853516 | +-----------------+------+----------+----------------+---------------+----------------+--------------+ | 3 | 1104 | poor | 16 | 4750 | westbrae | | +-----------------+------+----------+----------------+---------------+----------------+--------------+ | 1 | 543 | poor | 18 | 1871 | westbrae | | +-----------------+------+----------+----------------+---------------+----------------+--------------+ | 2 | 872 | good | 14 | 3375 | west_welmwood | 3373.663818 | +-----------------+------+----------+----------------+---------------+----------------+--------------+ | 1 | 673 | great | 7 | 2604 | south_side | 2583.209473 | +-----------------+------+----------+----------------+---------------+----------------+--------------+ As you may observe, some rental prices are missing and we would like to predict them. To achieve this, we can use the regression analytic provided by Actable AI. Let's choose ``RENTAL_PRICE`` as the target and the rest of the columns as the predictors. Moreover, we enable **Explain predictions** to allow us to review the model later. Your setting should look like this in the **Basic** tab: .. image:: images/gdoc_addon/plugin-setup2.png :scale: 75% :align: center More advanced options may also be available in the **Advanced** tab: .. image:: images/gdoc_addon/plugin-setup_advanced.png :scale: 75% :align: center For more information on the parameters available, please refer to the :ref:`regression` documentation. .. note:: Before selecting the prediction target and predictors, and before running the desired analytics, remember to press the 'Refresh' button (shown in the picture below) to ensure that the add-on is using the most up-to-date version of the data in the sheet. .. image:: images/gdoc_addon/refresh-button.png :scale: 75% :align: center .. _gdoc_results: Review Result ------------- Please wait patiently, the model will be ready in a few moments. Once ready, the predicted results are shown in blue: .. image:: images/gdoc_addon/data-result.png :scale: 55% :align: center Actable AI provides not only the results but also the *confidence* of our model. Please check the automatically created sheet that is named using the format **__validation_**: .. image:: images/gdoc_addon/model-validation-sheet-tab.png :scale: 75% :align: center Actable AI provides multiple validation metrics in the **Model Performance** section with which you can evaluate the model that was just trained, such as :ref:`gls_mae`, :ref:`gls_rmse` and :ref:`gls_r2`. Our R2 value for predicted data is approximately 0.999906 in this example. In addition to the validation metrics, Actable AI also indicates which feature contributes the most/least to the target, as shown in the image below. As you may notice, there is also a **Feature-Importance** table in the validation sheet. In this example, the ``INITIAL_PRICE`` contributes the most to the ``RENTAL_PRICE``. However, to understand more about the causes for the rental price, we would strongly recommend that the :ref:`causal_inference` analytic is used. .. image:: images/gdoc_addon/validation-metrics2.png :scale: 55% :align: center Different metrics may also be shown depending on the analytic chosen. For example, a :ref:`confusion matrix` is shown when performing classification. The table at the bottom of this sheet in the **Model Validation** section contains the validation details, as shown in the image below. To provide the model confidence, Actable AI randomly samples a part of the table as the :ref:`gls_validation_set`, and runs the prediction on this subset of data to calculate the errors. The size of the validation sample is controlled by the slider ``validation percentage`` in the control panel. If the **Explain predictions** option is selected, :ref:`Shapley values` corresponding to a feature's contribution on the predicted result is also shown: .. image:: images/gdoc_addon/validation-table2.png :scale: 55% :align: center If any samples contain missing values, these are predicted and shown in **Result Explanation** section: .. image:: images/gdoc_addon/result-explanation.png :scale: 65% :align: center Additional information may also be shown in the sheet, such as details pertaining to the Live API and Google Function: .. image:: images/gdoc_addon/additional_info.png :scale: 55% :align: center .. _gdoc_function: Using the trained model in Google Sheets ---------------------------------------- The trained model can easily be used to predict any other required values in the sheet (or any other sheet). .. image:: images/gdoc_addon/google_function_complete.png :scale: 50% :align: center Given the table shown in the above image, the missing values in cells ``G25:G26`` can be predicted as follows: #. In the newly created sheet, go to the **Google Function** section and copy the value to the right of **taskId**: .. image:: images/gdoc_addon/google_function_taskid.png :scale: 70% :align: center #. In a cell having empty values that need to be predicted, type ``=AAIPREDICT(`` and paste the copied value from the previous step. Enclose the value in double quotes ("): .. image:: images/gdoc_addon/google_function_function.png :scale: 95% :align: center #. Next, type a comma (,) to insert the next parameter to the function, which should correspond to the range of the table headers. In this example, the headers are in the range ``A1:F1``: .. image:: images/gdoc_addon/google_function_headers.png :scale: 95% :align: center #. Next, type another comma (,) to insert the next parameter to the function, which should correspond to the range of cells to be used for the prediction. In this example, the features/columns are in columns ``A:F``, and we would like to predict rows ``25:26``. Hence, the range is ``A25:F26``. Finally, insert the closing bracket: .. image:: images/gdoc_addon/google_function_data.png :scale: 95% :align: center #. After a few moments, the cells will contain the predicted values using the trained model: .. image:: images/gdoc_addon/google_function_result.png :scale: 75% :align: center .. _gdoc_errors: Troubleshooting --------------- Some errors may be displayed whilst using the add-on. The following is a list of some common issues and how they can be resolved: * **ERROR; Unknown function: 'AAIPREDICT'.**: This error might occur occasionally due to an issue from Google's side and there isn't a permanent fix for it yet. As a work-around, please launch the extension by going to *Extensions > Actable AI > Home*. Once the sidebar loads, delete the contents of the cell where the error was raised, and press the 'Enter' key. Then, try using the function again. .. image:: images/gdoc_addon/error_unknown_function.png :scale: 95% :align: center * **"An error occurred. Please make sure the parameters are correct."**: First, please ensure that the parameters passed to the function are correct, as detailed :ref:`above`. If you are confident that the parameters are correct, then please ensure that the extension is launched, by going to *Extensions > Actable AI > Home*. Once the sidebar loads, delete the contents in the cell where the error was raised, and press the 'Enter' key. Then, try using the function again. .. image:: images/gdoc_addon/error_parameters.png :scale: 95% :align: center * **ERROR; Exceeded maximum execution time**: There are time limits imposed in Google Sheets as detailed `here `_, which will be triggerred if the Actable AI extension exceeds this limit. Unfortunately, this limit cannot be changed. However, you can try to limit the number of rows that need to be predicted, in order to lower the amount of execution time required. .. image:: images/gdoc_addon/error_execution_time.png :scale: 90% :align: center * **Nothing happens after I click Run**: Due to an issue with multiple logons on Google, try to log out of all other accounts from Google and try again. * **I still got the issue**: Feel free to use the Live Chat button to chat with one of our customer supports. We might not respond immediately, feel free to leave us your email and we will get back to you as soon as possible. * **How can I delete data and models trained with Actable AI**: Once you click Run, your data shall be uploaded to Actable AI platform to be analyzed. If you don't want your data to be retained on our platform, go to our `web app ` and log in with the same Google account. After logging in, you can delete uploaded data or created analytics on our platform.