BigQuerySheet-Manipulation
Google App Scripts to link Google sheets to bigquery table
Before you edit the source code
Before you edit the source code note that every functions below are already implemented in the workflow and can stop working if you change anything. Be sure to check the whole code before editing.
Functions
- onOpen()
Function to trigger the menu creation when the spreadsheet is open by the user
- updateConfiguration()
Function to update the current document (Spreadsheet) configuration. With this all the collaborator using a same Spreadsheet can execute event with the same configuration.
- showConfiguration()
Function to show the user the current document (Spreadsheet) configuration.
- appendData()
Function linked to the menu. Run the data manipulation with parameter append data.
- updateData()
Function linked to the menu. Run the data manipulation with parameter update data.
- manipulateData(update)
Function to gather the information, the data and the job that need to be done
- checkIfSheetExists() ⇒
Sheet|null Function that check if the Sheet exists in the current Spreadsheet.
- checkIfDatasetExists(projectId, datasetId) ⇒
Bool Function that check if the dataset in the configuration exists in the GCP project defined by the Project ID
- insertData(projectId, blob, datasetId, tableId, update)
Function that create insert data job in BigQuery.
onOpen()
Function to trigger the menu creation when the spreadsheet is open by the user
updateConfiguration()
Function to update the current document (Spreadsheet) configuration. With this all the collaborator using a same Spreadsheet can execute event with the same configuration.
showConfiguration()
Function to show the user the current document (Spreadsheet) configuration.
appendData()
Function linked to the menu. Run the data manipulation with parameter append data.
updateData()
Function linked to the menu. Run the data manipulation with parameter update data.
manipulateData(update)
Function to gather the information, the data and the job that need to be done
Kind: global function
| Param | Type | Description |
|---|---|---|
| update | Bool |
This parameter define if data need to be truncated (true) or simply append to the current data in BigQuery. |
checkIfSheetExists() ⇒ Sheet | null
Function that check if the Sheet exists in the current Spreadsheet.
Kind: global function
Returns: Sheet | null - - Return the sheet object if it's found or null if not.
checkIfDatasetExists(projectId, datasetId) ⇒ Bool
Function that check if the dataset in the configuration exists in the GCP project defined by the Project ID
Kind: global function
Returns: Bool - - Return true if the process work else it return false.
| Param | Type | Description |
|---|---|---|
| projectId | String |
Project name that should appear in the configuration. |
| datasetId | String |
Dataset name that should appear in the configuration. |
insertData(projectId, blob, datasetId, tableId, update)
Function that create insert data job in BigQuery.
Kind: global function
| Param | Type | Description |
|---|---|---|
| projectId | String |
Project name that should appear in the configuration. |
| blob | Blob |
The CSV blob to import in BigQuery with all fetched data. |
| datasetId | String |
Dataset name that should appear in the configuration. |
| tableId | String |
Table name that should appear in the configuration. |
| update | Bool |
This parameter define if data need to be truncated (true) or simply append to the current data in BigQuery. |