How to Train and Use a Machine Learning Model from Google Sheets with BigQuery ML

. . , . Google  –  Google Workspace,  2 . 

-. , .  ,  40 %.

, . ! . .

 BigQuery ML,  BigQuery, SQL-. , BigQuery ML.  – , .  – .

, . , , . , . BigQuery ML   .

, , . BigQuery ML : ,  AutoML   TensorFlow. ,  – . BigQuery ML .

, . , .   , BigQuery. .

BigQuery ML  Apps Script. JavaScript Google Workspace. Apps Script , BigQuery ML , , .

-   Google   BigQuery. , 12 . .

. , , , ( ). BigQuery ML . , , , BigQuery ML .

BigQuery

. - BigQuery, . .

"" " BigQuery"

, . ! .

, , , . BiqQuery. , . "".

BigQuery ML Model Google

, BigQuery ML .  ml-integration-samples  Google Workspace.

(" > "), Apps Script .    (BigQuery ), API.

, , , .

 onOpen()



 , . , , . ,  train()



 , "".

/**
* Create menu items linked to functions
*/
function onOpen() {
 SpreadsheetApp.getUi()
 .createMenu('BQML')
 .addItem('Train', 'train')
 .addItem('Forecast', 'forecast')
 .addSeparator()
 .addSubMenu(SpreadsheetApp.getUi().createMenu('Configure')
 .addItem('Project', 'configureProject')
 .addItem('Dataset', 'configureDataset'))
 .addToUi();
}
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, . . Range.

BigQuery. .

/**
* Create a forecasting model based on the input data
*/
function train() {
 // ...
 // Populate temporary table in BigQuery with selected data from sheet
 const inputs = SpreadsheetApp.getActiveRange().getValues();
 populateTable(project, table, [DATETIME_COLUMN, DATA_COLUMN], inputs);

 // Create a new model using training data in BigQuery
 const request = {
 query: 'CREATE OR REPLACE MODEL `' + model + '` ' +
 getOptionsStr(CREATE_OPTIONS) + ' AS SELECT * FROM `' + table + '`',
 useLegacySql: false,
 };

 runQuery(request, project);
}
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, . ARIMA ( ), . BigQuery ML  ,  , .

CREATE OR REPLACE MODEL
`sheets_forecast.sheets_forecast_model` OPTIONS( MODEL_TYPE='ARIMA',
 TIME_SERIES_TIMESTAMP_COL='datetime',
 TIME_SERIES_DATA_COL='data') AS
SELECT
*
FROM
`sheets_forecast.sheets_forecast_training_data`
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, "" .  ML.FORECAST(),  .

:

• , .

  
  
  
  
  

• .

  
  
  
  
  

, :

SELECT
FORMAT_TIMESTAMP("%FT%T%Ez", forecast_timestamp),
forecast_value
FROM
ML.FORECAST(MODEL `sheets_forecast.sheets_forecast_model`,
 STRUCT(3 AS horizon))
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

. , – .

Google BigQuery ML, . , , – , .

   , , . , , . , , .

 , . , . , BigQuery ML,  . BigQuery ML, , , , -.

---

Google Cloud: 300 , 20 .   .

: ,