I am currently reading data from a CSV, and I am pasting the data to a sheet using a comma as a delimiter. I would like to input the numbers from the CSV as text to the sheet. For example, I would like to input 0003928371 as "0003928371" (to keep the leading zeros). Is there a way to do this?

function populateSheet(sheetsAPI, csvData, sheetId) {
  return new Promise((resolve, reject) => {
    const dataAndStyle = {
      spreadsheetId: process.env.SPREADSHEET_ID,
      resource: {
        requests: [
          {
            pasteData: {
              coordinate: {
                sheetId: sheetId,
                rowIndex: 1,
                columnIndex: 0
              },
              data: csvData,
              delimiter: ","
            }
          }
        ]
      }
    };
        
    sheetsAPI.spreadsheets.batchUpdate(dataAndStyle, function(err) {
      if (err) {
        reject("The Sheets API returned an error: "   err);
      } else {
        console.log(sheetId   " sheet populated with "   csvData.length   " rows and column style set.");
        resolve();
      }
    });    
  });
}

I was hoping to use valueInputOptions: "RAW", but this is not valid for spreadsheets.batchUpdate, only spreadsheets.values.batchUpdate.

CodePudding user response：

From your following comment,

I was hoping to use valueInputOptions: "RAW", but this is not valid for spreadsheets.batchUpdate, only spreadsheets.values.batchUpdate.

For example, how about the following modification?

Modified script:

function populateSheet(sheetsAPI, csvData, sheetId) {
  return new Promise((resolve, reject) => {
    const dataAndStyle = {
      spreadsheetId: process.env.SPREADSHEET_ID,
      resource: {
        requests: [
          {
            repeatCell: {
              cell: { userEnteredFormat: { numberFormat: { type: "TEXT" } } },
              range: {
                sheetId,
                startRowIndex: 1,
                endRowIndex: csvData.split("\n").length,
              },
              fields: "userEnteredFormat.numberFormat",
            },
          },
          {
            pasteData: {
              coordinate: { sheetId, rowIndex: 1, columnIndex: 0 },
              data: csvData,
              delimiter: ",",
            },
          },
        ],
      },
    };
    sheetsAPI.spreadsheets.batchUpdate(dataAndStyle, function (err) {
      if (err) {
        reject("The Sheets API returned an error: "   err);
      } else {
        console.log(
          sheetId  
            " sheet populated with "  
            csvData.length  
            " rows and column style set."
        );
        resolve();
      }
    });
  });
}

• In this modification, the number format of the pasted rows is changed to TEXT type. By this, the value of 0003928371 in the CSV data is put as 0003928371 as a string.
• In this modification, in order to retrieve the number of rows of CSV data, csvData.split("\n").length is used. But, I'm not sure about your actual CSV data. So, if this was not suitable, please modify it for your actual situation. For example, if you want to set the number format to the whole sheet, you can use range: { sheetId }.

Reference:

• RepeatCellRequest

CodePudding user response：

A simple, surefire fix is to concatenate a space at the beginning: " 0003928371"

This should be pretty robust against any other attempts the sheet may do to re-format it as a number after you input it, too.