public AppendCellsRequest AddAppendCells(int sheetId, string fields = "*")
{
var appendCellsRequest = new AppendCellsRequest {
SheetId = sheetId, Fields = fields, Rows = new List <RowData>()
};
this.Requests.Add(new Request {
AppendCells = appendCellsRequest
});
return(appendCellsRequest);
}
/// <summary>
/// Creates a reques to append cells at the bottom of existing data
/// </summary>
/// <param name="spreadsheetId">The id of the sheets doc</param>
/// <param name="tabName">The name of the tab to append cells to</param>
/// <param name="rowData">List of rows to append</param>
/// <param name="fieldMask">Set this to a field of the request to only override that field</param>
/// <returns></returns>
private static Request requestAppendCells(string spreadsheetId, string tabName, List <RowData> rowData, string fieldMask = "*")
{
AppendCellsRequest appendCells = new AppendCellsRequest();
appendCells.SheetId = getTabIndex(spreadsheetId, tabName);
appendCells.Rows = rowData;
appendCells.Fields = fieldMask;
Request request = new Request();
request.AppendCells = appendCells;
return(request);
}
private static AppendCellsRequest CreateAppendCellRequest(int sheetId, DataColumnCollection columns, int fgColorHeader, int bgColorHeader, bool doFullFormatting)
{
var rowData = CreateRowData(sheetId, columns, fgColorHeader, bgColorHeader, doFullFormatting);
var rowDataList = new List <RowData>();
rowDataList.Add(rowData);
var appendRequest = new AppendCellsRequest();
appendRequest.SheetId = sheetId;
appendRequest.Rows = rowDataList;
appendRequest.Fields = "*";
return(appendRequest);
}
private static AppendCellsRequest CreateAppendCellRequest(int sheetId, DataRowCollection rows, int fgColorRow, int bgColorRow)
{
var rowDataList = new List <RowData>();
foreach (DataRow row in rows)
{
var rowData = CreateRowData(sheetId, row, fgColorRow, bgColorRow);
rowDataList.Add(rowData);
}
var appendRequest = new AppendCellsRequest();
appendRequest.SheetId = sheetId;
appendRequest.Rows = rowDataList;
appendRequest.Fields = "*";
return(appendRequest);
}
public UploadSheetTransaction(SpreadsheetAccess spreadsheetAccess, SheetProperties sheet)
{
this.requestBuilder = spreadsheetAccess.CreateBatchRequestBuilder();
this.sheetId = sheet.SheetId.Value;
var deleteRange = this.requestBuilder.AddDeleteRange(this.sheetId);
deleteRange.Range.StartRowIndex = 1;
deleteRange.Range.EndRowIndex = sheet.GridProperties.RowCount - 1;
var cellsUpdater = this.requestBuilder.AddUpdateCells(this.sheetId);
this.firstRow = cellsUpdater.Rows.AddRow();
this.appender = this.requestBuilder.AddAppendCells(this.sheetId);
this.requestBuilder.AddAutoResizeDimensions(this.sheetId);
this.spreadsheetAccess = spreadsheetAccess;
}
public virtual void AddPixelsForUser(string userName, int numPixelsToAdd, string actingUser)
{
SheetsService service;
IList <IList <object> > values;
GetSheetService(out service, out values);
userName = userName.ToLowerInvariant().Trim();
var thisRow = values.FirstOrDefault(r => r[0].ToString().Trim().ToLowerInvariant() == userName);
if (thisRow == null)
{
Logger.LogDebug($"Adding row for {userName}");
var rangeToSet = new List <IList <object> > {
new List <object> {
userName, numPixelsToAdd
}
};
var append = new AppendCellsRequest();
append.SheetId = 0;
append.Fields = "*";
var newRow = new RowData()
{
Values = new List <CellData> {
}
};
newRow.Values.Add(new CellData {
UserEnteredValue = new ExtendedValue {
StringValue = userName
}
});
newRow.Values.Add(new CellData {
UserEnteredValue = new ExtendedValue {
NumberValue = numPixelsToAdd
}, UserEnteredFormat = new CellFormat {
HorizontalAlignment = "CENTER"
}
});
append.Rows = new[] { newRow };
var appendRequest = new Request();
appendRequest.AppendCells = append;
service.Spreadsheets.BatchUpdate(new BatchUpdateSpreadsheetRequest {
Requests = new[] { appendRequest }
}, Spreadsheet).Execute();
ResortSpreadsheet(service);
LogActivityOnSheet(service, actingUser, userName, "Add", numPixelsToAdd);
Twitch.BroadcastMessageOnChannel($"Successfully granted {userName} their first {numPixelsToAdd} {_CurrencyName}!");
}
else
{
Logger.LogDebug($"Updating row for {userName}");
var pos = values.IndexOf(thisRow);
var newValue = (int.Parse(thisRow.Count < 2 ? "0" : thisRow[1].ToString())) + numPixelsToAdd;
if (newValue < 0)
{
newValue = 0;
}
var rangeToSet = new List <IList <object> > {
new List <object> {
newValue
}
};
UpdateRequest update = service.Spreadsheets.Values.Update(
new Google.Apis.Sheets.v4.Data.ValueRange {
Values = rangeToSet, Range = $"Pixels!B{4 + pos}"
},
Spreadsheet,
$"Pixels!B{4 + pos}"
);
update.ValueInputOption = UpdateRequest.ValueInputOptionEnum.USERENTERED;
update.Execute();
LogActivityOnSheet(service, actingUser, userName, "Add", numPixelsToAdd);
Twitch.BroadcastMessageOnChannel($"Successfully granted {userName} an additional {numPixelsToAdd} {_CurrencyName}. Their new total is {newValue} {_CurrencyName}");
}
}
public GoogleSheetsExport(string spreadsheetId, Structs.SoundRedemptionLogic soundRedemptionLogic, char prefixCharacter, List <Structs.SoundEntry> soundsEntries)
{
WasSuccess = false;
var batchRequests = new BatchUpdateSpreadsheetRequest()
{
Requests = new List <Request>()
};
if (File.Exists("credentials.json"))
{
UserCredential credential;
try
{
using (var stream = new FileStream("credentials.json", FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
string credPath = "token.json";
credential = GoogleWebAuthorizationBroker.AuthorizeAsync(
GoogleClientSecrets.Load(stream).Secrets,
Scopes,
"user",
CancellationToken.None,
new FileDataStore(credPath, true)).Result;
Console.WriteLine("Credential file saved to: " + credPath);
}
// Create Google Sheets API service.
var service = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
//Get spreadsheets
var spreadsheets = service.Spreadsheets.Get(spreadsheetId).Execute();
//Clear existing Spreadsheets
{
var tmp = spreadsheets.Sheets.FirstOrDefault(x => x.Properties.Title == "Temp");
if (tmp == null)
{
var AddSheetTmp = new AddSheetRequest
{
Properties = new SheetProperties()
};
AddSheetTmp.Properties.Title = "Temp";
AddSheetTmp.Properties.SheetId = 999;
batchRequests.Requests.Add(new Request()
{
AddSheet = AddSheetTmp
});
}
}
foreach (var sheet in spreadsheets.Sheets)
{
if (sheet.Properties.Title == "Autogenerated")
{
batchRequests.Requests.Add(new Request()
{
DeleteSheet = new DeleteSheetRequest()
{
ETag = sheet.ETag, SheetId = sheet.Properties.SheetId
}
});
}
}
#pragma warning disable IDE0017 // No it can't be simplified, because then VisualStudio thinks it should look in Project Properties
var properties = new SheetProperties();
#pragma warning restore IDE0017
properties.Title = "Autogenerated";
properties.SheetId = 0;
var addSheetRequest = new AddSheetRequest
{
Properties = properties
};
batchRequests.Requests.Add(new Request()
{
AddSheet = addSheetRequest
});
var appendData = new AppendCellsRequest
{
SheetId = addSheetRequest.Properties.SheetId,
Fields = "*"
};
var rowList = appendData.Rows = new List <RowData>();
rowList.Add(new RowData()
{
Values = new List <CellData>()
});
foreach (var sound in soundsEntries)
{
var rowData = new RowData
{
Values = new List <CellData>()
};
rowData.Values.Add(new CellData
{
UserEnteredValue = new ExtendedValue()
{
StringValue = (soundRedemptionLogic == Structs.SoundRedemptionLogic.ChannelPoints ? "" : prefixCharacter.ToString()) + sound.GetCommand()
},
UserEnteredFormat = GetCellStyle(sound.GetRequirement())
});
rowData.Values.Add(new CellData
{
UserEnteredValue = new ExtendedValue()
{
StringValue = sound.GetAllFiles().Count() > 1 ? "multiple" : Path.GetFileName(sound.GetAllFiles().First())
},
UserEnteredFormat = GetCellStyle(sound.GetRequirement()),
});
rowData.Values.Add(new CellData
{
UserEnteredValue = new ExtendedValue()
{
StringValue = sound.GetDescription()
},
UserEnteredFormat = GetCellStyle(sound.GetRequirement())
});
rowData.Values.Add(new CellData
{
UserEnteredValue = new ExtendedValue()
{
NumberValue = sound.GetDateAdded().ToOADate()
},
UserEnteredFormat = GetCellStyle(sound.GetRequirement(), ValueTypeFormatting.DateTime)
});
rowList.Add(rowData);
}
rowList[0].Values.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = string.Format((soundRedemptionLogic != Structs.SoundRedemptionLogic.Legacy ? "Command (total: {1})" : "({0}) Command (total: {1})"), prefixCharacter, soundsEntries.Count)
}, UserEnteredFormat = GetCellStyle(Structs.TwitchRightsEnum.Disabled, ValueTypeFormatting.Header)
});
rowList[0].Values.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "File"
}, UserEnteredFormat = GetCellStyle(Structs.TwitchRightsEnum.Disabled, ValueTypeFormatting.Header)
});
rowList[0].Values.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Description"
}, UserEnteredFormat = GetCellStyle(Structs.TwitchRightsEnum.Disabled, ValueTypeFormatting.Header)
});
rowList[0].Values.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Updated (UTC)"
}, UserEnteredFormat = GetCellStyle(Structs.TwitchRightsEnum.Disabled, ValueTypeFormatting.Header)
});
rowList[0].Values.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = ""
}
});
rowList[0].Values.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Colors:"
}, UserEnteredFormat = GetCellStyle(Structs.TwitchRightsEnum.Disabled, ValueTypeFormatting.Header)
});
rowList[1].Values.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = ""
}
});
rowList[1].Values.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Admin only"
}, UserEnteredFormat = GetCellStyle(Structs.TwitchRightsEnum.Admin)
});
rowList[2].Values.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = ""
}
});
rowList[2].Values.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Moderators"
}, UserEnteredFormat = GetCellStyle(Structs.TwitchRightsEnum.Mod)
});
rowList[3].Values.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = ""
}
});
rowList[3].Values.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Subscribers + Trusted"
}, UserEnteredFormat = GetCellStyle(Structs.TwitchRightsEnum.TrustedSub)
});
rowList[4].Values.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = ""
}
});
rowList[4].Values.Add(new CellData()
{
UserEnteredValue = new ExtendedValue()
{
StringValue = "Public"
}, UserEnteredFormat = GetCellStyle(Structs.TwitchRightsEnum.Public)
});
batchRequests.Requests.Add(new Request()
{
AppendCells = appendData
});
batchRequests.Requests.Add(new Request()
{
AutoResizeDimensions = new AutoResizeDimensionsRequest()
{
Dimensions = new DimensionRange()
{
SheetId = addSheetRequest.Properties.SheetId, Dimension = "COLUMNS"
}
}
});
batchRequests.Requests.Add(new Request()
{
DeleteSheet = new DeleteSheetRequest()
{
SheetId = 999
}
});
var request = service.Spreadsheets.BatchUpdate(batchRequests, spreadsheetId);
var response = request.Execute();
batchRequests.Requests.Clear();
WasSuccess = true;
}
catch (Exception e)
{
MessageBox.Show(e.ToString(), "Exception error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
else
{
MessageBox.Show("File doesn't exists");
}
}
