private static async System.Threading.Tasks.Task AddForiegnKeysAsync(SheetsService service, List <ForeignKeyAssociaion> foreignKeys, Dictionary <string, string> columnToSheetIndex, string spreadsheetId)
{
foreach (ForeignKeyAssociaion association in foreignKeys)
{
if (!association.Found)
{
continue;
}
string range;
if (!columnToSheetIndex.ContainsKey(association.ReferenceTable + "|" + association.ReferenceField))
{
continue;
}
range = columnToSheetIndex[association.ReferenceTable + "|" + association.ReferenceField];
var updateCellsRequest = new Request()
{
SetDataValidation = new SetDataValidationRequest()
{
Range = new GridRange()
{
SheetId = association.SheetId,
StartRowIndex = 1,
StartColumnIndex = association.StartColumnIndex,
EndColumnIndex = association.StartColumnIndex + 1
},
Rule = new DataValidationRule()
{
Condition = new BooleanCondition()
{
Type = "ONE_OF_RANGE",
Values = new List <ConditionValue>()
{
new ConditionValue()
{
UserEnteredValue = "=" + range,
}
}
},
InputMessage = "It must match associated key",
ShowCustomUi = true,
Strict = true
}
}
};
var requestBody = new Google.Apis.Sheets.v4.Data.BatchUpdateSpreadsheetRequest();
var requests = new List <Request>();
requests.Add(updateCellsRequest);
requestBody.Requests = requests;
var batchRequest = service.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);
await batchRequest.ExecuteAsync().ConfigureAwait(false);
await Throttler.ThrottleCheck().ConfigureAwait(false);
}
}
// set header table in list
public void SetDataHeaderInTable(Data.Spreadsheet response, SheetsService service)
{
string spreadSheetId = response.SpreadsheetId;
List <int?> sheetsId = new List <int?>();
string[] dataHeader = { "Server", "DB", "Size, in GB", "Update date" };
foreach (var item in response.Sheets)
{
sheetsId.Add(item.Properties.SheetId);
}
List <Data.Request> requests = new List <Data.Request>();
List <Data.CellData> values = new List <Data.CellData>();
foreach (string data in dataHeader)
{
values.Add(new Data.CellData()
{
UserEnteredValue = new Data.ExtendedValue()
{
StringValue = data
}
});
}
requests.Add(
new Data.Request
{
UpdateCells = new Data.UpdateCellsRequest()
{
Start = new Data.GridCoordinate()
{
SheetId = sheetsId[0],
RowIndex = 0,
ColumnIndex = 0
},
Rows = new List <Data.RowData> {
new Data.RowData {
Values = values
}
},
Fields = "userEnteredValue"
}
});
Data.BatchUpdateSpreadsheetRequest bUpd = new Data.BatchUpdateSpreadsheetRequest
{
Requests = requests
};
service.Spreadsheets.BatchUpdate(bUpd, spreadSheetId).Execute();
}
public static void appendEmptyColumn(string spreadsheetId = "1rkEhkGsitr3VhKayIJpvdoUsIYOPfJUNimMD09CkMuE", int sheetId = 0)
{
// The A1 notation of a range to search for a logical table of data.
// Values will be appended after the last row of the table.
// string range = "1 - Performance Test Results"; // TODO: Update placeholder value.
//"'1 - Performance Test Results'!A:G"
// The ID of the spreadsheet to update.
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = GetCredential(),
ApplicationName = "RaveReportAutomationUtility",
});
Data.Request reqBody = new Data.Request
{
AppendDimension = new Data.AppendDimensionRequest
{
SheetId = sheetId,
Dimension = "COLUMNS",
Length = 1
}
};
List <Data.Request> requests = new List <Data.Request>();
requests.Add(reqBody);
// TODO: Assign values to desired properties of `requestBody`:
Data.BatchUpdateSpreadsheetRequest requestBody = new Data.BatchUpdateSpreadsheetRequest();
requestBody.Requests = requests;
SpreadsheetsResource.BatchUpdateRequest request = sheetsService.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);
//SpreadsheetsResource.BatchUpdateRequest request = sheetsService.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Data.BatchUpdateSpreadsheetResponse response = request.Execute();
// Data.BatchUpdateSpreadsheetResponse response = await request.ExecuteAsync();
// TODO: Change code below to process the `response` object:
//Console.WriteLine(JsonConvert.SerializeObject(response));
// SpreadsheetsResource.ValuesResource.AppendRequest request =
// sheetsService.Spreadsheets.Values.Append(body, spreadsheetId, range);
// request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
//request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
//var response = request.Execute();
}
private async void HighlightCellAsync(int row, int col)
{
Google.Apis.Sheets.v4.Data.BatchUpdateSpreadsheetRequest requestBody = new Google.Apis.Sheets.v4.Data.BatchUpdateSpreadsheetRequest();
// Send a request that get the range, set the background color to highlight yellow, and set the date as today
requestBody.Requests = new List <Google.Apis.Sheets.v4.Data.Request>()
{
new Request()
{
RepeatCell = new RepeatCellRequest()
{
Range = new GridRange()
{
SheetId = this.SheetID,
StartRowIndex = row,
EndRowIndex = row + 1,
StartColumnIndex = col,
EndColumnIndex = col + 1
},
Cell = new CellData()
{
UserEnteredFormat = new CellFormat()
{
BackgroundColor = new Color()
{
Red = 1,
Green = 1,
Blue = 0
},
},
UserEnteredValue = new ExtendedValue()
{
StringValue = DateTime.Today.ToString("MM/dd/yyyy")
}
},
Fields = "userEnteredValue,userEnteredFormat.backgroundColor"
},
}
};
// Send the request
SpreadsheetsResource.BatchUpdateRequest request = Service.Spreadsheets.BatchUpdate(requestBody, this.SpreadSheetID);
Google.Apis.Sheets.v4.Data.BatchUpdateSpreadsheetResponse response = await request.ExecuteAsync();
Console.WriteLine(JsonConvert.SerializeObject(response));
}
private static async System.Threading.Tasks.Task DeleteOriginalSheet(SheetsService service, string spreadsheetId)
{
try
{
var requestBody = new Google.Apis.Sheets.v4.Data.BatchUpdateSpreadsheetRequest();
var requests = new List <Request>();
DeleteSheetRequest deleteSheet = new DeleteSheetRequest();
deleteSheet.SheetId = await GetSheetIdFromSheetNameAsync(service, spreadsheetId, SheetOneTitle).ConfigureAwait(false);
var deleteRequest = new Request()
{
DeleteSheet = deleteSheet
};
requests.Add(deleteRequest);
requestBody.Requests = requests;
var batchRequest = service.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);
await batchRequest.ExecuteAsync().ConfigureAwait(false);
}
finally
{
await Throttler.ThrottleCheck().ConfigureAwait(false);
}
}
public static async System.Threading.Tasks.Task InsertColumnNoteAsync(SheetsService service, string spreadsheetId, int sheetId, int columnNo, string comment)
{
try
{
var gridRange = new Google.Apis.Sheets.v4.Data.GridRange
{
EndColumnIndex = columnNo + 1,
StartColumnIndex = columnNo,
EndRowIndex = 1,
StartRowIndex = 0,
SheetId = sheetId
};
var request = new Google.Apis.Sheets.v4.Data.Request();
request.UpdateCells = new Google.Apis.Sheets.v4.Data.UpdateCellsRequest();
request.UpdateCells.Range = gridRange;
request.UpdateCells.Fields = "note";
request.UpdateCells.Rows = new List <Google.Apis.Sheets.v4.Data.RowData>();
request.UpdateCells.Rows.Add(new Google.Apis.Sheets.v4.Data.RowData());
request.UpdateCells.Rows[0].Values = new List <Google.Apis.Sheets.v4.Data.CellData>();
request.UpdateCells.Rows[0].Values.Add(new Google.Apis.Sheets.v4.Data.CellData());
request.UpdateCells.Rows[0].Values[0].Note = comment;
var requests = new List <Request>();
requests.Add(request);
var requestBody = new Google.Apis.Sheets.v4.Data.BatchUpdateSpreadsheetRequest();
requestBody.Requests = requests;
var batchRequest = service.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);
await batchRequest.ExecuteAsync().ConfigureAwait(false);
}
finally
{
await Throttler.ThrottleCheck().ConfigureAwait(false);
}
}
// set servers data in list
public void SetDataInTable(Data.Spreadsheet response, SheetsService service, List <Server> servers)
{
string spreadSheetId = response.SpreadsheetId;
List <int?> sheetsId = new List <int?>();
string currentDate = DateTime.Now.ToString();
foreach (var item in response.Sheets)
{
sheetsId.Add(item.Properties.SheetId);
}
for (int i = 0; i < servers.Count; i++)
{
Server server = servers[i];
int? sheetId = sheetsId[i];
string[,] data = new string[server.dbData.Count, 4];
foreach (string key in server.dbData.Keys)
{
data.SetValue(server.name, 0, 0);
data.SetValue(key, 0, 1);
data.SetValue(server.dbData[key], 0, 2);
data.SetValue(currentDate, 0, 3);
server.dbData.Remove(key);
break;
}
int count = 1;
foreach (string key in server.dbData.Keys)
{
data.SetValue(" ", count, 0);
data.SetValue(key, count, 1);
data.SetValue(server.dbData[key], count, 2);
data.SetValue(" ", count, 3);
++count;
}
List <Data.Request> requests = new List <Data.Request>();
for (int j = 0; j < data.GetLength(0); j++)
{
List <Data.CellData> values = new List <Data.CellData>();
for (int k = 0; k < data.GetLength(1); k++)
{
values.Add(new Data.CellData()
{
UserEnteredValue = new Data.ExtendedValue()
{
StringValue = data[j, k]
}
});
}
requests.Add(
new Data.Request
{
UpdateCells = new Data.UpdateCellsRequest()
{
Start = new Data.GridCoordinate()
{
SheetId = sheetId,
RowIndex = j + 1,
ColumnIndex = 0
},
Rows = new List <Data.RowData> {
new Data.RowData {
Values = values
}
},
Fields = "userEnteredValue"
}
});
Data.BatchUpdateSpreadsheetRequest bUpd = new Data.BatchUpdateSpreadsheetRequest
{
Requests = requests
};
service.Spreadsheets.BatchUpdate(bUpd, spreadSheetId).Execute();
}
string[] dataBasement = { server.name, "Volume", server.size, currentDate };
List <Data.CellData> valuesB = new List <Data.CellData>();
foreach (string dataB in dataBasement)
{
valuesB.Add(new Data.CellData()
{
UserEnteredValue = new Data.ExtendedValue()
{
StringValue = dataB
}
});
}
requests.Add(
new Data.Request
{
UpdateCells = new Data.UpdateCellsRequest()
{
Start = new Data.GridCoordinate()
{
SheetId = sheetId,
RowIndex = server.dbData.Count + 1,
ColumnIndex = 0
},
Rows = new List <Data.RowData> {
new Data.RowData {
Values = valuesB
}
},
Fields = "userEnteredValue"
}
});
Data.BatchUpdateSpreadsheetRequest bUpsRequest = new Data.BatchUpdateSpreadsheetRequest
{
Requests = requests
};
service.Spreadsheets.BatchUpdate(bUpsRequest, spreadSheetId).Execute();
}
}
public static void mergeCells(int startCol, int startRow, string spreadsheetId = "1rkEhkGsitr3VhKayIJpvdoUsIYOPfJUNimMD09CkMuE", int sheetId = 0)
{
// The A1 notation of a range to search for a logical table of data.
// Values will be appended after the last row of the table.
//"'1 - Performance Test Results'!A:G"
// The ID of the spreadsheet to update.
// string spreadsheetId = spreadsheetIDTextbox.Text; //Impliment this for the input entered in the textbox
SheetsService sheetsService = new SheetsService(new BaseClientService.Initializer
{
HttpClientInitializer = GetCredential(),
ApplicationName = "RaveReportAutomationUtility",
});
Data.Request reqBody = new Data.Request
{
MergeCells = new Data.MergeCellsRequest
{
Range = new Data.GridRange
{
SheetId = sheetId,
StartRowIndex = startRow,
EndRowIndex = startRow + 1,
StartColumnIndex = startCol,
EndColumnIndex = startCol + 9
},
MergeType = "MERGE_ROWS"
}
};
List <Data.Request> requests = new List <Data.Request>();
requests.Add(reqBody);
// TODO: Assign values to desired properties of `requestBody`:
Data.BatchUpdateSpreadsheetRequest requestBody = new Data.BatchUpdateSpreadsheetRequest();
requestBody.Requests = requests;
SpreadsheetsResource.BatchUpdateRequest request = sheetsService.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);
//SpreadsheetsResource.BatchUpdateRequest request = sheetsService.Spreadsheets.BatchUpdate(requestBody, spreadsheetId);
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Data.BatchUpdateSpreadsheetResponse response = request.Execute();
// Data.BatchUpdateSpreadsheetResponse response = await request.ExecuteAsync();
// TODO: Change code below to process the `response` object:
//Console.WriteLine(JsonConvert.SerializeObject(response));
// SpreadsheetsResource.ValuesResource.AppendRequest request =
// sheetsService.Spreadsheets.Values.Append(body, spreadsheetId, range);
// request.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
//request.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.RAW;
//var response = request.Execute();
}
public ApiResponse GetSheetsBySpreadSheetId(string id)
{
ApiResponse res = new ApiResponse();
try
{
var r = this.Auth();
if (r.Code != System.Net.HttpStatusCode.OK)
{
return(r);
}
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = "Punnel",
});
var result = service.Spreadsheets.Get(id).Execute();
if (result != null)
{
string sheet_name = "Punnel";
var k = result.Sheets.FirstOrDefault(x => x.Properties.Title.ToLower() == "punnel");
if (k != null)
{
Core.Entities.Integration.GoogleSheet.SpreadPunnelSheetViewModel result_data1 = new Core.Entities.Integration.GoogleSheet.SpreadPunnelSheetViewModel()
{
Id = result.SpreadsheetId,
Name = result.Properties.Title,
SheetId = k.Properties.SheetId
};
res.Data = result_data1;
res.Code = System.Net.HttpStatusCode.OK;
}
else
{
List <Google.Apis.Sheets.v4.Data.Request> requests = new List <Google.Apis.Sheets.v4.Data.Request>();
requests.Add(new Google.Apis.Sheets.v4.Data.Request()
{
AddSheet = new Google.Apis.Sheets.v4.Data.AddSheetRequest()
{
Properties = new Google.Apis.Sheets.v4.Data.SheetProperties()
{
Title = sheet_name
}
}
});
Google.Apis.Sheets.v4.Data.BatchUpdateSpreadsheetRequest requestBody = new Google.Apis.Sheets.v4.Data.BatchUpdateSpreadsheetRequest();
requestBody.Requests = requests;
var res_updated = service.Spreadsheets.BatchUpdate(requestBody, result.SpreadsheetId).Execute();
result = service.Spreadsheets.Get(result.SpreadsheetId).Execute();
var pnsheet = result.Sheets[result.Sheets.Count - 1];
Core.Entities.Integration.GoogleSheet.SpreadPunnelSheetViewModel result_data = new Core.Entities.Integration.GoogleSheet.SpreadPunnelSheetViewModel()
{
Id = result.SpreadsheetId,
SheetId = pnsheet.Properties.SheetId
};
res.Data = result_data;
res.Code = System.Net.HttpStatusCode.OK;
}
}
}
catch (Exception ex)
{
_log.Error(ex);
res.Message = "Không tìm thấy Spread Id: " + id;
}
return(res);
}
