private void CleanRange(SheetsService service, string range)
{
Data.ClearValuesRequest requestBody = new Data.ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = service.Spreadsheets.Values.Clear(requestBody, spreadsheetId, range);
// To execute asynchronously in an async method, replace `request.Execute()` as shown:
Data.ClearValuesResponse response = request.Execute();
}
public static void Main(string[] args)
{
string x = "0";
while (x != "6")
{
Console.WriteLine("Press: \n1. Reading\n2. Appending\n3. Updating\n4. Clearing\n5. Add/Update Headers\n6. Exit");
x = Console.ReadLine();
switch (x)
{
case "1":
Data.ValueRange readResponse = ReadSpreadsheet(spreadsheetId, range, service);
if (readResponse.Values == null)
{
Console.WriteLine("Sorry! No data read from this range.");
break;
}
Console.WriteLine("Read " + readResponse.Values.Count + " rows.");
break;
case "2":
Data.AppendValuesResponse appendResponse = AppendSpreadsheet(spreadsheetId, range, service, GetDataAsObject());
Console.WriteLine("Appended! Updated " + appendResponse.Updates.UpdatedCells + " cells.");
break;
case "3":
Data.UpdateValuesResponse updateResponse = UpdateSpreadsheet(spreadsheetId, range, service, GetDataAsObject());
Console.WriteLine("Updated " + updateResponse.UpdatedRows + " rows and " + updateResponse.UpdatedColumns + " columns.");
break;
case "4":
Data.ClearValuesResponse clearResponse = Clearspreadsheet(spreadsheetId, range, service);
Console.WriteLine("Cleared range: " + clearResponse.ClearedRange);
break;
case "5":
UpdateHeadersInSpreadsheet(spreadsheetId, service, GetHeaders());
Console.WriteLine("Updated the headers!");
break;
default:
Console.WriteLine("Please enter a valid key.");
break;
}
;
}
}
public static Data.ClearValuesResponse Clearspreadsheet(string spreadsheetId, string range, SheetsService service)
{
Console.WriteLine("Do you want to give your own range? 1. Yes\t Any other key. No");
string rangeChoice = Console.ReadLine();
if (rangeChoice == "1")
{
range = GetRangeFromUser();
}
//Create a clear request
SpreadsheetsResource.ValuesResource.ClearRequest request = service.Spreadsheets.Values.Clear(null, spreadsheetId, range);
Console.WriteLine("Clearing range " + range + " from spreadsheet...");
//Execute the request
Data.ClearValuesResponse response = request.Execute();
Headers.Clear();
return(response);
}
public Form1()
{
InitializeComponent();
GoogleCredential credential;
using (var stream = new FileStream("My First Project-2dfed0050064.json", FileMode.Open, FileAccess.Read))
{
credential = GoogleCredential.FromStream(stream).CreateScoped(Scopes);
}
service = new SheetsService(new Google.Apis.Services.BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = ApplicationName,
});
Google.Apis.Sheets.v4.Data.ClearValuesRequest requestBody = new Google.Apis.Sheets.v4.Data.ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = service.Spreadsheets.Values.Clear(requestBody, SpreadsheetId, "Main Table!A1:Z100");
Google.Apis.Sheets.v4.Data.ClearValuesResponse response = request.Execute();
Google.Apis.Sheets.v4.Data.ClearValuesRequest requestBody2 = new Google.Apis.Sheets.v4.Data.ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request2 = service.Spreadsheets.Values.Clear(requestBody, SpreadsheetId, "Data Table!A1:Z100");
Google.Apis.Sheets.v4.Data.ClearValuesResponse response2 = request2.Execute();
}
public object deleteData(string spreadsheetId, string tenBang, string phamViCell)
{
/*Xóa dữ liệu trong bảng
* Đầu vào cần có ID bảng, Tên sheet vd: sheet 1, phạm vi cần xóa*/
UserCredential credential = GetCredential(ScopesWrite);
var sheetsService = new SheetsService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = "VolunteerDTU"
});
try
{
string range = "'" + tenBang + "'!" + phamViCell;;
Data.ClearValuesRequest requestBody = new Data.ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = sheetsService.Spreadsheets.Values.Clear(requestBody, spreadsheetId, range);
Data.ClearValuesResponse response = request.Execute();
return(request);
}
catch (Exception e)
{
return(e);
}
}
public async Task ClearArea(string range, string spreadsheetId)
{
Data.ClearValuesRequest requestBody = new Data.ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest request = _service.Spreadsheets.Values.Clear(requestBody, spreadsheetId, range);
Data.ClearValuesResponse response = await request.ExecuteAsync();
}
static void Main(string[] args)
{
Console.WriteLine("Welcome to Sheets2CSV");
if (File.Exists("Settings.txt"))
{
Console.WriteLine("There Are Settings!!");
StreamReader infile = new StreamReader("Settings.txt");
configured = infile.ReadLine();
outputdir = infile.ReadLine();
spreadsheetID = infile.ReadLine();
headernum = infile.ReadLine();
entity = infile.ReadLine();
columns = infile.ReadLine();
infile.Close();
}
else
{
Console.WriteLine("Program has not been configured yet! Please Run SettingsUI first to start this program.");
Environment.Exit(0);
}
Console.WriteLine("Moving On...");
#region Configuration Level
string path = outputdir;
//string value = outputdir;
path = path + "\\";
#endregion
#region Preparation Level
#region Sheets API
UserCredential credential;
using (var stream = new FileStream("client_secret.json", FileMode.Open, FileAccess.Read))
{
string credPath = System.Environment.GetFolderPath(
System.Environment.SpecialFolder.Personal);
credPath = Path.Combine(credPath, ".credentials/sheets.googleapis.com-dotnet-Sheetstocsv.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,
});
// Define request parameters.
String spreadsheetId = spreadsheetID;
char let = (char)(64 + Convert.ToInt32(headernum));
string range = "A2:" + let;
SpreadsheetsResource.ValuesResource.GetRequest request = service.Spreadsheets.Values.Get(spreadsheetId, range);
Data.ValueRange response = request.Execute();
IList <IList <Object> > values = response.Values;
#endregion
Boolean fileExist;
//Check for File
if (File.Exists(path + "Lead_Sheet.csv"))
{
fileExist = true;
}
else
{
fileExist = false;
}
//Setup File IO
StreamWriter file = new StreamWriter(path + entity + "_Sheet.csv", true);
StreamWriter injectionfile = new StreamWriter(path + entity + "_inject.csv");
//Set up Headers
string[] headers = new string[]
{
/*"+new_createdon",
* "firstname",
* "lastname",
* "address1_postalcode",
* "emailaddress1",
* "telephone2",
* "new_isprimaryphonecellphone",
* "*new_besttimetocall",
* "subject"*/
"fname",
"lname",
"org_name",
"member_role"
};
//Check for exisitng records
if (!fileExist)
{
file.Write(entity + ", " + columns + "\n");
}
injectionfile.Write(entity + ", " + columns + "\n");
#endregion
#region Execution Level
//Handle Retrieved Data
if (values != null && values.Count > 0)
{
foreach (var row in values)
{
file.Write(entity + ", ");
injectionfile.Write(entity + ", ");
for (int i = 0; i < row.Count - 1; i++)
{
if (i == row.Count - 1)
{
file.Write(row[i]);
injectionfile.Write(row[i]);
}
if (headers[i] == "address1_postalcode")
{
string r = row[i].ToString();
if (r.Length > 5)
{
file.Write(r.Substring(0, 5) + ", ");
injectionfile.Write(r.Substring(0, 5) + ", ");
}
else
{
file.Write(row[i] + ", ");
injectionfile.Write(row[i] + ", ");
}
}
else if (headers[i] == "telephone2")
{
string val = row[i].ToString();
val = val.Replace("-", string.Empty);
val = val.Replace(" ", string.Empty);
val = val.Replace("(", string.Empty);
val = val.Replace(")", string.Empty);
val = val.Replace(".", string.Empty);
if (val.Length > 10)
{
val = val.Substring(0, 10);
}
file.Write(val + ", ");
injectionfile.Write(val + ", ");
}
else if (headers[i] == "new_isprimaryphonecellphone")
{
if (row[i].ToString() == "Yes")
{
file.Write("TRUE" + ", ");
injectionfile.Write("TRUE" + ", ");
}
else
{
file.Write("FALSE" + ", ");
injectionfile.Write("FALSE" + ", ");
}
}
else if (headers[i] == "*new_besttimetocall")
{
file.Write(callTime[row[i].ToString()] + ", ");
injectionfile.Write(callTime[row[i].ToString()] + ", ");
}
else
{
file.Write(row[i] + ", ");
injectionfile.Write(row[i] + ", ");
}
}
file.Write("\n");
injectionfile.Write("\n");
}
}
string ClearRange = "A2:Z";
Data.ClearValuesRequest requestBody = new Data.ClearValuesRequest();
SpreadsheetsResource.ValuesResource.ClearRequest clearRequest = service.Spreadsheets.Values.Clear(requestBody, spreadsheetId, ClearRange);
Data.ClearValuesResponse clearResponse = clearRequest.Execute();
file.Close();
injectionfile.Close();
#endregion
}