1

I'm using the following code to copy-paste from a Gsheets file into other Gsheets file, but I would like to do it maintaining the original source format.

Anyone can help with this issue?

The problem today is that I have some numbers stored as text and after I copy-paste if, these fields automatically change their format into numbers.

Thank you so much in advance.

function Run_function1() {
  var ss = SpreadsheetApp.openById("Destination ID"); 
  const sk = ss.getSheetByName('Settings WS'); 
  var cc = sk.getRange("cell with origin file ID").getValue();  
  const sh = ss.getSheetByName('Destination WS'); 
  sh.getRange(2,1,sh.getLastRow(),30).clearContent(); 
  var dbss=SpreadsheetApp.openById(cc); 
  const dbsh=dbss.getSheetByName('Origin WS'); 
  var vs=dbsh.getRange(2,1,dbsh.getLastRow()-1,25).getValues(); 
  Logger.log(dbsh);
  var ff=sh.getRange(2,1,dbsh.getLastRow()-1,25).setValues(vs);   
}
user13332855
  • 37
  • 6

2 Answers2

2

I've figured it out. Using the getValues() SetValues() method, you'll have to add the command setNumberFormat("@") that will allow the text strings to be maintained as text strings. So, all we need to do is replace the last line of code with:

var ff=sh.getRange(2,1,dbsh.getLastRow()-1,25).setNumberFormat("@").setValues(vs);

Please refer also to the following article:

Script 'setValues' method interprets strings as numbers in cells

user13332855
  • 37
  • 6
1

Explanation:

  • Instead of copying the full data from the source sheet to the destination sheet, why don't you just copy the source sheet to the destination spreadsheet? In this way, you will get both the format and the values in one go.

The steps are the following:

  1. Get all sheets and spreadsheet objects:

    const ss = SpreadsheetApp.openById("Destination ID"); 
const sk = ss.getSheetByName('Settings WS');
const cc = sk.getRange("cell with origin file ID").getValue();  
const sh = ss.getSheetByName('Destination WS'); 
const dbss=SpreadsheetApp.openById(cc); 
const dbsh=dbss.getSheetByName('Origin WS');

  2. This step is optional, but you can get the sheet index of the destination sheet Destination WS so you can use it when you create a new one:

    const destIndex = sh.getIndex();

  3. Delete the previous Destination WS sheet:

    ss.deleteSheet(sh);

  4. Create a new destination sheet with the same name as before and place it to the same position as before. The new destination sheet will be the copy of the source sheet and it will carry both the format and the values:

    const newSheet = dbsh.copyTo(ss).setName('Destination WS');
ss.setActiveSheet(newSheet);
ss.moveActiveSheet(destIndex);

Solution:

function Run_function1() {
   
  const ss = SpreadsheetApp.openById("Destination ID"); 
  const sk = ss.getSheetByName('Settings WS');
  const cc = sk.getRange("cell with origin file ID").getValue();  
  const sh = ss.getSheetByName('Destination WS'); 
  const dbss=SpreadsheetApp.openById(cc); 
  const dbsh=dbss.getSheetByName('Origin WS'); 
  
  const destIndex = sh.getIndex();
  ss.deleteSheet(sh); // delete Destination WS
  const newSheet = dbsh.copyTo(ss).setName('Destination WS');
  ss.setActiveSheet(newSheet);
  ss.moveActiveSheet(destIndex);
}
Marios
  • 26,333
  • 8
  • 32
  • 52
  • Hello and thank you for your support. This is a good idea, but I can't do it because I need to consolidate several Worksheets into one and this is a sample of the code I use to copy each worksheet. Sorry for not having mentioned before. – user13332855 Oct 19 '20 at 09:57
  • 1
    Then create a copy of the original sheet as I am doing here in order to get the format. And then copy all of your data from different sheets into that sheet. @user13332855 – Marios Oct 19 '20 at 10:00
  • Hello, I've tried this solution but the problem is the original worksheet has some built-in import range formulas and I need to copy-paste data without the important range but maintaining the format. But your code works perfectly. – user13332855 Oct 19 '20 at 14:52