Coefficient's SQL Parameter feature gives additional flexibility when writing custom SQL queries for your imports. Using this feature, you can dynamically reference a cell/range of cells on your spreadsheet, which automatically captures the value from the cell, allowing you to run your query without updating it manually! How cool is that, huh? 😎
SQL Parameters with Google Sheets
What are SQL Parameters?
When using a custom SQL query with Coefficient, you can now add a Parameter that will dynamically point to a specific cell/range of cells/values in your sheet. This will allow you to create complex queries + Parameters that allow your teammates to enter the needed data in a cell for the import to run.
SQL Parameters with Google Sheets
SQL Parameters can be used with any Coefficient Integration with custom SQL queries as an import option. These include MySQL, PostgreSQL, MS SQL, Snowflake, RedShift, etc.
Example: Using MySQL, set up a SQL Parameter to dynamically set the value of the “film.release_year” column, based on the value of B1 in the sheet.
|
1. To set the SQL parameter, on a new sheet, go to cell “B1” and then enter “2006”. This value will be used in the film.release_year column. |
2. Select “Import from…” from the main menu. |
3. Select MySQL and choose “Custom SQL Query”. |
4. The Import Preview window will appear. You will see all your previously created parameters listed, but you can always add new ones.
ℹ️ NOTE: The parameters listed are other parameters in the current spreadsheet and are not shared across spreadsheets.
5. Click the “Add another parameter” button to add your new SQL Parameter. Each parameter name must be UNIQUE. You will need to manually enter the cell/range of cells referenced in your Sheet.
ℹ️ NOTE: The format of your cell/range should be ‘<Name of your sheet>’!<cell or range of cells>”. (Example: “Import1!A1” or “Import1!A1:D1”)’
6. Add your query to this import. If you are using the parameter, you MUST include “{{” before and “}}” after for it to be valid. Click the “Refresh Preview” button to see how your data is shown.
ℹ️ PRO TIP: You can start typing “{{” and a list of available variables should appear so you do not have to copy and paste it. Make sure you place “}}” at the end.
ℹ️ NOTE: The system should automatically list your parameters for easy selection once you type in “{{”.
7. Give your import a UNIQUE name, and click “Import”.
8. Coefficient should now capture the value we entered on our parameter cell. Your import now shows all the films released in 2006. 🎉 🙌
SQL Parameters with Excel
Example: Using MySQL, set up a SQL Parameter to dynamically set the “actor_id” column based on the value of B1 in another sheet.
|
1. To set the SQL parameter, on a new tab/sheet, go to cell “B1” and then enter “2”. This value will be used in the film.actor_id column. |
2. Select “Import from…” |
3. Select MySQL and choose “Custom SQL Query”. |
4. The Import Preview window will appear. You will see all your previously created parameters listed, but you can always add new ones.
ℹ️ NOTE: The parameters listed are other parameters in the current spreadsheet and are not shared across spreadsheets.
5. Click the “Add another parameter” button to add your new SQL Parameter. Each parameter name must be UNIQUE. You will need to manually enter the cell/range of cells referenced in your Sheet.
ℹ️ NOTE: The format of your cell/range should be ‘<Name of your sheet>’!<cell or range of cells>”. (Example: 'SQL Parameters'!B1 or 'SQL Parameters'!B2)
6. Add your query to this import. If you are using the parameter, you MUST include “{{” before and “}}” after for it to be valid. Click the “Refresh Preview” button to see how your data is shown.
ℹ️ PRO TIP: You can start typing “{{” and a list of available variables should appear so you do not have to copy and paste it. Make sure you place “}}” at the end.
ℹ️ NOTE: The system automatically lists your parameters for easy selection once you type in “{{”.
7. Give your import a UNIQUE name, and click “Import”.
8. Coefficient now captures the value we entered on our parameter cell. Your import now shows all the films under Actor Id = 2. 🎉 🙌
FAQs for SQL Parameters
Is there a limit to how many parameters I can create and use for my query?
You can create and use up to 50 parameters in your SQL query. 🙌
What data types are supported when creating a parameter?
SQL Parameters support the following data types: Boolean, Number, Date, and String.
Can the cell(s) referencing my SQL Parameter contain formulas?
Yes, SQL Parameters can be defined on any Google Sheet, and the referencing cell(s) can include formulas. When creating the parameter, just ensure that the correct sheet name and range are specified. Cells with formulas are not supported in Excel.
Can I use a range of cells with SQL Parameters?
Yes, you can use a range of cells with SQL Parameters, allowing the range to function as a list within your query.
Can variables be shared between spreadsheets?
Variables are specific to the current spreadsheet. While they can be used across different imports within the same spreadsheet, they cannot be shared between separate spreadsheets.