CODEWITHSUNDEEP
node.jsgoogle-apps-scriptgoogle-sheets
usama sulaiman
usama sulaiman

Reputation: 2019

How to get the first empty cell in a googlesheet

I'm using below Nodejs code to insert some text to google spreadsheet, what I need to know is:

How can I make the code detect the first empty cell in specific column , for example first empty cell in column A, so I can start add my text to it, (or last cell which has value in column A, so I can start add my text after it)

Here you are existing code:

const mySpreadSheetId = 'mySheetId';
const range = 'Sheet2!A10';
return new Promise((resolve, reject) => {
    sheets.spreadsheets.values.update({
        auth: jwtClient,
        spreadsheetId: mySpreadSheetId,
        range: range,
        valueInputOption: 'USER_ENTERED',
        resource: {range: 'Sheet2!A10',
            majorDimension: 'ROWS',
              values: [["some Text"]]}     
    } ,(err, resp) => {

        if (err) {
            console.log('Data Error :', err);
            reject(err);
        }
        resolve(resp);
    });
});

Upvotes: 1

Views: 911

Answers (1)

Tanaike
Tanaike

Reputation: 201748

  • You want to put a value to the 1st empty cell in the specific column.
  • You want to achieve this using googleapis with Node.js.
  • You have already been able to get and put values for the Google Spreadsheet using Sheets API.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Flow:

In order to know the 1st empty row in the specific column, at first, the values are required to be retrieved. The flow of this modified script is as follows.

  1. Retrieve the values of the specific column you want to retrieve the the 1st empty cell.
  2. Retrieve the 1st empty row.
  3. Put the value to the cell using the retrieved 1st empty row.

Modified script:

Please modify the script in new Promise((resolve, reject) => { ### }) as follows. Before you run the script, please set the variables of mySpreadSheetId and sheetName. In this modified script, the 1st empty cell of the column "A" is retrieved and the value of some Text is put to the retrieved cell.

const mySpreadSheetId = 'mySheetId';
const sheetName = "Sheet2";

sheets.spreadsheets.values.get(
  {
    auth: jwtClient,
    spreadsheetId: mySpreadSheetId,
    range: `${sheetName}!A:A`
  },
  (err, res) => {
    if (err) {
      console.error(err);
      return;
    }
    const data = res.data.values;
    let i = 0;
    for (i = 0; i < data.length; i++) {
      if (!data[i][0]) break;
    }
    sheets.spreadsheets.values.update(
      {
        auth: jwtClient,
        spreadsheetId: mySpreadSheetId,
        range: `${sheetName}!A${i + 1}`,
        valueInputOption: "USER_ENTERED",
        resource: {
          majorDimension: "ROWS",
          values: [["some Text"]]
        }
      },
      (err, resp) => {
        if (err) {
          console.log("Data Error :", err);
          reject(err);
        }
        resolve(resp);
      }
    );
  }
);

Note:

  • Above modified script retrieves the 1st empty cell of the specific column. In this case, for example, when the values are set like 1, 2, , 4, 5, the value is put to the 3rd cell. If you want to append the value to the specific column, please modify above script as follows.

    • From:

      let i = 0;
for (i = 0; i < data.length; i++) {
  if (!data[i][0]) break;
}

    • To:

      let i = data.length;

References:

If I misunderstood your question and this was not the direction you want, I apologize.

Upvotes: 3

Related Questions