Good night, I got a script here, it works perfectly, except that I have one of the columns with date information (column E) and when imported, they return me in a string format, I don't know how to go around this for my weak knowledge!

CodePudding user response：

Although I'm not sure whether I could correctly understand your actual situation, when this sample script is modified, how about the following modification?

Modified script:

From:

function getdata2() {
  const srcSpreadsheetId = "###"; // Please set source Spreadsheet ID.
  const dstSpreadsheetId = "###"; // Please set destination Spreadsheet ID.
  const srcRange = "'Database'!A1:U";
  const dstRange = "Database";

  const values = Sheets.Spreadsheets.Values.get(srcSpreadsheetId, srcRange).values;
  const sheetId = SpreadsheetApp.openById(dstSpreadsheetId).getSheetByName(dstRange).getSheetId();
  Sheets.Spreadsheets.batchUpdate({requests:[{repeatCell:{range:{sheetId},fields:"userEnteredValue"}}]}, dstSpreadsheetId);
  Sheets.Spreadsheets.Values.update({values}, dstSpreadsheetId, dstRange, {valueInputOption: "USER_ENTERED"});
}

To:

function getdata2() {
  const srcSpreadsheetId = "###"; // Please set source Spreadsheet ID.
  const dstSpreadsheetId = "###"; // Please set destination Spreadsheet ID.
  const srcRange = "'Database'!A1:U";
  const dstRange = "Database";

  // Here, the date object is retrieved as the serial number.
  const values = Sheets.Spreadsheets.Values.get(srcSpreadsheetId, srcRange, { dateTimeRenderOption: "SERIAL_NUMBER", valueRenderOption: "UNFORMATTED_VALUE" }).values;

  const dstSheet = SpreadsheetApp.openById(dstSpreadsheetId).getSheetByName(dstRange);
  const sheetId = dstSheet.getSheetId();
  Sheets.Spreadsheets.batchUpdate({ requests: [{ repeatCell: { range: { sheetId }, fields: "userEnteredValue" } }] }, dstSpreadsheetId);
  Sheets.Spreadsheets.Values.update({ values }, dstSpreadsheetId, dstRange, { valueInputOption: "USER_ENTERED" });

  // Here, the number format is copied.
  const numberFormats = SpreadsheetApp.openById(srcSpreadsheetId).getRange(srcRange).getNumberFormats();
  dstSheet.getRange(1, 1, numberFormats.length, numberFormats[0].length).setNumberFormats(numberFormats);
}

Note:

• If the date object is put in only column "E" and the same number format is used, the last 2 lines might be able to be modified as follows. In this case, it supposes that the 1st row is the date object. If the header row is existing, please modify getRange("E2").

    const numberFormat = SpreadsheetApp.openById(srcSpreadsheetId).getRange(srcRange).getSheet().getRange("E1").getNumberFormat();
    dstSheet.getDataRange().setNumberFormat(numberFormat);
  

Reference:

• Method: spreadsheets.values.get