Copper Contributor

Feb 12, 2024

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

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 ...

excel

Formulas and Functions

Macros and VBA

Office Scripts

acklenk

Copper Contributor

Feb 13, 2024

JKPieterse

function main(workbook: ExcelScript.Workbook) {

// Add a new worksheet

let sheet2 = workbook.addWorksheet();

// Set range A1:G2 on sheet2

sheet2.getRange("A1:G2").setFormulasLocal([["First Visit","Company","Name","Title","Email","Phone","Client Feedback"],["=Sheet1!L2","=Sheet1!H2","=LEFT(PROPER(Sheet1!F2&\" \"&PROPER(Sheet1!G2)),20)","Owner","email address removed for privacy reasons","xxx-xxx-1270","=Sheet1!E2"]]);

// Set format for range A:A on sheet2

sheet2.getRange("A:A").setNumberFormatLocal("[$-en-US]mmmm d, yyyy;@");

// Auto fit the columns of all cells on sheet2

sheet2.getRange().getFormat().autofitColumns();

// Auto fill range

sheet2.getRange("A2:G2").autoFill("A2:G400", ExcelScript.AutoFillType.fillDefault);

// Set print area for sheet2 to range "A1:G400"

sheet2.getPageLayout().setPrintArea("A1:G400");

// Set print area for sheet2 to range "A1:G400"

sheet2.getPageLayout().setPrintArea("A1:G400");

// Set ExcelScript.PageOrientation.portrait orientation for sheet2

sheet2.getPageLayout().setOrientation(ExcelScript.PageOrientation.portrait);

// Set Letter paperSize for sheet2

sheet2.getPageLayout().setPaperSize(ExcelScript.PaperType["Letter"]);

// Set No Scaling scaling for sheet2

sheet2.getPageLayout().setZoom({scale: 100});

// Set horizontal alignment to ExcelScript.HorizontalAlignment.left for range A:A on sheet2

sheet2.getRange("A:A").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.left);

// Indent set to 0 for range A:A on sheet2

sheet2.getRange("A:A").getFormat().setIndentLevel(0);

// Set horizontal alignment to ExcelScript.HorizontalAlignment.center for range 1:1 on sheet2

sheet2.getRange("1:1").getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.center);

sheet2.getRange("1:1").getFormat().setIndentLevel(0);

// Indent set to 0 for range 1:1 on sheet2

sheet2.getRange("1:1").getFormat().setIndentLevel(0);

// Set font bold to true for range 1:1 on sheet2

sheet2.getRange("1:1").getFormat().getFont().setBold(true);

}

JKPieterse

Silver Contributor

Feb 14, 2024

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
Copper Contributor
Feb 14, 2024
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. Does that make sense? JKPieterse
- JKPieterse
  Silver Contributor
  Feb 15, 2024
  acklenk What you would need to do is add a new column which contains a formula that changes the email address to how you want it to look. Suppose the current email address is in cell E2, that formula could be something like this:
  ="xxx.xxxxxx@"&TEXTAFTER(E2,"@")
  Then once the formula is as you want it, record a script where you edit that formula cell so you can see its syntax and add this to your current script.
  - acklenk
    Copper Contributor
    Feb 17, 2024
    JKPieterse Thanks! I think I did what you were saying, but I just had to bring the full emails into the new tab, create a new column to process the formula and then copy modified cells, paste as values over actual email address and then delete the column with the formula. A ton of extra steps but it seems to work this way.

Forum Discussion

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

Resources