Setting valueInputOption to allow Google Sheet formulas in appended values

[danieltheodore]

Hi - I am trying to enter a formula into a string value on a new row but its adds a single quote to the front of the formula when viewed in the the google sheet. The solution is to set the valueInputOption to "USER_ENTERED" from default value of "RAW"

Below is code if you just used the API directly in C#. I couldn't figure out how GoogleSheetsWrapper implemented appends so I could add this property setting.

BatchUpdateValuesRequest batchRequest = new BatchUpdateValuesRequest(); batchRequest.setValueInputOption("USER_ENTERED"); batchRequest.setData(updateValueRangeList);

BatchUpdateValuesResponse updateResponse = this.mService.spreadsheets(). values().batchUpdate(spreadsheetId, batchRequest). execute();

[SteveWinward]

@danieltheodore

Check out this sample that I think lets you do what you are looking for. In the SheetAppender class you can pass the RowData object that lets you specify the value is a FormulaValue vs a StringValue. Sample code below,

// Create a new SheetHelper class
var sheetHelper = new SheetHelper(documentId, serviceAccount, "");
sheetHelper.Init(jsonCredsContent);

// Create the SheetAppender class
var appender = new SheetAppender(sheetHelper);

// Create the RowData object
var rowData = new RowData();
var cell = new CellData();
var value = new ExtendedValue()
{
    FormulaValue = "=NOW()"
};

cell.UserEnteredValue = value;
rowData.Values = new List<CellData>();
rowData.Values.Add(cell);

// Append the row
appender.AppendRow(rowData);