Google Apps Script ハンズオン 2017/08/17

bigquery.gs

function myFunction() {
  
  // シートを取得
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("シート1");
  
  // プロジェクトID、Queryを取得
  var projectId =  sheet.getRange("B1").getValue();
  var query =  sheet.getRange("B2").getValue();
  
  // Queryパラメータを作成
  var queryRequest = BigQuery.newQueryRequest();
  queryRequest.query = query;
  
  // BigQueryにクエリを投げる
  var result = BigQuery.Jobs.query(queryRequest, projectId);
  
  // ヘッダーの取得
  var headers = [];
  for (var i = 0; i < result.schema.fields.length; i++) {
    var headerName = result.schema.fields[i].name;
    headers.push(headerName);
  }
  
  // 結果の取得
  var rows = []
  rows.push(headers);
  for (var i = 0; i < result.rows.length; i++) {
    var resultRow = result.rows[i];
    var row = [];
    
    for (var j = 0; j < resultRow.f.length; j++) {
      row.push(resultRow.f[j].v);
    }
    rows.push(row);
  }
  
  // Sheetsへ書き込み
  sheet.getRange(4, 1, rows.length, headers.length).setValues(rows);
}

mail.gs

function myFunction() {
  MailApp.sendEmail(Session.getActiveUser().getEmail(), "test", "Hello GAS");
}

sheet1.gs

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  for (var row = 2; row <= sheet.getLastRow(); row++ ) {
    
    var familyName = sheet.getRange("A" + row).getValue();
    var firstName = sheet.getRange("B" + row).getValue();
    var email = sheet.getRange("C" + row).getValue();
    MailApp.sendEmail(email, "Test",  familyName + " " + firstName + "さん こんにちは");
  }
}

sheet2.gs

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange().offset(1, 0).getValues();
  
  for (var rowIndex = 0; rowIndex < rows.length; rowIndex++ ) {
    var row = rows[rowIndex];
    
    var familyName = row[0];
    var firstName = row[1];
    var email = row[2];
    
    MailApp.sendEmail(email, "Test",  familyName + " " + firstName + "さん こんにちは");
  }
}