Google Sheet轉貼POC
===

方法1：.NET Core Application (Windows Schedule)
--

### 1. 前置作業
* Add package Google.Apis.Sheets.v4

    dotnet add package Google.Apis.Sheets.v4

* Modiify *.csproj
Add a new ItemGroup into csproj

    <Project Sdk="Microsoft.NET.Sdk">
      <PropertyGroup>
        <OutputType>Exe</OutputType>
        <TargetFramework>netcoreapp1.1</TargetFramework>
      </PropertyGroup>
      <ItemGroup>
        <PackageReference Include="Google.Apis.Sheets.v4">
          <Version>1.22.0.806</Version>
        </PackageReference>
      </ItemGroup>
      <ItemGroup>
        <None Update="client_secret.json">
          <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </None>
      </ItemGroup>
    </Project>

### 2. 程式撰寫
#### Get Data

static void ReadEntries()
{
    var range = $"{sheet}!A:F";
    SpreadsheetsResource.ValuesResource.GetRequest request =
            service.Spreadsheets.Values.Get(SpreadsheetId, range);

    var response = request.Execute();
    IList<IList<object>> values = response.Values;
    if (values != null && values.Count > 0)
    {
        foreach (var row in values)
        {
            // Print columns A to F, which correspond to indices 0 and 4.
            Console.WriteLine("{0} | {1} | {2} | {3} | {4} | {5}", row[0], row[1], row[2], row[3], row[4], row[5]);
        }
    }
    else
    {
        Console.WriteLine("No data found.");
    }
}


#### Insert Data

static void CreateEntry() { 
    var range = $"{sheet}!A:F";
    var valueRange = new ValueRange();

    var oblist = new List<object>() { "Hello!", "This", "was", "insertd", "via", "C#" };
    valueRange.Values = new List<IList<object>> { oblist };

    var appendRequest = service.Spreadsheets.Values.Append(valueRange, SpreadsheetId, range);
    appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
    var appendReponse = appendRequest.Execute();
}


#### Update Data

static void UpdateEntry()
{
    var range = $"{sheet}!D543";
    var valueRange = new ValueRange();

    var oblist = new List<object>() { "updated" };
    valueRange.Values = new List<IList<object>> { oblist };

    var updateRequest = service.Spreadsheets.Values.Update(valueRange, SpreadsheetId, range);
    updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
    var appendReponse = updateRequest.Execute();
}

#### Delete Data

static void DeleteEntry()
{
    var range = $"{sheet}!A543:F";
    var requestBody = new ClearValuesRequest();

    var deleteRequest = service.Spreadsheets.Values.Clear(requestBody, SpreadsheetId, range);
    var deleteReponse = deleteRequest.Execute();
}

### 3. Reference
* https://stackoverflow.com/questions/725627/accessing-google-spreadsheets-with-c-sharp-using-google-data-api
* https://www.twilio.com/blog/2017/03/google-spreadsheets-and-net-core.html
* https://developers.google.com/sheets/api/quickstart/dotnet

方法2：Apps Script
--
### 1. 進入[Apps Script](https://www.google.com/script/start/)
點擊Start Scripting

### 2. 建立新專案

### 3. 新增Google Sheets API 服務

### 4. 程式撰寫
紅框處進行javascript撰寫,上方可以執行及偵錯
#### Get Data

function logNamesAndMajors() {
  var spreadsheetId = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms';
  var rangeName = 'Class Data!A2:E';
  var values = Sheets.Spreadsheets.Values.get(spreadsheetId, rangeName).values;
  if (!values) {
    Logger.log('No data found.');
  } else {
    Logger.log('Name, Major:');
    for (var row = 0; row < values.length; row++) {
      // Print columns A and E, which correspond to indices 0 and 4.
      Logger.log(' - %s, %s', values[row][0], values[row][4]);
    }
  }
}

#### Append Data

var values = [
  [
    // Cell values ...
  ]
  // Additional rows ...
];
var valueRange = Sheets.newRowData();
valueRange.values = values;

var appendRequest = Sheets.newAppendCellsRequest();
appendRequest.sheetId = spreadsheetId;
appendRequest.rows = [valueRange];

var result = Sheets.Spreadsheets.Values.append(valueRange, spreadsheetId, range, {
  valueInputOption: valueInputOption
});

### 5. 排程設定
點擊左側清單鬧鐘圖示可設定觸發條件

### 6. Reference
* https://developers.google.com/sheets/api/quickstart/apps-script