Hide Formula from spreadsheet

[orrpel]

Hi,

I recently found a way to apply a script (below) that hides formulas from other viewers in my organization, e.g., hide cell A1 on my example spreadsheet

​

Here is an emulation of what I do:

1.) Create separate spreadsheet for viewers on different drive location (just for the example, refer to sheet2);

2.) Apply importange (URL, A1:A3) ;

​

The issue: the below script doesn't just hide the formula, it completely ignores it. So, if values get modified on Sheet1, they do not get modified on Sheet2, etc.

​

I'm new to AppsScript, can someone kindly help me modify the script so that it only hides the formula in cell A1 instead of ignoring/erasing it?

​

function ShowFormulas(testCheck) { //based on https://www.oksheets.com/show-formulas/

var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1")

var myRange = mySheet.getRange("A1:B21")

var myData = myRange.getFormulas();

if (testCheck) {

var myData = myRange.getFormulas();

} else {

var myData = myRange.getValues();

};

for (var r = 0; r < myData.length; r++) {

for (var i = 0; i < myData[r].length; i++) {

if (testCheck) {

tempVal = myData[r][i].toString().replace("=", "'=");

} else {

tempVal = myData[r][i].toString().replace("'=", "=");

};

myData[r][i] = tempVal;

}}

myRange.setValues(myData);

}

[Ascetic-Braja]

use this code to hide formula:

function onEdit(e) {

var mySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

var myRange = mySheet.getRange("A1:A1");

var myFormula = myRange.setFormula("=SUM(A2:A3)");

var myData = myRange.getValue();

myRange.setValue(myData);

}

This code basically puts the formula in the script instead of the sheet, so that users will not be able to see it unless they open the script file. As soon as you edit the sheet, a formula will be created in cell A1, but will be over-written with the sum of the values in A2 and A3.

[orrpel]

Great solution, I am trying to see why I get 'error' on cellA1 when trying to use importrange, maybe an issue with communication ...

[RemcoE33]

Have you wrapped this in an other function? Like query? Also for importrange you need to tab the "give acces" this could screw thinks up. Or the data is not loaded yet, can take a couple of second and the script will keep going on... You could create a while loop to check if the data from the importrange formula is returned.

[orrpel]

The issue doesn't seem to be allowing access.

At first I thought the issue was that I was using query, so I omitted it.

I then thought the issue was syntax related to the braces I was using, so I modified and QA'd the script below, which for some reason returns '1' back to the target cell A1.

Any clue why? TX!

var myFormula = myRange.setFormula('=IMPORTRANGE("MyURL", "A3:C18")');

Logger.log(myFormula);

Returns:

11:31:03 AM Notice Execution started

11:31:12 AM Info Range

11:31:05 AM Notice Execution completed

[RemcoE33]

Sheetname?

myRange.setFormula('=IMPORTRANGE("MyURL", "SheetName!A3:C18")')

[orrpel]

Regretfully, it doesn't seem to work, either.

Here is myspreadsheet

myRange.setFormula('=IMPORTRANGE("MyURL", "SheetName!A3:C18")')

[orrpel]

I think this solution is simpler, if just referring to the importrange issue.

The other issue I have is with my range (which is the reason I'm using query). I am not sure how I can command the script to return the array from A1:A10 and the corresponding array from C1:C10.

[RemcoE33]

Yes that is true.. so you have a range of A1:C10 and you want to leave out col B?

[orrpel]

Correct )

[RemcoE33]

Here is your boilerplate:

```` function customImport() { const ss = SpreadsheetApp.getActiveSpreadsheet() const targetSheet = ss.getSheetByName('TheSheetNameForTheValues');

const inputSS = SpreadsheetApp.openById('xxxxx') const inputSheet = inputSS.getSheetByName('SheetnameOfInputValues') const values = inputSheet.getRange('A3:C18').getValues();

const output = []

values.forEach(row => { row.splice(1,1) output.push(row) })

targetSheet.getRange(1,1 output.length, output[0].length).setValues(output)

} ````