Unexpected Row Insertion Above Headers When First Column Is Blank

[harsha-iiiv]

Expected behavior

• When appending a new row to a Google Sheet using the API, the new row should be added below the existing data, respecting the presence of table headers.

Actual behavior

• When the first column is left blank, the new row is inserted above the headers, disrupting the intended data structure.

[ddelgrosso1]

Hi @harsha-iiiv can you provide a code snippet that demonstrates this behavior?

[harsha-iiiv]

@ddelgrosso1 Here it is, Thank you.

       const headerResponse = await getHeader({
            auth: commons.getOauth2Client(context.auth),
            spreadsheetId: sheetId,
            range: `${worksheetId.split('/')[1]}!1:1` // Assuming headers are in the first row
        });

        if (!headerResponse.values || headerResponse.values.length === 0) {
            throw new Error('Header row is empty or not found');
        }
        const headerValues = headerResponse.values[0];

        let startColumn = 'A'; // Default start column
        if (headerValues && headerValues.length > 0) {
            // Find the index of the first non-empty header and adjust the start column
            const firstNonEmptyIndex = headerValues.findIndex(value => value !== '');
            if (firstNonEmptyIndex > -1) {
                // Convert index to corresponding column letter (0 -> A, 1 -> B, etc.)
                startColumn = String.fromCharCode('A'.charCodeAt(0) + firstNonEmptyIndex);
            }
        }

        const orderedValues = headerValues.map(column => values[column] || '');
 
        const resource = {
            values: [orderedValues]
        };

        // Find the next available row for insertion
        const rangeResponse = await sheets.spreadsheets.values.get({
            auth: commons.getOauth2Client(context.auth),
            spreadsheetId: sheetId,
            range: worksheetId.split('/')[1]
        });

        const nextRow = rangeResponse.values ? rangeResponse.values.length + 1 : 2;
        const response = await createRow({
            auth: commons.getOauth2Client(context.auth),
            spreadsheetId: sheetId,
            range: `${worksheetId.split('/')[1]}!${startColumn}${nextRow}`, // Start from column A and next available row
            valueInputOption: 'RAW',
            insertDataOption: 'INSERT_ROWS',
            resource
        });

[ddelgrosso1]

@harsha-iiiv what does the implementation of createRow look like?

[harsha-iiiv]

@ddelgrosso1 here it is, using "googleapis": "12.2.0"

        const sheets = google.sheets('v4');
        const getHeader = Promise.promisify(sheets.spreadsheets.values.get, { context: sheets.spreadsheets.values });
        const createRow = Promise.promisify(sheets.spreadsheets.values.append, { context: sheets.spreadsheets.values });