I created a script that can import xlsx file to google sheet and its currently working but I need to modify it to get the whole sheet without using specific range and paste it as normal text since the current output text has bold and large fonts, Can someone help me what I need to modify or change to my script?

My script

 const sourceValues = sourceSheet.getRange("B1:V5231").getValues();
 const target = SpreadsheetApp.openById(ID).getSheetByName('sheetname');
 target.getRange(target.getLastRow() 1, 1, sourceValues.length, sourceValues[0].length).setValues(sourceValues);

Thank you!

CodePudding user response：

SUGGESTION

If I have understood your question clearly, you want to dynamically get the range on your sheet that contains .xlsx data instead of explicitly using a specific range using the getRange() method.

You may try using the getDataRange() method as it returns a range corresponding to the dimensions in which data is present on your sheet.

As for the normal pasting of data as text without formatting, you can try using getDisplayValues() instead of using the getValues() as this will return the values as String objects.

[UPDATE]

If getDisplayValues() doesn't remove the text formatting, you can also use the clearFormats method as per this existing post. This method will clear the contents and/or format. See this sample usage below:

Script

const sourceValues = sourceSheet.getRange("B1:V5231").getValues();
const target = SpreadsheetApp.openById(ID).getSheetByName('sheetname');
target.getRange(target.getLastRow()   1, 1, sourceValues.length, sourceValues[0].length).setValues(sourceValues);
target.clearFormats();

The clearFormats method will remove the formatting of the text on the target sheet after setting the values.

References:

• getDataRange() Method
• getDisplayValues() Method
• clearFormats() Method