Forum Discussion
Script to convert CSV to table
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
9 Replies
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!
- Hi NikolinoDE,
Thank you for your reply. In the script you provided the sales name, first name and email are always the same. The names and emails are changing all the time. So in the script it should be interactive, depending what is in the csv file.
Is that possible to have it in a script?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.
