Recording script in Excel; formula not dragging down, only values

Question

I'm trying to create a script in Excel by recording. Everything works great except when I run it, the cell I dragged down to copy a formula down gives the result of the value from the first cell and not the formulas result. I've ensured automatic fill is on.

For example, I have a list of email addresses and I want this part of the script to "xxxx" the beginning of each. When I record, the result is email address removed for privacy reasons, the cell under is email address removed for privacy reasons and that is correct. However, when I run the script I get email address removed for privacy reasons in both cells, or in my case a few hundred.

Any ideas?

sergeibaklan · Answer

acklenk&nbsp;
You may substitute names directly in the script without formulae. The pattern for one column could be like

function main(workbook: ExcelScript.Workbook) {

const source = workbook
    .getActiveWorksheet()
    .getRange("E:E")
    .getUsedRange()
    .getOffsetRange(1,0)
    .getResizedRange(-1,0)
    .getValues()
    .map(v =&gt;
        [v[0].replace(/^(.)(.*)(.@[^@]*)$/,
          function (m, g1, g2, g3) {
            return g1 +
                   g2.replace(/[A-Za-z]/g, "*") +
                   g3;
          }
        )]
    )

const size = source.length

const target = workbook
    .getActiveWorksheet()
    .getRange("G1")
    .getOffsetRange(1,0)
    .getResizedRange(size-1,0)
    .setValues(source)

}
which returns

Pattern for RegEx is taken from here&nbsp;Regex to replace characters between strings - Stack Overflow

jkpieterse · Answer

Please post your script so we can see what is going on. Also, show us the formula you intended to drag down. Finally, I would "send a frown" about this (Excel desktop: in the ribbon, click Help, Feedback, Excel on-line: click "Give feedback to Microsoft" in the bottom-right corner)

acklenk · Answer

JKPieterse&nbsp;&nbsp;function&nbsp;main(workbook:&nbsp;ExcelScript.Workbook)&nbsp;{&nbsp;&nbsp;//&nbsp;Add&nbsp;a&nbsp;new&nbsp;worksheet&nbsp;&nbsp;let&nbsp;sheet2&nbsp;=&nbsp;workbook.addWorksheet();&nbsp;&nbsp;//&nbsp;Set&nbsp;range&nbsp;A1:G2&nbsp;on&nbsp;sheet2&nbsp;&nbsp;sheet2.getRange("A1:G2").setFormulasLocal([["First&nbsp;Visit","Company","Name","Title","Email","Phone","Client&nbsp;Feedback"],["=Sheet1!L2","=Sheet1!H2","=LEFT(PROPER(Sheet1!F2&amp;\"&nbsp;\"&amp;PROPER(Sheet1!G2)),20)","Owner","email address removed for privacy reasons","xxx-xxx-1270","=Sheet1!E2"]]);&nbsp;&nbsp;//&nbsp;Set&nbsp;format&nbsp;for&nbsp;range&nbsp;A:A&nbsp;on&nbsp;sheet2&nbsp;&nbsp;sheet2.getRange("A:A").setNumberFormatLocal("[$-en-US]mmmm&nbsp;d,&nbsp;yyyy;@");&nbsp;&nbsp;//&nbsp;Auto&nbsp;fit&nbsp;the&nbsp;columns&nbsp;of&nbsp;all&nbsp;cells&nbsp;on&nbsp;sheet2&nbsp;&nbsp;sheet2.getRange().getFormat().autofitColumns();&nbsp;&nbsp;//&nbsp;Auto&nbsp;fill&nbsp;range&nbsp;&nbsp;sheet2.getRange("A2:G2").autoFill("A2:G400",&nbsp;ExcelScript.AutoFillType.fillDefault);&nbsp;&nbsp;//&nbsp;Set&nbsp;print&nbsp;area&nbsp;for&nbsp;sheet2&nbsp;to&nbsp;range&nbsp;"A1:G400"&nbsp;&nbsp;sheet2.getPageLayout().setPrintArea("A1:G400");&nbsp;&nbsp;//&nbsp;Set&nbsp;print&nbsp;area&nbsp;for&nbsp;sheet2&nbsp;to&nbsp;range&nbsp;"A1:G400"&nbsp;&nbsp;sheet2.getPageLayout().setPrintArea("A1:G400");&nbsp;&nbsp;//&nbsp;Set&nbsp;ExcelScript.PageOrientation.portrait&nbsp;orientation&nbsp;for&nbsp;sheet2&nbsp;&nbsp;sheet2.getPageLayout().setOrientation(ExcelScript.PageOrientation.portrait);&nbsp;&nbsp;//&nbsp;Set&nbsp;Letter&nbsp;paperSize&nbsp;for&nbsp;sheet2&nbsp;&nbsp;sheet2.getPageLayout().setPaperSize(ExcelScript.PaperType["Letter"]);&nbsp;&nbsp;//&nbsp;Set&nbsp;No&nbsp;Scaling&nbsp;scaling&nbsp;for&nbsp;sheet2&nbsp;&nbsp;sheet2.getPageLayout().setZoom({scale:&nbsp;100});&nbsp;&nbsp;//&nbsp;Set&nbsp;horizontal&nbsp;alignment&nbsp;to&nbsp;ExcelScript.HorizontalAlignment.left&nbsp;for&nbsp;range&nbsp;A:A&nbsp;on&nbsp;sheet2&nbsp;&nbsp;sheet2.getRange("A:A").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.left);&nbsp;&nbsp;//&nbsp;Indent&nbsp;set&nbsp;to&nbsp;0&nbsp;for&nbsp;range&nbsp;A:A&nbsp;on&nbsp;sheet2&nbsp;&nbsp;sheet2.getRange("A:A").getFormat().setIndentLevel(0);&nbsp;&nbsp;//&nbsp;Set&nbsp;horizontal&nbsp;alignment&nbsp;to&nbsp;ExcelScript.HorizontalAlignment.center&nbsp;for&nbsp;range&nbsp;1:1&nbsp;on&nbsp;sheet2&nbsp;&nbsp;sheet2.getRange("1:1").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);&nbsp;&nbsp;sheet2.getRange("1:1").getFormat().setIndentLevel(0);&nbsp;&nbsp;//&nbsp;Indent&nbsp;set&nbsp;to&nbsp;0&nbsp;for&nbsp;range&nbsp;1:1&nbsp;on&nbsp;sheet2&nbsp;&nbsp;sheet2.getRange("1:1").getFormat().setIndentLevel(0);&nbsp;&nbsp;//&nbsp;Set&nbsp;font&nbsp;bold&nbsp;to&nbsp;true&nbsp;for&nbsp;range&nbsp;1:1&nbsp;on&nbsp;sheet2&nbsp;&nbsp;sheet2.getRange("1:1").getFormat().getFont().setBold(true);}

jkpieterse · Answer

Thanks. Looks like the recorded script places the hard-coded text "email address removed for privacy reasons" into column E, is that not what you need? Which formula did you intend to get in column E?

acklenk · Answer

Each row has a different email address and correct, I want a portion of the email address to be removed and replaced with x's for privacy reasons, however when I run the script it fills every single row with the concealed email address from the first row.&nbsp; Does that make sense?&nbsp;&nbsp;JKPieterse&nbsp;

Forum Discussion

Recording script in Excel; formula not dragging down, only values

7 Replies

Resources