Apr 18 2023 01:12 AM
Hello,
I am trying to create a script that convert csv to an excel table to use in power automate to send emails from that script.
The emails need to be clickebale
My csv example
[Sales Name],[First Name],[Email]
John Dow, John,email address removed for privacy reasons
Roger Stone, Roger,email address removed for privacy reasons
Claire Dupont,Claire,email address removed for privacy reasons
Final Result to Achieve
Apr 18 2023 02:17 AM
From what I could gather from your text, you want to create an office script that converts a CSV file to an Excel spreadsheet, and then use that script in Power Automate to send emails.
Here's an example of how you can write an Office script to convert a CSV file to an Excel spreadsheet:
function main(workbook: ExcelScript.Workbook) {
// Get the current worksheet
let sheet = workbook.getActiveWorksheet();
// Get the data from the CSV file
let csvData = `Sales Name,First Name,Email
John Dow,John,email address removed for privacy reasons
Roger Stone,Roger,email address removed for privacy reasons
Claire Dupont,Claire,email address removed for privacy reasons`;
// Split the CSV data into rows
let rows = csvData.split("\n");
// Loop through each row
for (let i = 0; i < rows.length; i++) {
// Split the row into columns
let columns = rows[i].split(",");
// Loop through each column
for (let j = 0; j < columns.length; j++) {
// Set the value of the cell
sheet.getCell(i, j).setValue(columns[j]);
}
}
// Create a table from the data
let table = sheet.addTable(sheet.getUsedRange().getAddress(), true);
}
*Script from Internet.
This script gets the data from a CSV file and splits it into rows.
It then loops through each row and splits it into columns.
The script sets the value of each cell in the worksheet based on the data from the CSV file.
Finally, it creates a table from the data in the worksheet.
You can then use this script in Power Automate to convert your CSV file into an Excel table and send emails based on the data in the table.
As for making the emails clickable, you can format the cells containing email addresses as hyperlinks. This will make them clickable and allow users to easily send emails by clicking on the email addresses.
I hope this helps!
Apr 18 2023 05:50 AM
Apr 18 2023 06:10 AM
Example how to change the code to format the cells in email column as hyperlinks:
async function main(context: Excel.RequestContext) {
// Get the file from OneDrive
let response = await fetch("<YOUR_FILE_URL>");
let csvData = await response.text();
// Get the current worksheet
let sheet = context.workbook.worksheets.getActiveWorksheet();
// Split the CSV data into rows
let rows = csvData.split("\n");
// Loop through each row
for (let i = 0; i < rows.length; i++) {
// Split the row into columns
let columns = rows[i].split(",");
// Loop through each column
for (let j = 0; j < columns.length; j++) {
// Set the value of the cell
sheet.getCell(i, j).values = [[columns[j]]];
// Format the email column as a hyperlink
if (j === 2 && i > 0) {
sheet.getCell(i, j).hyperlink = `mailto:${columns[j]}`;
sheet.getCell(i, j).format.font.color = "blue";
sheet.getCell(i, j).format.font.underline = "Single";
}
}
}
// Create a table from the data
let table = sheet.tables.add(sheet.getUsedRange().getAddress(), true);
table.getHeaderRowRange().format.fill.color = "#4472C4";
table.getHeaderRowRange().format.font.color = "white";
await context.sync();
}
Just hope the file isn't in OneDrive.
Apr 18 2023 06:41 AM
@NikolinoDE
I copy past your script and I have some errors. I see I have to modify <YOUR FILE URL>, but I have errors on line 1, 7 and 32.
Apr 18 2023 06:47 AM
Apr 18 2023 06:50 AM - edited Apr 18 2023 06:53 AM
I added the URL (file based on OneDrive), but I still have the errors
Apr 18 2023 07:00 AM
Apr 18 2023 07:02 AM
Apr 18 2023 07:08 AM
If you have copied the script into the Office Scripts code editor in Excel on the web and it is not working as expected, there could be several reasons for this. Here are some common issues and troubleshooting steps you can try:
If you have tried these steps and are still encountering issues, If this didn't help either, I can't help, unfortunately, because I'm at my wits end.
Thank you for your understanding and patience