using Google.Apis.Auth.OAuth2;

using Google.Apis.Sheets.v4;

using Google.Apis.Sheets.v4.Data;

using Google.Apis.Services;

using System;

using System.Collections.Generic;

using System.IO;

using System.Linq;

using System.Dynamic;

using System.Drawing;

using Color = Google.Apis.Sheets.v4.Data.Color;

namespace SendSNtoGoogle

{

    public class GoogleSheetsHelper

    {

        static string[] Scopes = { SheetsService.Scope.Spreadsheets };

        static string ApplicationName = "GoogleSheetsHelper";

        private readonly SheetsService _sheetsService;

        private readonly string _spreadsheetId;

        public GoogleSheetsHelper(string credentialFileName, string spreadsheetId)

        {

            var credential = GoogleCredential.FromStream(new FileStream(credentialFileName, FileMode.Open)).CreateScoped(Scopes);

            _sheetsService = new SheetsService(new BaseClientService.Initializer()

            {

                HttpClientInitializer = credential,

                ApplicationName = ApplicationName,

            });

            _spreadsheetId = spreadsheetId;

        }

        public List<ExpandoObject> GetDataFromSheet(GoogleSheetParameters googleSheetParameters)

        {

            googleSheetParameters = MakeGoogleSheetDataRangeColumnsZeroBased(googleSheetParameters);

            var range = $"{googleSheetParameters.SheetName}!{GetColumnName(googleSheetParameters.RangeColumnStart)}{googleSheetParameters.RangeRowStart}:{GetColumnName(googleSheetParameters.RangeColumnEnd)}{googleSheetParameters.RangeRowEnd}";

            SpreadsheetsResource.ValuesResource.GetRequest request =

                _sheetsService.Spreadsheets.Values.Get(_spreadsheetId, range);

            var numberOfColumns = googleSheetParameters.RangeColumnEnd - googleSheetParameters.RangeColumnStart;

            var columnNames = new List<string>();

            var returnValues = new List<ExpandoObject>();

            if (!googleSheetParameters.FirstRowIsHeaders)

            {

                for (var i = ; i <= numberOfColumns; i++)

                {

                    columnNames.Add($"Column{i}");

                }

            }

            var response = request.Execute();

            int rowCounter = ;

            IList<IList<Object>> values = response.Values;

            if (values != null && values.Count > )

            {

                foreach (var row in values)

                {

                    if (googleSheetParameters.FirstRowIsHeaders && rowCounter == )

                    {

                        for (var i = ; i <= numberOfColumns; i++)

                        {

                            columnNames.Add(row[i].ToString());

                        }

                        rowCounter++;

                        continue;

                    }

                    var expando = new ExpandoObject();

                    var expandoDict = expando as IDictionary<String, object>;

                    var columnCounter = ;

                    foreach (var columnName in columnNames)

                    {

                        expandoDict.Add(columnName, row[columnCounter].ToString());

                        columnCounter++;

                    }

                    returnValues.Add(expando);

                    rowCounter++;

                }

            }

            return returnValues;

        }

        public void AddCells(GoogleSheetParameters googleSheetParameters, List<GoogleSheetRow> rows)

        {

            var requests = new BatchUpdateSpreadsheetRequest { Requests = new List<Request>() };

            var sheetId = GetSheetId(_sheetsService, _spreadsheetId, googleSheetParameters.SheetName);

            GridCoordinate gc = new GridCoordinate

            {

                ColumnIndex = googleSheetParameters.RangeColumnStart - ,

                RowIndex = googleSheetParameters.RangeRowStart - ,

                SheetId = sheetId

            };

            var request = new Request { UpdateCells = new UpdateCellsRequest { Start = gc, Fields = "*" } };

            var listRowData = new List<RowData>();

            foreach (var row in rows)

            {

                var rowData = new RowData();

                var listCellData = new List<CellData>();

                foreach (var cell in row.Cells)

                {

                    var cellData = new CellData();

                    var extendedValue = new ExtendedValue { StringValue = cell.CellValue };

                    cellData.UserEnteredValue = extendedValue;

                    var cellFormat = new CellFormat { TextFormat = new TextFormat() };

                    if (cell.IsBold)

                    {

                        cellFormat.TextFormat.Bold = true;

                    }

                    cellFormat.BackgroundColor = new Color { Blue = (float)cell.BackgroundColor.B / , Red = (float)cell.BackgroundColor.R / , Green = (float)cell.BackgroundColor.G /  };

                    cellData.UserEnteredFormat = cellFormat;

                    listCellData.Add(cellData);

                }

                rowData.Values = listCellData;

                listRowData.Add(rowData);

            }

            request.UpdateCells.Rows = listRowData;

            // It's a batch request so you can create more than one request and send them all in one batch. Just use reqs.Requests.Add() to add additional requests for the same spreadsheet

            requests.Requests.Add(request);

            _sheetsService.Spreadsheets.BatchUpdate(requests, _spreadsheetId).Execute();

        }

        private string GetColumnName(int index)

        {

            const string letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

            var value = "";

            if (index >= letters.Length)

                value += letters[index / letters.Length - ];

            value += letters[index % letters.Length];

            return value;

        }

        private GoogleSheetParameters MakeGoogleSheetDataRangeColumnsZeroBased(GoogleSheetParameters googleSheetParameters)

        {

            googleSheetParameters.RangeColumnStart = googleSheetParameters.RangeColumnStart - ;

            googleSheetParameters.RangeColumnEnd = googleSheetParameters.RangeColumnEnd - ;

            return googleSheetParameters;

        }

        private int GetSheetId(SheetsService service, string spreadSheetId, string spreadSheetName)

        {

            try

            {

                var spreadsheet = service.Spreadsheets.Get(spreadSheetId).Execute();

                var sheet = spreadsheet.Sheets.FirstOrDefault(s => s.Properties.Title == spreadSheetName);

                int sheetId = (int)sheet.Properties.SheetId;

                return sheetId;

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

    }

    public class GoogleSheetCell

    {

        public string CellValue { get; set; }

        public bool IsBold { get; set; }

        public System.Drawing.Color BackgroundColor { get; set; } = System.Drawing.Color.White;

    }

    public class GoogleSheetParameters

    {

        public int RangeColumnStart { get; set; }

        public int RangeRowStart { get; set; }

        public int RangeColumnEnd { get; set; }

        public int RangeRowEnd { get; set; }

        public string SheetName { get; set; }

        public bool FirstRowIsHeaders { get; set; }

    }

    public class GoogleSheetRow

    {

        public GoogleSheetRow() => Cells = new List<GoogleSheetCell>();

        public List<GoogleSheetCell> Cells { get; set; }

    }

}

            var gsh = new GoogleSheetsHelper.GoogleSheetsHelper("security-details.json", "18p6CMRLbN6L4IViUIbAxce_3ij6HGlPYXkKUPR5ZkGo");

            var row1 = new GoogleSheetRow();

            var row2 = new GoogleSheetRow();

            var cell1 = new GoogleSheetCell() { CellValue = "Header 1", IsBold = true, BackgroundColor = Color.DarkGoldenrod};

            var cell2 = new GoogleSheetCell() { CellValue = "Header 2", BackgroundColor = Color.Cyan };

            var cell3 = new GoogleSheetCell() { CellValue = "Value 1"};

            var cell4 = new GoogleSheetCell() { CellValue = "Value 2"};

            row1.Cells.AddRange(new List<GoogleSheetCell>() {cell1, cell2});

            row2.Cells.AddRange(new List<GoogleSheetCell>() { cell3, cell4 });

            var rows = new List<GoogleSheetRow>() { row1, row2 };

            gsh.AddCells(new GoogleSheetParameters() {SheetName="Sheet44", RangeColumnStart = , RangeRowStart =  }, rows);

var gsh = new GoogleSheetsHelper.GoogleSheetsHelper("Google Sheets-e1ceb012eb0c.json", "18p6CMRLbN6L4IViUIbAxce_3ij6HGlPYXkKUPR5ZkGo");

var gsp = new GoogleSheetParameters() { RangeColumnStart = , RangeRowStart = , RangeColumnEnd = , RangeRowEnd = , FirstRowIsHeaders = true, SheetName = "sheet1" };

var rowValues = gsh.GetDataFromSheet(gsp);

foreach (rowValue in rowValues) {

    var name = rowValue.Name;

    var color = rowValue.FavoriteColor;

    var color = rowValue.Age;

}

foreach (rowValue in rowValues) {

    var name = rowValue.Column1;

    var color = rowValue.Column2;

    var color = rowValue.Column3;

}