Google App Script get (MID) string from named range

1
votes

I'm a newbie with GAS and this is my first question on Stackoverflow. Hopefully I can explain my question in a correct manor for you.

I have 2 sheets in 1 spreadsheet named "INPUT" & "DATA". On the INPUT sheet there is a named range called "ScanNr" (the value is always 18 digits)

The value of this named range needs to be copied to sheet DATA (col A) in a new row.

At the moment I'm using a MID-formula in sheet DATA (col B) to get a part of the scannr.

Can you help me adjust the script so I don't have to use a formula? An example of the current formula is "=MID(A5, 8, 4)"

This is my code sofar:

    function SaveScan() {
    var ss       = SpreadsheetApp.getActiveSpreadsheet();
    var myForm   = ss.getSheetByName("INPUT");
    var myData   = ss.getSheetByName("DATA");
    
    var myScan   = myForm.getRange("ScanNr").getValue();
      
      myData.getRange(myData.getLastRow()+1, 1).setValue(myScan);
  //  myData.getRange(myData.getLastRow(), 2)  // ?? I don't know how to do this.

    }

Any help will be highly appreciated.

1
google-apps-scriptgoogle-sheets

1 Answers

0
votes

In your situation, how about the following modification? I think that =MID(A5, 8, 4) can be converted to value.substr(7, 4) using Javascript. And, in your situation, I thought that appendRow might be suitable.

From:

myData.getRange(myData.getLastRow()+1, 1).setValue(myScan);

To:

myData.appendRow([myScan, myScan.substr(7, 4)]);

References: