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

Kind: global function

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.

Kind: global function

showConfiguration()

Function to show the user the current document (Spreadsheet) configuration.

Kind: global function

appendData()

Function linked to the menu. Run the data manipulation with parameter append data.

Kind: global function

updateData()

Function linked to the menu. Run the data manipulation with parameter update data.

Kind: global function

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.