Get all the contents of a cell as text-CodePudding

I have two google spreadsheets. One of the sheets does all the math/formula while the second sheet pulls that data using the =IMPORTRANGE function. The data pulled is an URL. What I need is to extract just the contents of the cell.

I would like the contents of 'H1' to automatically paste to cell 'J1' in text format. I searched the web but every solution just talks about using "Ctrl Shift V" to copy and paste the 'values only'. Is there any way to automate this process and write a formula in cell 'I1' to extract the text from the formula cell which is 'H2' and paste it as text in cell 'J1'?

CodePudding user response：

use in J1:

=ARRAYFORMULA(H1:H&"")

CodePudding user response：

If you want just the contents of the H1 cell being pasted onto J1 cell, you will have to make try Apps Script and use the following lines of code:

function retrieveValue() {
  let ss = SpreadsheetApp.getActive().getSheetByName('SHEET_NAME');
  let cellH1 = ss.getRange('H1').getValue();
  ss.getRange('J1').setValue(cellH1);
}

The script basically retrieves the H1 cell value from the SHEET_NAME sheet by using the getValue method and later sets this value for the J1 cell.

To set this script up, from your Spreadsheet, you will have to go to Extensions > Apps Script and when prompted with the editor, simply paste the above lines of code.

Reference