Script to get data from Google Sheet CSV

Actions - Share What You've Made
,
1

I appreciate the help I’ve received on the forum, and I wanted to at least give back a bit. Here is a JavaScript action step that takes the URL of a Google Sheet published as a URL, and gets values in the column under two headers: “Student Name” and “Student Email.” It then creates a newline-separated list of the student emails and passes them to a template tag for further action such as writing to a draft. I’m sure folks here can modify in ways useful for their projects.

const http = HTTP.create();
const url = draft.getTemplateTag("csvUrl");

// Function to convert CSV to JSON
const csvToJson = (csv) => {
    const [headers, ...rows] = csv.split('\n').map(row => row.split(','));
    return rows.map(row => Object.fromEntries(headers.map((header, index) => [header, row[index]])));
};

// Async function to fetch and process data
const fetchData = async () => {
    try {
        // Make HTTP request to fetch CSV data
        const response = await http.request({
            url: url,
            method: 'GET'
        });

        if (response.success) {
            // Process CSV data if request is successful
            const csvData = response.responseText;
            const jsonData = csvToJson(csvData);

            // Define headers for extraction
            const headers = [
                { name: 'Student Name', prop: 'name' },
                { name: 'Student Email', prop: 'email' }
            ];

            // Extract data based on headers
            const extractedData = jsonData.map(item => headers.reduce((acc, header) => {
                acc[header.prop] = item[header.name];
                return acc;
            }, {}));

            // Create a string of emails separated by newlines
            const studentEmails = extractedData.map(item => item.email).join('\n');

            // Display student emails in an alert
            alert(`Student Emails:\n${studentEmails}`);

            // Set the 'studentemails' template tag in the draft
            draft.setTemplateTag("studentemails", studentEmails);
        } else {
            // Display error message if request is not successful
            alert(`Error fetching data. Status code: ${response.statusCode}\nError message: ${response.error}`);
        }
    } catch (error) {
        // Display error message if there is an exception
        alert(`Error fetching data: ${error}`);
    }
};

// Call the async function to fetch and process the data
fetchData();

To have Google Sheets serve the CSV in the first place:

  • Publish the spreadsheet to the web by choosing “File” > “Share” > “Publish to the web”.
  • Select sheet to share.
  • Select “Comma-separated values (.csv).”
  • Close dialog.
  • Copy CSV serving link.
2 Likes