Updating cells adds a single quote (') before the value.

[gaurangdave]

Hi,

I am trying to update cells in a for loop but after successful update I see the sheet as a sincle quote ' before each values, because of this the formatting doesn't work.

Below is the sample code of my implementation. It seems we need to set valueInputOption as USER_ENTERED but the update cell call doesn't have that option. Can someone please help look into this? Am I missing something or is there a work around?

for (let idx = startIndex; idx <= endIndex; idx++) {
		const ticker = sheet.getCellByA1(`A${idx}`).value;
		const stockExchange = sheet.getCellByA1(`B${idx}`).value;
		if (ticker && stockExchange && stockExchange == "TSE") {
			const dividendYieldCell = sheet.getCellByA1(`G${idx}`);

			const monthlyPayoutCell = sheet.getCellByA1(`H${idx}`);
			const payoutRatioCell = sheet.getCellByA1(`J${idx}`);

			const exDividendDateCell = sheet.getCellByA1(`K${idx}`);
			
			const keyData = await tmxMoney.getStockKeyData(ticker);
			console.log('keyData', keyData);
			dividendYieldCell.value = keyData.dividendYield;
			monthlyPayoutCell.value = getMonthlyDividend(keyData.dividend, keyData.dividendFrequency);
			payoutRatioCell.value = keyData.payoutRatio;

			exDividendDateCell.numberFormat = {
				type: "DATE",
				pattern: "mmmm dd, yyyy",
			};
			
			exDividendDateCell.value = keyData.exDividendDate;		
		}
			}

	await sheet.saveUpdatedCells();

[EstevaoTerci]

Maybe it's something related to parsing the nuber to string as shown in this article:

https://spreadsheetpoint.com/keep-leading-zeros-in-google-sheets/

[antony]

You need to set the date as a serialNumber otherwise it gets translated to a string. See https://github.com/theoephraim/node-google-spreadsheet/issues/363#issuecomment-1437308370

[theoephraim]

opened a new issue #630 related specifically to dates.

Also about to release a new version which lets you explicitly set a cell.stringValue which should handle escaping better.