Feature request

[ancich]

I have looked everywhere but I cannot seem to find any code (or at least clear code in C#) that shows how to add a blank row (for a new record) between EXISTING records (i.e. "insert row").

[SteveWinward]

@ancich that’s a really interesting question. I had not thought about supporting that operation for the initial purposes I made this library.

I found docs on Googles Sheets API to support this. I’ll have to look into how to call this via C# and then how to update this library to support it.

https://developers.google.com/sheets/api/samples/rowcolumn#insert_an_empty_row_or_column

I will try to look into this in a few days.

[ancich]

That would be FANTASTIC. I have seen that exact page that you pointed me to, but I have zero idea how to implement that in my c# program. I currently have stuff working, like adding a row at the end, reading values and changing values, but it is the INSERT that I really, really need. I'd be happy even with brute force "no helper" code at this point. As soon as anything starts including things like POST and REST, my eyes glaze just glaze over. I am currently using "using Google.Apis.Sheets.v4" but it's documentation is cryptic. It sounds like I have to do a "batch update" POST https://sheets.googleapis.com/v4/spreadsheets/spreadsheetId:batchUpdate and somehow magically give it these field and/or data { "requests": [ { "moveDimension": { "source": { "sheetId": sheetId, "dimension": "ROWS", "startIndex": 4, "endIndex": 10 }, "destinationIndex": 19 } }, ], }

... so confused.

PS: If you need other features to add: return number of rows (or columns) in the sheet, get the name of the tabs, any easy way to select a specific sheet if the worksheet has several tabs, ability to export sheet to csv, ability to import sheet FROM csv, and many, many more

[SteveWinward]

New Insert Blank Row Method

I just added a new method to the SheetHelper class to allow you to insert a blank row at any given row number. This example below will insert a new blank row at row id 2.

// Create a SheetHelper class for the default first tab
var sheetHelper = new SheetHelper(
    settings.GoogleSpreadsheetId,
    settings.GoogleServiceAccountName,
    "");

// Initialize the SheetHelper
sheetHelper.Init(settings.JsonCredential);

// insert a new blank row at row 2
helper.InsertBlankRow(2);

Export Methods

With regards to exporting data from Google Sheets, I have two helper methods for that which are documented below,

https://github.com/SteveWinward/GoogleSheetsWrapper#export-google-sheet-to-csv

https://github.com/SteveWinward/GoogleSheetsWrapper#export-google-sheet-to-excel-file

Tab Operations

To get the name of the tabs in a Google Spreadsheet, you can do this,

// Create a SheetHelper class for the default first tab
var sheetHelper = new SheetHelper(
    settings.GoogleSpreadsheetId,
    settings.GoogleServiceAccountName,
    "");

// Initialize the SheetHelper
sheetHelper.Init(settings.JsonCredential);

// Get all tab names in the spreadsheet
var tabs = sheetHelper.GetAllTabNames();

// loop over all tabs
foreach(var tab in tabs)
{
    // update the tab name with the SheetHelper class
    sheetHelper.UpdateTabName(tab);

    // Create the range to be the 1st row to all data rows
    // this also includes the 1st to 8th columns in the range
    var range = new SheetRange(tab, 1, 1, 8);
    var rows = sheetHelper.GetRows(range);
}

Hope this helps!

[ancich]

The changes you made are AWESOME.

But I feel a bit dumb . I am not sure how I instantiate or reference or call your GoogleSheetsWrapper itself.

I tried using GoogleSheetsWrapper;

Just straight up var sheetHelper = new SheetHelper("1f6MrL1G6tdnkxUEK1q-5c2I2Sv79AH5Jz9pOzocmJlI", service, ""); but it doesn't recognize what SheetHelper is.

Warmest regards, Johnny Ancich www.PastExpiry.comhttp://www.PastExpiry.com https://project2013b.blogspot.comhttps://project2013b.blogspot.com/2021/10/welcome.html

---

From: Steve Winward @.> Sent: October 16, 2021 5:42 PM To: SteveWinward/GoogleSheetsWrapper @.> Cc: Past Expiry @.>; Mention @.> Subject: Re: [SteveWinward/GoogleSheetsWrapper] Feature request (#3)

@ancichhttps://github.com/ancich that’s a really interesting question. I had not thought about supporting that operation for the initial purposes I made this library.

I found docs on Googles Sheets API to support this. I’ll have to look into how to call this via C# and then how to update this library to support it.

https://developers.google.com/sheets/api/samples/rowcolumn#insert_an_empty_row_or_column

I will try to look into this in a few days.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/SteveWinward/GoogleSheetsWrapper/issues/3#issuecomment-945011202, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGPDS3GPPQP3AFMZCO6NO3TUHHWSXANCNFSM5GEBOWOA. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[SteveWinward]

No worries. This GoogleSheetsWrapper library I created is packaged and published to nuget.org. So you need to make sure that you have imported the published Nuget package for this library. You can go here to find the latest version that includes the new insert blank row method.

https://www.nuget.org/packages/GoogleSheetsWrapper/

Optionally, if you just want to see the C# code that inserts a new blank row, you can see it here in the source code for the library,

https://github.com/SteveWinward/GoogleSheetsWrapper/blob/main/src/GoogleSheetsWrapper/BaseSheetHelper.cs#L147

Hope this helps.

[ancich]

OMG... This reminds me of online dating : I was so hopeful, but disappointed.

It compiled, and ran up to the line sheetHelper.Init("core-329217-b87fcb9f5b36.json"); where it gives some "unexpected character error"

System.InvalidOperationException

HResult=0x80131509

Message=Error deserializing JSON credential data.

Source=Google.Apis.Auth

StackTrace:

at Google.Apis.Auth.OAuth2.DefaultCredentialProvider.CreateDefaultCredentialFromJson(String json)

at Google.Apis.Auth.OAuth2.GoogleCredential.FromJson(String json)

at GoogleSheetsWrapper.BaseSheetHelper.Init(String jsonCredentials)

at _2013B_Updater007.Form1..ctor() in C:\Users\Ancich\Documents_blog_2013b!Project2013B_Updater\2013B_Updater007\2013B_Updater007\Form1.cs:line 87

at _2013B_Updater007.Program.Main() in C:\Users\Ancich\Documents_blog_2013b!Project2013B_Updater\2013B_Updater007\2013B_Updater007\Program.cs:line 20

This exception was originally thrown at this call stack:

[External Code]

Inner Exception 1:

JsonReaderException: Unexpected character encountered while parsing value: c. Path '', line 0, position 0.

MY CODE... public partial class Form1 : Form {

static readonly string[] Scopes = { SheetsService.Scope.Spreadsheets };
static readonly string ApplicationName = "2013core";
static readonly string SpreadsheetId = "1f6MrL1G6tdnkxUEK1q-5c2I2Sv79AH5Jz9pOzocmJlI";
static readonly string sheet = "Sheet1";
static SheetsService service;

    public Form1()
    {
        InitializeComponent();

        GoogleCredential credential;
        using (var stream = new FileStream("core-329217-b87fcb9f5b36.json", FileMode.Open, FileAccess.Read))
        {
            credential = GoogleCredential.FromStream(stream)
                .CreateScoped(Scopes);
        }

        // Create Google Sheets API service.
        service = new SheetsService(new BaseClientService.Initializer()
        {
            HttpClientInitializer = credential,
            ApplicationName = ApplicationName,
        });

        ReadEntries();   // reads and displays google sheet's data in Visual Studio's Output window.

        // ================== GoogleSheetsWrapper
        // sheetID,  serviceaccountEMAIL , Sheetname

        var sheetHelper = new SheetHelper("1f6MrL1G6tdnkxUEK1q-5c2I2Sv79AH5Jz9pOzocmJlI", ***@***.***", "Sheet1");

        sheetHelper.Init("core-329217-b87fcb9f5b36.json");

        // insert a new blank row at row 2
        sheetHelper.InsertBlankRow(2);

☹

Warmest regards, Johnny Ancich www.PastExpiry.comhttp://www.PastExpiry.com https://project2013b.blogspot.comhttps://project2013b.blogspot.com/2021/10/welcome.html

---

From: Steve Winward @.> Sent: October 16, 2021 7:20 PM To: SteveWinward/GoogleSheetsWrapper @.> Cc: Past Expiry @.>; Mention @.> Subject: Re: [SteveWinward/GoogleSheetsWrapper] Feature request (#3)

New Insert Blank Row Method

I just added a new method to the SheetHelper class to allow you to insert a blank row at any given row number. This example below will insert a new blank row at row id 2.

// Create a SheetHelper class for the default first tab var sheetHelper = new SheetHelper( settings.GoogleSpreadsheetId, settings.GoogleServiceAccountName, "");

// Initialize the SheetHelper sheetHelper.Init(settings.JsonCredential);

// insert a new blank row at row 2 helper.InsertBlankRow(2);

Export Methods

With regards to exporting data from Google Sheets, I have two helper methods for that which are documented below,

https://github.com/SteveWinward/GoogleSheetsWrapper#export-google-sheet-to-csv

https://github.com/SteveWinward/GoogleSheetsWrapper#export-google-sheet-to-excel-file

Tab Operations

To get the name of the tabs in a Google Spreadsheet, you can do this,

// Create a SheetHelper class for the default first tab var sheetHelper = new SheetHelper( settings.GoogleSpreadsheetId, settings.GoogleServiceAccountName, "");

// Initialize the SheetHelper sheetHelper.Init(settings.JsonCredential);

// Get all tab names in the spreadsheet var tabs = sheetHelper.GetAllTabNames();

// loop over all tabs foreach(var tab in tabs) { // update the tab name with the SheetHelper class sheetHelper.UpdateTabName(tab);

// Create the range to be the 1st row to all data rows
// this also includes the 1st to 8th columns in the range
var range = new SheetRange(tab, 1, 1, 8);
var rows = sheetHelper.GetRows(range);

}

Hope this helps!

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/SteveWinward/GoogleSheetsWrapper/issues/3#issuecomment-945020218, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGPDS3F2IPA4BOHKQGCVHSTUHICCNANCNFSM5GEBOWOA. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[SteveWinward]

You need to pass the actual json text of the file you have downloaded. Right now you are passing the name of the json file instead.

[ancich]

Oh god... another rabbit hole. I think my brain just melted How? Literally open it as if it were a text file or do I have to delve into deserializing mumbo jumbo and the rabbit hole that will become.

Warmest regards, Johnny Ancich www.PastExpiry.comhttp://www.PastExpiry.com https://project2013b.blogspot.comhttps://project2013b.blogspot.com/2021/10/welcome.html

---

From: Steve Winward @.> Sent: October 16, 2021 11:10 PM To: SteveWinward/GoogleSheetsWrapper @.> Cc: Past Expiry @.>; Mention @.> Subject: Re: [SteveWinward/GoogleSheetsWrapper] Feature request (#3)

You need to pass the actual json text of the file you have downloaded. Right now you are passing the name of the json file instead.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/SteveWinward/GoogleSheetsWrapper/issues/3#issuecomment-945041210, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGPDS3D5GBQMGQ636GOUHRLUHI5BHANCNFSM5GEBOWOA. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[ancich]

OMG! it worked!

Warmest regards, Johnny Ancich www.PastExpiry.comhttp://www.PastExpiry.com https://project2013b.blogspot.comhttps://project2013b.blogspot.com/2021/10/welcome.html

---

From: Steve Winward @.> Sent: October 16, 2021 11:10 PM To: SteveWinward/GoogleSheetsWrapper @.> Cc: Past Expiry @.>; Mention @.> Subject: Re: [SteveWinward/GoogleSheetsWrapper] Feature request (#3)

You need to pass the actual json text of the file you have downloaded. Right now you are passing the name of the json file instead.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/SteveWinward/GoogleSheetsWrapper/issues/3#issuecomment-945041210, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGPDS3D5GBQMGQ636GOUHRLUHI5BHANCNFSM5GEBOWOA. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[SteveWinward]

Excellent. Glad it’s working for you.

[ancich]

Hi Steve,

Here's a dumb question.... you example show how to get and update the first record but what is the  syntax for getting  say the 7th record, or the 234th record?

// Get all rows from the Google Sheet var allRecords = repository.GetAllRecords();

// Get the first record var firstRecord = allRecords.First();

// Update the PriceAmount field and save it back to Google Sheets firstRecord.PriceAmount = 99.99; repository.SaveField(firstRecord, (r) => r.PriceAmount);

Warmest regards, Johnny Ancich www.PastExpiry.comhttp://www.PastExpiry.com https://project2013b.blogspot.comhttps://project2013b.blogspot.com/2021/10/welcome.html

---

From: Steve Winward @.> Sent: October 17, 2021 6:46 AM To: SteveWinward/GoogleSheetsWrapper @.> Cc: Past Expiry @.>; Mention @.> Subject: Re: [SteveWinward/GoogleSheetsWrapper] Feature request (#3)

Excellent. Glad it’s working for you.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/SteveWinward/GoogleSheetsWrapper/issues/3#issuecomment-945090344, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGPDS3FRAM25I7W2X7SG5PLUHKSPPANCNFSM5GEBOWOA. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[SteveWinward]

The First() method comes from the C# framework called LINQ. The return type for GetAllRecords() is a generic list. You can do a foreach loop to iterate through the whole collection or you can use the index properties to get specific items. For example allRecords[6] would give you the 7th item from the collection because it’s a zero based index.

Hope this helps.

[ancich]

Thanks! Your help and additions to your code are above and beyond.

IF you get bored one day, a cool feature would be to have the ability to take a CSV and create a GoogleSheet from it (and/or append it to an existing GoogleSheet).

Warmest regards, Johnny Ancich www.PastExpiry.comhttp://www.PastExpiry.com https://project2013b.blogspot.comhttps://project2013b.blogspot.com/2021/10/welcome.html

---

From: Steve Winward @.> Sent: October 24, 2021 2:49 PM To: SteveWinward/GoogleSheetsWrapper @.> Cc: Past Expiry @.>; Mention @.> Subject: Re: [SteveWinward/GoogleSheetsWrapper] Feature request (#3)

The First() method comes from the C# framework called LINQ. The return type for GetAllRecords() is a generic list. You can do a foreach loop to iterate through the whole collection or you can use the index properties to get specific items. For example allRecords[6] would give you the 7th item from the collection because it’s a zero based index.

Hope this helps.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/SteveWinward/GoogleSheetsWrapper/issues/3#issuecomment-950374791, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGPDS3FRA4UGO3CJFB7FV5TUIRIJZANCNFSM5GEBOWOA. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[SteveWinward]

@ancich , that is a great suggestion. I will see if there is a way I can add that to the library.

[ancich]

Yeah, I just stumbled across something related to my suggestion just now; it looks like it would have to include the GooogleDrive Api, so I am not sure if that might muddy the waters on your end.

Only other suggestion I would have is that one line that gave me a bit of grief : sheetHelper.Init(JSONtext); which required the text of the json file and not the filename. There was some other apis I have used where a line like this just needed the filename (and path). Maybe so things remain backwards compatible make it something like sheetHelper.InitJSONfilename(JSONtext);

Warmest regards, Johnny Ancich www.PastExpiry.comhttp://www.PastExpiry.com https://project2013b.blogspot.comhttps://project2013b.blogspot.com/2021/10/welcome.html

---

From: Steve Winward @.> Sent: October 26, 2021 2:38 PM To: SteveWinward/GoogleSheetsWrapper @.> Cc: Past Expiry @.>; Mention @.> Subject: Re: [SteveWinward/GoogleSheetsWrapper] Feature request (#3)

@ancichhttps://github.com/ancich , that is a great suggestion. I will see if there is a way I can add that to the library.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/SteveWinward/GoogleSheetsWrapper/issues/3#issuecomment-952208092, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGPDS3EEXFU42T3L3QQN3NDUI3YSZANCNFSM5GEBOWOA. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[SteveWinward]

I pushed an update with a new class called SheetAppender.

You can optionally include the header row from the CSV. This will append all rows to a blank spreadsheet tab or it will append to an existing sheet with data. If you already have data I would skip including the header row.

Updated docs to use the new class are below!

https://github.com/SteveWinward/GoogleSheetsWrapper/blob/main/README.md#import-a-csv-to-google-sheets

[ancich]

Hi Steve,

The exporter (google sheet to csv) is working great.

I tried the 'appender'  (csv to googlesheet) and I get a system null reference exception.  The csv file that I chose to append to my googlesheet somehow gets overwritten and becomes a 0 byte file.  Is my interpretation than the appender should take my existing csv file and append it to the Google Sheet who's ID and sheetname I provide, wrong?

Here is my code (ideally my data in queue.csv should end up in the google sheet with the ID of "1sy3QGNlMB1HpeleKQIw3vCeVmHdUWO2TvgHIxyzjG3o")

    private void QUEUE_CSV_TO_QUEUE_Gsheet()
    {
        var spreadsheetsID = "1sy3QGNlMB1HpeleKQIw3vCeVmHdUWO2TvgHIxyzjG3o";
        var spreadsheet_TABname = qsheet1;     // name of the TAB in the spreadsheet

        string outputfilePATH = @"C:\Users\Ancich\Documents\_blog_2013b\!Project2013B_Updater\2013B_Updater007\DATA\";
        string outputCSVfile = outputfilePATH + "queue.csv";

       // =========================
        var appender = new SheetAppender(
        spreadsheetsID,
        ***@***.***",
        spreadsheet_TABname);

        string JSONtext = System.IO.File.ReadAllText(GsheetAPIcred);    // Read the file as one string.
        appender.Init(JSONtext);  // Steve's example:   appender.Init(settings.JsonCredential);

        using (var stream = new FileStream(outputCSVfile, FileMode.Create))
        {
            // Append the csv file to Google sheets, include the header row
            // and wait 1000 milliseconds between batch updates
            // Google Sheets API throttles requests per minute so you may need to play
            // with this setting.
            appender.AppendCsv(
                stream, // The CSV FileStrem
                true, // true indicating to include the header row
                3000); // 1000 milliseconds to wait every 100 rows that are batch sent to the Google Sheets API
        }
    }

I like the idea of the option of including or excluding the header line. Have you thought of adding a 'switch' that would just let if overwrite whatever data is currently in the sheet?

Warmest regards, Johnny Ancich www.PastExpiry.comhttp://www.PastExpiry.com https://project2013b.blogspot.comhttps://project2013b.blogspot.com/2021/10/welcome.html

---

From: Steve Winward @.> Sent: October 26, 2021 7:44 PM To: SteveWinward/GoogleSheetsWrapper @.> Cc: Past Expiry @.>; Mention @.> Subject: Re: [SteveWinward/GoogleSheetsWrapper] Feature request (#3)

I pushed an update with a new class called SheetAppender.

You can optionally include the header row from the CSV. This will append all rows to a blank spreadsheet tab or it will append to an existing sheet with data. If you already have data I would skip including the header row.

Updated docs to use the new class are below!

https://github.com/SteveWinward/GoogleSheetsWrapper/blob/main/README.md#import-a-csv-to-google-sheets

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/SteveWinward/GoogleSheetsWrapper/issues/3#issuecomment-952414848, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGPDS3D2UZDGI73EPRRBOFTUI44NFANCNFSM5GEBOWOA. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[SteveWinward]

Sorry about that. I copied the code sample and forgot to update one part. When you create the FileStream, use FileMode.Open, not FileMode.Create.

using (var stream = new FileStream(filepath, FileMode.Open))

FileMode.Create will result in creating /overwriting the existing file and leaving you with a 0 byte file that used to have your csv file data.

I'm sorry about that typo and I updated my sample in the main readme page too.

[ancich]

No need to apologize at all. You fixed it very quickly and have been super helpful and responsive to my questions and issues. I am used to leaving questions and requests on Github where the my question is either ignored or not answered for months (if not years).

The wrapper is now doing 99% of what I need 🙂 Only wishlist item is ability to overwrite the google Sheet's content (if one choose) when exporting the csv to the google Sheet. Not sure if it would be useful, but I tried to use your append feature but specified a Sheet Tab name that did not exist... it gave an error as I expected, but might be a feature somebody out there might want.

Warmest regards, Johnny Ancich www.PastExpiry.comhttp://www.PastExpiry.com https://project2013b.blogspot.comhttps://project2013b.blogspot.com/2021/10/welcome.html

---

From: Steve Winward @.> Sent: October 27, 2021 11:41 PM To: SteveWinward/GoogleSheetsWrapper @.> Cc: Past Expiry @.>; Mention @.> Subject: Re: [SteveWinward/GoogleSheetsWrapper] Feature request (#3)

Sorry about that. I copied the code sample and forgot to update one part. When you create the FileStream, use FileMode.Open, not FileMode.Create.

using (var stream = new FileStream(filepath, FileMode.Open))

FileMode.Create will result in creating /overwriting the existing file and leaving you with a 0 byte file that used to have your csv file data.

I'm sorry about that typo and I updated my sample in the main readme page too.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/SteveWinward/GoogleSheetsWrapper/issues/3#issuecomment-953475623, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGPDS3EIFEYAIXCE5EYTHRTUJDA7RANCNFSM5GEBOWOA. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[SteveWinward]

Got it. So if there was a way to create a new tab and then run the appender would that be what you need?

[ancich]

yes, that would be cool and nice to have at some point, but just brute-force overwriting an existing tab's content (csv to google Sheet) would be more useful for my current use case.

Basically I have a google sheet that I use as a queue list that shows the status of each item.... rather than loop through the who thing and chew up API calls, I just dump the whole sheet to csv, read it into a datatable, do whatever mods I need to do to my heart's content, then dump it back to the google sheet. Append (which is working great) does the same thing for me, but the overwriting feature would just save me a step of manually removing the previous data.

Sheet I am working with if curious at all... https://docs.google.com/spreadsheets/d/1f6MrL1G6tdnkxUEK1q-5c2I2Sv79AH5Jz9pOzocmJlI/edit#gid=0 [https://lh3.googleusercontent.com/d8SzsHgSb4ElDpFwrV5zbLzwdiQ9ftDn1wlDpBVh0Yv_PxOhQ_4HRPCmNBssGJgl3jRew21pjF-bhA=w1200-h630-p]https://docs.google.com/spreadsheets/d/1f6MrL1G6tdnkxUEK1q-5c2I2Sv79AH5Jz9pOzocmJlI/edit#gid=0 Queuehttps://docs.google.com/spreadsheets/d/1f6MrL1G6tdnkxUEK1q-5c2I2Sv79AH5Jz9pOzocmJlI/edit#gid=0 Sheet1 1,PROJECT 2013B SERIAL NUMBERS QUEUED FOR ADDITION TO THE MAIN DATABASE.,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20 QUEUE STATUS,STATUS MESSAGE,Record No.,Block / Run,Sheet Number,Plate Position,Face Plate Text,Back Plate Number,Block/Run Serial Number,Block/Run Serial Number To,DC / ... docs.google.com

Warmest regards, Johnny Ancich www.PastExpiry.comhttp://www.PastExpiry.com https://project2013b.blogspot.comhttps://project2013b.blogspot.com/2021/10/welcome.html

---

From: Steve Winward @.> Sent: October 28, 2021 7:05 AM To: SteveWinward/GoogleSheetsWrapper @.> Cc: Past Expiry @.>; Mention @.> Subject: Re: [SteveWinward/GoogleSheetsWrapper] Feature request (#3)

Got it. So if there was a way to create a new tab and then run the appender would that be what you need?

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/SteveWinward/GoogleSheetsWrapper/issues/3#issuecomment-953740150, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGPDS3DIQR5O5RPOYWVCUSTUJEU55ANCNFSM5GEBOWOA. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[SteveWinward]

Got it. I'm not sure the best way to clear all of the rows in an existing tab, other than to delete the tab and create a new one. Would that work for you? If so, we can add functions to the library to delete a tab and then create a tab with a given name. Then you could run the append function and repopulate the new tab with the same existing name.

[ancich]

Hmmmmm.... maybe... I just worry that this might somehow alter the URL of this specific sheet and tab, as the address is currently being shared publicly. I guess the gSheet ID probably wouldn't change but not sure about the other gobblygook that is part of the URL. I am a paranoid fellow.

Maybe a better alternative would be something like a "delete and append" where you delete the first 'x' existing rows, THEN append...... I guess these could be seperate commands (loop and delete rows ... row 0 to X, with or without header row), then the existing append.

Warmest regards, Johnny Ancich www.PastExpiry.comhttp://www.PastExpiry.com https://project2013b.blogspot.comhttps://project2013b.blogspot.com/2021/10/welcome.html

---

From: Steve Winward @.> Sent: October 28, 2021 10:09 AM To: SteveWinward/GoogleSheetsWrapper @.> Cc: Past Expiry @.>; Mention @.> Subject: Re: [SteveWinward/GoogleSheetsWrapper] Feature request (#3)

Got it. I'm not sure the best way to clear all of the rows in an existing tab, other than to delete the tab and create a new one. Would that work for you? If so, we can add functions to the library to delete a tab and then create a tab with a given name. Then you could run the append function and repopulate the new tab with the same existing name.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/SteveWinward/GoogleSheetsWrapper/issues/3#issuecomment-953884511, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGPDS3HTJWF656HITYUXKGDUJFKTPANCNFSM5GEBOWOA. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[SteveWinward]

Understood.

There is a way to delete a set of rows (say from the first row to the 1,000th row). If we had a way to say clear the first 2,000 rows where you can specify how many rows to delete, would that work? You could probably just set the number to be really high and it would effectively wipe the tab's data. Then you could run the append.

Let me know if you think this would work.

[SteveWinward]

I added a method to get all the rows of the existing tab to then give you the total row size. You can then delete all of those rows before calling the appender. I added sample code here,

https://github.com/SteveWinward/GoogleSheetsWrapper#import-a-csv-to-google-sheets-and-purge-existing-rows

[ancich]

Yeah, that would be great.

---

From: Steve Winward @.> Sent: October 28, 2021 10:44 AM To: SteveWinward/GoogleSheetsWrapper @.> Cc: Past Expiry @.>; Mention @.> Subject: Re: [SteveWinward/GoogleSheetsWrapper] Feature request (#3)

Understood.

There is a way to delete a set of rows (say from the first row to the 1,000th row). If we had a way to say clear the first 2,000 rows where you can specify how many rows to delete, would that work? You could probably just set the number to be really high and it would effectively wipe the tab's data. Then you could run the append.

Let me know if you think this would work.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/SteveWinward/GoogleSheetsWrapper/issues/3#issuecomment-953916879, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGPDS3FKYS6S2TDLQV7ENDLUJFOUFANCNFSM5GEBOWOA. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[SteveWinward]

Check out this updated sample with the newest nuget version,

https://github.com/SteveWinward/GoogleSheetsWrapper#import-a-csv-to-google-sheets-and-purge-existing-rows

[ancich]

It's working great! Thanks!

Warmest regards, Johnny Ancich www.PastExpiry.comhttp://www.PastExpiry.com https://project2013b.blogspot.comhttps://project2013b.blogspot.com/2021/10/welcome.html

---

From: Steve Winward @.> Sent: October 28, 2021 3:49 PM To: SteveWinward/GoogleSheetsWrapper @.> Cc: Past Expiry @.>; Mention @.> Subject: Re: [SteveWinward/GoogleSheetsWrapper] Feature request (#3)

Check out this updated sample with the newest nuget version,

https://github.com/SteveWinward/GoogleSheetsWrapper#import-a-csv-to-google-sheets-and-purge-existing-rows

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/SteveWinward/GoogleSheetsWrapper/issues/3#issuecomment-954150513, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGPDS3GMLCPT4AXLLYIVNYDUJGSOFANCNFSM5GEBOWOA. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[SteveWinward]

Awesome.

[ancich]

Hi,

I might have found a bug when I was mucking around trying some different things.

in the Append a CSV to Google Sheets feature... if I change the headerline indicator to false, I get an error.

appender.AppendCsv(
    stream, // The CSV FileStrem
    false, // true indicating to include the header row
    1000);

Basically it is treating my headerless csv file as if the first row of data

was a header, so it kinda freaks out when it sees what it sees as two headers

with the same name (e.g. TBD)

Extensive error details below....

CsvHelper.ReaderException HResult=0x80131500 Message=An unexpected error occurred. IReader state: ColumnCount: 0 CurrentIndex: 4 HeaderRecord: ["TRANSFERRED!","Now in main database!","TBD","B-02-DC","TBD","F2","F16","1","B 05777576"," ","DC","2021.10.21","M>CT","Prefers to buy"," "," ","Stratford","CT","USA"," "," ","5777576","Michelle @."] IParser state: ByteCount: 0 CharCount: 378 Row: 2 RawRow: 2 Count: 25 RawRecord: TRANSFERRED!,Now in main database!,TBD,B-01-FW,TBD,H2,FWH107,86,B 00098724," ",FW,2021.10.17,GF>VA,Undecided," "," ",Richmond,VA,USA," "," @.

Source=CsvHelper StackTrace: at CsvHelper.CsvReader.d__871.MoveNext() at System.Linq.Enumerable.SelectEnumerableIterator2.ToList() at GoogleSheetsWrapper.SheetAppender.AppendCsv(Stream stream, Boolean includeHeaders, Int32 batchWaitTime) at _2013B_Updater007.Form1.QUEUE_CSV_TO_QUEUE_Gsheet() in C:\Users\Ancich\Documents_blog_2013b!Project2013B_Updater\2013B_Updater007\2013B_Updater007\Form1.cs:line 2119 at _2013B_Updater007.Form1.button14_Click(Object sender, EventArgs e) in C:\Users\Ancich\Documents_blog_2013b!Project2013B_Updater\2013B_Updater007\2013B_Updater007\Form1.cs:line 2169 at System.Windows.Forms.Control.OnClick(EventArgs e) at System.Windows.Forms.Button.OnClick(EventArgs e) at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent) at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks) at System.Windows.Forms.Control.WndProc(Message& m) at System.Windows.Forms.ButtonBase.WndProc(Message& m) at System.Windows.Forms.Button.WndProc(Message& m) at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m) at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, WM msg, IntPtr wparam, IntPtr lparam)

This exception was originally thrown at this call stack: [External Code]

Inner Exception 1: ArgumentException: An element with the same key 'TBD' already exists in the ExpandoObject. (Parameter 'key')

Warmest regards, Johnny Ancich www.PastExpiry.comhttp://www.PastExpiry.com https://project2013b.blogspot.comhttps://project2013b.blogspot.com/2021/10/welcome.html

---

From: Johnny Ancich @.> Sent: October 28, 2021 10:39 PM To: SteveWinward/GoogleSheetsWrapper @.> Subject: Re: [SteveWinward/GoogleSheetsWrapper] Feature request (#3)

It's working great! Thanks!

Warmest regards, Johnny Ancich www.PastExpiry.comhttp://www.PastExpiry.com https://project2013b.blogspot.comhttps://project2013b.blogspot.com/2021/10/welcome.html

---

From: Steve Winward @.> Sent: October 28, 2021 3:49 PM To: SteveWinward/GoogleSheetsWrapper @.> Cc: Past Expiry @.>; Mention @.> Subject: Re: [SteveWinward/GoogleSheetsWrapper] Feature request (#3)

Check out this updated sample with the newest nuget version,

https://github.com/SteveWinward/GoogleSheetsWrapper#import-a-csv-to-google-sheets-and-purge-existing-rows

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/SteveWinward/GoogleSheetsWrapper/issues/3#issuecomment-954150513, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGPDS3GMLCPT4AXLLYIVNYDUJGSOFANCNFSM5GEBOWOA. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[SteveWinward]

Got it. I think I just reproduced this and pushed a commit just now that should fix that. Look for the nuget package version 1.0.27 which will include the update.

https://github.com/SteveWinward/GoogleSheetsWrapper/commit/99e0b263e44bc94a105e29917c6378b229af84aa

[ancich]

Hi Steve,

I have been using your wrapper for several months now with no issues.  Anytime there is an issue it is always with the user entering bad data a field in my google form (which dumps to a google sheet) namely stuff like commas.   Is there a way to read the google sheet and dump it to a TAB delimitted csv file, or do I need to find a way to do that upstream on the google form itself?

Regards, Johnny

Warmest regards, Johnny Ancich www.PastExpiry.comhttp://www.PastExpiry.com https://project2013b.blogspot.comhttps://project2013b.blogspot.com/2021/10/welcome.html

---

From: Johnny Ancich @.> Sent: October 28, 2021 10:39 PM To: SteveWinward/GoogleSheetsWrapper @.> Subject: Re: [SteveWinward/GoogleSheetsWrapper] Feature request (#3)

It's working great! Thanks!

Warmest regards, Johnny Ancich www.PastExpiry.comhttp://www.PastExpiry.com https://project2013b.blogspot.comhttps://project2013b.blogspot.com/2021/10/welcome.html

---

From: Steve Winward @.> Sent: October 28, 2021 3:49 PM To: SteveWinward/GoogleSheetsWrapper @.> Cc: Past Expiry @.>; Mention @.> Subject: Re: [SteveWinward/GoogleSheetsWrapper] Feature request (#3)

Check out this updated sample with the newest nuget version,

https://github.com/SteveWinward/GoogleSheetsWrapper#import-a-csv-to-google-sheets-and-purge-existing-rows

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/SteveWinward/GoogleSheetsWrapper/issues/3#issuecomment-954150513, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGPDS3GMLCPT4AXLLYIVNYDUJGSOFANCNFSM5GEBOWOA. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.

[SteveWinward]

Hey Johnny,

If you run this code below, do you get an error when exporting to a CSV file?

var exporter = new SheetExporter(
    settings.GoogleSpreadsheetId, 
    settings.GoogleServiceAccountName, 
    settings.GoogleMainSheetName);

exporter.Init(settings.JsonCredential);

var filepath = @"C:\Output\output.csv";

using (var stream = new FileStream(filepath, FileMode.Create))
{
    // Query the range A1:G (ie 1st column, 1st row, 8th column and last row in the sheet)
    var range = new SheetRange("TAB_NAME", 1, 1, 8);
    exporter.ExportAsCsv(range, stream);
}

[ancich]

Hi Steve,

I'm not at my home computer right now, but I do not have any issues with exporting a google sheet as a csv file; I am just wondering if it could be exported but with a TAB as a delimitter and not a comma.

Warmest regards, Johnny Ancich www.PastExpiry.comhttp://www.PastExpiry.com https://project2013b.blogspot.comhttps://project2013b.blogspot.com/2021/10/welcome.html

---

From: Steve Winward @.> Sent: March 8, 2022 9:54 AM To: SteveWinward/GoogleSheetsWrapper @.> Cc: Past Expiry @.>; Mention @.> Subject: Re: [SteveWinward/GoogleSheetsWrapper] Feature request (#3)

Hey Johnny,

If you run this code below, do you get an error when exporting to a CSV file?

var exporter = new SheetExporter( settings.GoogleSpreadsheetId, settings.GoogleServiceAccountName, settings.GoogleMainSheetName);

exporter.Init(settings.JsonCredential);

var filepath = @"C:\Output\output.csv";

using (var stream = new FileStream(filepath, FileMode.Create)) { // Query the range A1:G (ie 1st column, 1st row, 8th column and last row in the sheet) var range = new SheetRange("TAB_NAME", 1, 1, 8); exporter.ExportAsCsv(range, stream); }

— Reply to this email directly, view it on GitHubhttps://github.com/SteveWinward/GoogleSheetsWrapper/issues/3#issuecomment-1061863252, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGPDS3CYMLDXUOQPSFO4DQDU65SYZANCNFSM5GEBOWOA. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub. You are receiving this because you were mentioned.Message ID: @.***>

[SteveWinward]

Understood. I made a new release of the nuget package. Download version 1.0.31 from nuget.org.

https://www.nuget.org/packages/GoogleSheetsWrapper/

Once you get the new package, you can use the following new method signature to specify a custom delimiter when you export to CSV,

var exporter = new SheetExporter(
    settings.GoogleSpreadsheetId, 
    settings.GoogleServiceAccountName, 
    settings.GoogleMainSheetName);

exporter.Init(settings.JsonCredential);

var filepath = @"C:\Output\output.csv";

using (var stream = new FileStream(filepath, FileMode.Create))
{
    // Query the range A1:G (ie 1st column, 1st row, 8th column and last row in the sheet)
    var range = new SheetRange("TAB_NAME", 1, 1, 8);
    // This tells the file to be "tab" delimited, but you can use any custom delimiter here that you would like
    exporter.ExportAsCsv(range, stream, "\t");
}

[ancich]

Hi Steve,

Cannot believe it was close to 3 years ago you gave me a lot of help with the use of your google sheets helper!  You probably don't remember,  but our back and forth resulted in you adding some more features like dumping a google sheet's data to a CSV file.

Now I need more help 🙁    I have been using your wrapper for close to 3 years and has been instrumental in making my website successful.  I want to try a new thing where I upload a csv file to a specific tab in an existing google sheet but instead of appending, I would like to be able to just over-write whatever is on the destination sheet named 'DATA".    I know how to delete rows and then append my csv data, but the problem I am running into is that I have formulas on another sheet that references various cells on my 'DATA' sheet, and the formulas end up all displaying #REF!    I am hoping that just overwriting the existing data keeps my formulas on the other sheet tab unharmed.   I suspect your batch command might work, but I am clueless when it comes to 'encapsulated helper classes'....

I hope my issue makes sense.

Warmest regards, Johnny Ancich https://project2013b.blogspot.comhttps://project2013b.blogspot.com/2021/10/welcome.html

---

From: Johnny Ancich @.> Sent: October 28, 2021 10:39 PM To: SteveWinward/GoogleSheetsWrapper @.> Subject: Re: [SteveWinward/GoogleSheetsWrapper] Feature request (#3)

It's working great! Thanks!

Warmest regards, Johnny Ancich www.PastExpiry.comhttp://www.PastExpiry.com https://project2013b.blogspot.comhttps://project2013b.blogspot.com/2021/10/welcome.html

---

From: Steve Winward @.> Sent: October 28, 2021 3:49 PM To: SteveWinward/GoogleSheetsWrapper @.> Cc: Past Expiry @.>; Mention @.> Subject: Re: [SteveWinward/GoogleSheetsWrapper] Feature request (#3)

Check out this updated sample with the newest nuget version,

https://github.com/SteveWinward/GoogleSheetsWrapper#import-a-csv-to-google-sheets-and-purge-existing-rows

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/SteveWinward/GoogleSheetsWrapper/issues/3#issuecomment-954150513, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AGPDS3GMLCPT4AXLLYIVNYDUJGSOFANCNFSM5GEBOWOA. Triage notifications on the go with GitHub Mobile for iOShttps://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Androidhttps://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub.