npm run import -- "update a row in google sheets"

var util = require('util');
var importer = require('../Core');
var getRows = importer.import("get worksheet rows");
var authorizeSheets = importer.import("authorize sheets api");
var getSheetByName = importer.import("get worksheet by name");

var getOrderedValues = (columns, rowData) => ({
    // Google sheets API, NULL means don't write value in range, as opposed to "" which clears
    values: [columns.map(c => typeof rowData[c] === 'undefined'
                         ? null
                         : rowData[c])]
})

var getRange = (title, columns, match, rows) => {
    var row = rows.filter(r => match(columns.reduce((acc, c, i) => (acc[c] = r[i], acc), {}), r))[0];
    // if no match, add it
    var index = (rows.indexOf(row) + 1) || (rows.length + 1);
    var range = `'${title}'!A${index}:${String.fromCharCode(65 + columns.length - 1)}${index}`;
    return range;
}

function updateRow(link, match, updateRow, page) {
    var sheets, sheet;
    
    return authorizeSheets()
        .then(s => (sheets = s, getSheetByName(link, page)))
        .then(sh => (sheet = sh, getRows(link, sheet)))
        .then(rows => {
            // if passing an array, just use that
            var columns = typeof updateRow.length != 'undefined'
                ? new Array(updateRow.length)
                : rows[0];
        
            var range = getRange(sheet.properties.title, columns, match, rows);
        
            console.log(`updating range ${range} ${columns}`);
            return sheets.spreadsheets.values.update({
                spreadsheetId: sheet.spreadsheetId,
                range: range,
                valueInputOption: 'RAW',
                resource: getOrderedValues(columns, updateRow)
            })
        })
        .then(() => updateRow)
}

module.exports = updateRow;

What the code could have been:

const { google } = require('googleapis');

const authorizeSheets = async () => {
    // TODO: Add auth logic here
    // For now, we assume it's already set up
    const auth = new google.auth.GoogleAuth();
    const client = await auth.getClient();
    const googleSheets = google.sheets({ version: 'v4', auth: client });
    return googleSheets;
};

const getRows = async (spreadsheetId, sheetName) => {
    try {
        // Get rows from the spreadsheet
        const sheets = await authorizeSheets();
        const result = await sheets.spreadsheets.values.get({
            spreadsheetId,
            range: `${sheetName}!A:Z` // Assume we're looking at the first 26 columns
        });
        return result.data.values;
    } catch (error) {
        console.error(`Error getting rows: ${error}`);
        return [];
    }
};

const getSheetByName = async (spreadsheetId, sheetName) => {
    // Get sheet by name
    try {
        const sheets = await authorizeSheets();
        const result = await sheets.spreadsheets.get({
            spreadsheetId,
            fields:'sheets/data'
        });
        const sheet = result.data.sheets.find(sheet => sheet.properties.title === sheetName);
        if (!sheet) {
            throw new Error(`Sheet "${sheetName}" not found`);
        }
        return sheet;
    } catch (error) {
        console.error(`Error getting sheet: ${error}`);
        return null;
    }
};

const getOrderedValues = (columns, rowData) => ({
    values: [columns.map(c => typeof rowData[c]!== 'undefined'? rowData[c] : null)]
});

const getRange = (title, columns, match, rows) => {
    // Calculate the range based on the match
    const row = rows.find(r => match(r.reduce((acc, c, i) => ({...acc, [columns[i]]: c }), {})));
    if (!row) {
        // If no match, add it to the end
        const index = rows.length + 1;
        return `'${title}'!A${index}:${String.fromCharCode(65 + columns.length - 1)}${index}`;
    }
    // Find the index of the matched row
    const index = rows.indexOf(row) + 1;
    return `'${title}'!A${index}:${String.fromCharCode(65 + columns.length - 1)}${index}`;
};

const updateRow = async (link, match, updateRow, page) => {
    // TODO: Validate inputs
    if (!link ||!match ||!updateRow ||!page) {
        throw new Error('Invalid inputs');
    }

    // Get the sheet and rows
    const sheet = await getSheetByName(link, page);
    if (!sheet) {
        throw new Error(`Sheet "${page}" not found`);
    }
    const rows = await getRows(link, sheet.properties.title);

    // Calculate the columns
    let columns;
    if (Array.isArray(updateRow)) {
        columns = updateRow;
    } else {
        columns = Object.keys(updateRow);
    }

    // Get the range to update
    const range = getRange(sheet.properties.title, columns, match, rows);

    // Update the row
    const sheets = await authorizeSheets();
    const result = await sheets.spreadsheets.values.update({
        spreadsheetId: sheet.spreadsheetId,
        range,
        valueInputOption: 'RAW',
        resource: getOrderedValues(columns, updateRow)
    });
    console.log(`Updated range ${range}`);
    return result;
};

module.exports = updateRow;