/// <summary>
/// 更新工作表名稱
/// </summary>
public static void UpdateSheetTitle(this SheetsService service, string spreadsheetId, string oldName, string newName)
{
var sheet = service.GetSheet(spreadsheetId, oldName);
if (sheet == null)
{
throw new NullReferenceException($"Can't find the spreadsheetId {spreadsheetId}, sheetName {oldName}");
}
var sheetProperties = new SheetProperties
{
SheetId = sheet.Properties.SheetId,
Title = newName
};
var updateSheetPropertiesRequest = new UpdateSheetPropertiesRequest
{
Properties = sheetProperties,
Fields = "title"
};
//Create a new request with containing the addSheetRequest and add it to the requestList
var request = new Request
{
UpdateSheetProperties = updateSheetPropertiesRequest
};
service.BatchUpdateSpreadsheet(spreadsheetId, request);
}
public static void ReadGoogleSheet(string newSheetName, string sheetName, int index, IUoW uow, int clientId, string sheetId, int apiKeySecretId = 20, int accountTypeId = 27)
{
try
{
var service = GetGoogleSpreadSheetService(uow, clientId, sheetId, apiKeySecretId, accountTypeId);
Spreadsheet spreadsheet = service.Spreadsheets.Get(sheetId).Execute();
Sheet sheet = spreadsheet.Sheets.Where(s => s.Properties.Title == sheetName).FirstOrDefault();
int curretSheetId = (int)sheet.Properties.SheetId;
var dd = sheet.Data;
// update new Sheet
var updateSheetRequest = new UpdateSheetPropertiesRequest();
updateSheetRequest.Properties = new SheetProperties();
updateSheetRequest.Properties.Title = sheetName; //Sheet1
updateSheetRequest.Properties.Index = index;
updateSheetRequest.Properties.SheetId = curretSheetId;
updateSheetRequest.Fields = "Index,Title";//fields that needs to be updated.* means all fields from that sheet
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
batchUpdateSpreadsheetRequest.Requests.Add(new Request
{
UpdateSheetProperties = updateSheetRequest
});
var batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, sheetId);
var response = batchUpdateRequest.Execute();
}
catch (Exception ex)
{
throw;
}
}
public void MakeSheetExistAndVisible(string sheetName)
{
var spreadsheets = Service.Spreadsheets.Get(SheetId).Execute();
var sheet = spreadsheets.Sheets.FirstOrDefault(x => x.Properties.Title == sheetName);
if (sheet == null)
{
AddSheetRequest request = new AddSheetRequest
{
Properties = new SheetProperties
{
Hidden = false,
Title = sheetName
}
};
var batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest
{
Requests = new List <Request> {
new Request {
AddSheet = request
}
}
};
Service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, SheetId).Execute();
}
else
{
if (sheet.Properties.Hidden.HasValue && sheet.Properties.Hidden.Value)
{
var newProperties = sheet.Properties;
newProperties.Hidden = false;
UpdateSheetPropertiesRequest request = new UpdateSheetPropertiesRequest
{
Properties = newProperties,
Fields = "*"
};
var batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest
{
Requests = new List <Request> {
new Request {
UpdateSheetProperties = request
}
}
};
Service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, SheetId).Execute();
}
}
}
public static void UpdateSheetAtParticularIndex(string newSheetName, string sheetName, int index, IUoW uow, int clientId, string sheetId, int apiKeySecretId = 20, int accountTypeId = 27)
{
try
{
var service = GetGoogleSpreadSheetService(uow, clientId, sheetId, apiKeySecretId, accountTypeId);
Spreadsheet spreadsheet = service.Spreadsheets.Get(sheetId).Execute();
Sheet sheet = spreadsheet.Sheets.Where(s => s.Properties.Title == sheetName).FirstOrDefault();
int curretSheetId = (int)sheet.Properties.SheetId;
var dd = sheet.Data;
//getting all data from sheet
var valTest = service.Spreadsheets.Values.Get(sheetId, $"{sheetName}!A2:D2").Execute();
var valvalues = valTest.Values;
//adding to specific row
InsertRangeRequest insRow = new InsertRangeRequest();
insRow.Range = new GridRange()
{
SheetId = curretSheetId,
StartRowIndex = 3,
EndRowIndex = 4,
};
insRow.ShiftDimension = "ROWS";
//sorting
SortRangeRequest sorting = new SortRangeRequest();
sorting.Range = new GridRange()
{
SheetId = curretSheetId,
StartColumnIndex = 0, // sorted by firstcolumn for all data after 1st row
StartRowIndex = 1
};
sorting.SortSpecs = new List <SortSpec>
{
new SortSpec
{
SortOrder = "DESCENDING"
}
};
//
InsertDimensionRequest insertRow = new InsertDimensionRequest();
insertRow.Range = new DimensionRange()
{
SheetId = curretSheetId,
Dimension = "ROWS",
StartIndex = 1, // 0 based
EndIndex = 2
};
var oblistt = new List <object>()
{
"Helloins", "This4ins", "was4ins", "insertd4ins"
};
PasteDataRequest data = new PasteDataRequest
{
Data = string.Join(",", oblistt),
Delimiter = ",",
// data gets inserted form this cordinate point( i.e column and row) index and to the right
Coordinate = new GridCoordinate
{
ColumnIndex = 0, // 0 based Col A is 0, col B is 1 and so on
RowIndex = 2, //
SheetId = curretSheetId
},
};
BatchUpdateSpreadsheetRequest r = new BatchUpdateSpreadsheetRequest()
{
Requests = new List <Request>
{
//new Request{ InsertDimension = insertRow },
//new Request{ PasteData = data },
//new Request { InsertRange = insRow},
new Request {
SortRange = sorting
}
}
};
BatchUpdateSpreadsheetResponse response1 = service.Spreadsheets.BatchUpdate(r, sheetId).Execute();
//adding data to sheet
var valueRange = new ValueRange();
var oblist = new List <object>()
{
"Hello466", "This466", "was466", "insertd466"
};
valueRange.Values = new List <IList <object> > {
oblist
};
var appendRequest = service.Spreadsheets.Values.Append(valueRange, sheetId, $"{sheetName}!A:D");
//appendRequest.InsertDataOption = SpreadsheetsResource.ValuesResource.AppendRequest.InsertDataOptionEnum.INSERTROWS;
appendRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.AppendRequest.ValueInputOptionEnum.USERENTERED;
var appendReponse = appendRequest.Execute();
//updating data to sheet
var valueRangeUpdate = new ValueRange();
var oblistUpdate = new List <object>()
{
"Hello4uqq", "This4uqq", "was4uqq", "insertd4uqq"
};
valueRangeUpdate.Values = new List <IList <object> > {
oblistUpdate
};
var updateRequest = service.Spreadsheets.Values.Update(valueRangeUpdate, sheetId, $"{sheetName}!A10:D510");
updateRequest.ValueInputOption = SpreadsheetsResource.ValuesResource.UpdateRequest.ValueInputOptionEnum.USERENTERED;
var updateReponse = updateRequest.Execute();
//deleting row from sheet
var range = $"{sheetName}!A7:G7";
var requestBody = new ClearValuesRequest();
var deleteRequest = service.Spreadsheets.Values.Clear(requestBody, sheetId, range);
var deleteReponse = deleteRequest.Execute();
// update new Sheet
var updateSheetRequest = new UpdateSheetPropertiesRequest();
updateSheetRequest.Properties = new SheetProperties();
updateSheetRequest.Properties.Title = sheetName; //Sheet1
updateSheetRequest.Properties.Index = index;
updateSheetRequest.Properties.SheetId = curretSheetId;
updateSheetRequest.Fields = "Index,Title";//fields that needs to be updated.* means all fields from that sheet
BatchUpdateSpreadsheetRequest batchUpdateSpreadsheetRequest = new BatchUpdateSpreadsheetRequest();
batchUpdateSpreadsheetRequest.Requests = new List <Request>();
batchUpdateSpreadsheetRequest.Requests.Add(new Request
{
UpdateSheetProperties = updateSheetRequest
});
var batchUpdateRequest = service.Spreadsheets.BatchUpdate(batchUpdateSpreadsheetRequest, sheetId);
var response = batchUpdateRequest.Execute();
}
catch (Exception ex)
{
throw;
}
}