Google sheets script with API to add data after the last row with data-CodePudding

I have a script in google sheets that brings in data via API. Right now it is set to clear any old data in the sheet and then add the new data from the API, however in this instance I would like to be able to tell the script to look for the last row of data and the add the new API pull after the last row of data.

Below is the information that I believe needs to be changed. If anything other information is needed please let me know.

function setDataInToSheet(sheet, data){
  if(sheet.getLastRow() > 1)
    sheet.getRange(2, 1, sheet.getLastRow()-1, sheet.getLastColumn()).clear()
    
  var rows = []
  data.forEach(function(item){
    
    rows.push([item.name, item.fuelPercent.value, item.fuelPercent.time])
  })
  sheet.getRange(2, 1,rows.length,rows[0].length).setValues(rows);
}

I am really at a loss and am unsure how to complete this. Thank you in advance for any assistance that you can provide.

CodePudding user response：

Load Data from API

function loadDataFromAPI() {
  const sss = SpreadsheetApp.openById(sssId);
  const dss = SpreadsheetApp.getActive();
  const dssId = dss.getId();
  const ssh = sss.getSheetByName('Sheet1');//Source sheet
  const srg = ssh.getRange(1, 1, ssh.getLastRow(), ssh.getLastColumn());
  const dsh = dss.getSheetByName("Sheet1");//Destination sheet
  var vs = Sheets.Spreadsheets.Values.get(sssId, `${ssh.getName()}!${srg.getA1Notation()}`).values;
  console.log(JSON.stringify(vs));
  const lr = dsh.getLastRow();
  if(lr == dsh.getMaxRows()) {
    dsh.insertRowsAfter(lr,vs.length);
  }
  const drg = dsh.getRange(lr   1, 1, vs.length, vs[0].length);//appends to bottom of spreadsheet
  Sheets.Spreadsheets.Values.update({ values: vs }, dssId, `${dsh.getName()}!${drg.getA1Notation()}`, { valueInputOption: "USER_ENTERED" });
}

The important thing for you is not to clear the sheet but just load at sheet.getRange(sheet.getLastRow() 1 , 1....

CodePudding user response：

    function mainFunc(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheetName = "RawData"
    var url = "https://WEBSITE"
    var data = [];
    var response = getDataFromUrl(url)
    data = data.concat(response.data)
    var sheet = ss.getSheetByName(sheetName)
}

function getDataFromUrl(url){
  var headers = {
    "Accept": "application/json",
    "Content-Type": "application/json"
  }
  try{
    var options = {
      "method": "GET",
      "headers": {Authorization: 'Bearer api'},
    };
    var response = UrlFetchApp.fetch(url,options);
    Logger.log(response)
    return JSON.parse(response);
  }catch(e){
    Logger.log(e)
  }
}

function loadDataFromAPI() {
  const sss = SpreadsheetApp.openById(sssId);
  const dss = SpreadsheetApp.getActive();
  const dssId = dss.getId();
  const ssh = sss.getSheetByName('RawData');//Source sheet
  const srg = ssh.getRange(1, 1, ssh.getLastRow(), ssh.getLastColumn());
  const dsh = dss.getSheetByName("RawData");//Destination sheet
  var vs = Sheets.Spreadsheets.Values.get(sssId, `${ssh.getName()}!${srg.getA1Notation()}`).values;
  console.log(JSON.stringify(vs));
  const lr = dsh.getLastRow();
  if(lr == dsh.getMaxRows()) {
    dsh.insertRowsAfter(lr,vs.length);
  }
  const drg = dsh.getRange(lr   1, 1, vs.length, vs[0].length);//appends to bottom of spreadsheet
  Sheets.Spreadsheets.Values.update({ values: vs }, dssId, `${dsh.getName()}!${drg.getA1Notation()}`, { valueInputOption: "USER_ENTERED" });
}