adding data to next row / adding row if no rows detected

Question

I'm new to using appscript / app script api in general.

How do I add data to next available row? And if there is no row, add a new row? I looked over the sheets API in app script dev site and couldn't find a reference to adding to next row...

My current code looks like this:

Where it iterates through gmail labels and supposed to add specific info from gmail emails onto sheets rows. Currently it only adds to the first row.

const data = []
function myFunction() {
  const spreadsheetId = 'id'
  const sheetName = 'application tracker'
  const resource = {
    valueInputOption: 'USER_ENTERED',
    data: data,
  };
  
  const sheetLink = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName)
  console.log(sheetLink)

  // const labels = GmailApp.getUserLabels();
  // for (let i = 0; i < labels.length; i++) {
  //   const labelNames = labels[i].getName()
  //   let threads = labels[i].getThreads();
  //   for (let j = 0; j < threads.length; j++) {
  //     let messages = threads[j].getMessages();
  //     for (let k = 0; k < messages.length; k++) {
  //       let message = messages[k];
  //       let subject = message.getSubject();
  //       data.push({
  //         range: 'C1', // Update single cell
  //         values: [[subject]],
  //       });
  //       console.log('subject:', subject)
  //       let from = message.getFrom();
  //       data.push({
  //         range: 'B1', // Update single cell
  //         values: [[from]],
  //       });
  //       console.log('from:', from)
  //       let date = message.getDate();
  //       data.push({
  //         range: 'A1', // Update single cell
  //         values: [[date]],
  //       });
  //       console.log('date:', date)
  //       data.push({
  //         range: 'D1', // Update single cell
  //         values: [[labelNames]],
  //       });

  //     }
  //     console.log('sheet data:', data)
  //     console.log('---------')
  //   }
  //   Sheets.Spreadsheets.Values.batchUpdate(resource, spreadsheetId);
  // }
  // console.log('done')
}

score 0 · Answer 1 · answered Aug 03 '22 at 19:35

With this as example i think you will figure it out. Calls from and to the sheet are time consuming. Best practice is to push all the values to an array (like you are doing) and paste all the values in one call. The .getRange() method can accepts an A1 notation or startrow | startcolumn | number of rows | number of columns. There is an .getLastRow() method and with a simple + 1 you have the next free row.

I hope this helps:

function boilerPlate(){
  const results = []

  for(let i = 0; i < 20; i++){
    results.push([i])
  }

  const ss = SpreadsheetApp.openById('xxx')
  const targetSheet = ss.getSheetByName('Data')
  targetSheet.getRange(targetSheet.getLastRow() + 1, 1, results.length, results[0].length).setValues(results)
}

adding data to next row / adding row if no rows detected

1 Answers1