google-apps-scriptrowmultiple-columnsedithistory

How to edit data of in last row of Column D of Specific Sheet


I append data from 1st row "A-E" Column to new row everyday via google script to keep data history. I need a script to change this last row data in "D" Column as per 1st row when needed.
Please guide on What to add/change in this script

function saveData() {
  
  {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('History');
  const date = sheet.getRange('History!A1').getValue();
  const data1= sheet.getRange('History!B1').getValue();
  const data2 = sheet.getRange('History!C1').getValue();
  const data3 = sheet.getRange('History!D1').getValue();
  const data4 = sheet.getRange('History!E1').getValue();
    sheet.appendRow([date,data1,data2,data3,data4]);
  
}
}

Solution

  • To update the last row's D column value based on the value in D1, modify your script as follows:

    function saveData() {
      const ss = SpreadsheetApp.getActiveSpreadsheet();
      const sheet = ss.getSheetByName('History');
    
      // Get data from the first row
      const date = sheet.getRange('A1').getValue();
      const data1 = sheet.getRange('B1').getValue();
      const data2 = sheet.getRange('C1').getValue();
      const data3 = sheet.getRange('D1').getValue();
      const data4 = sheet.getRange('E1').getValue();
    
      // Append new row
      sheet.appendRow([date, data1, data2, data3, data4]);
    
      // Update last row's D column with D1 value
      const lastRow = sheet.getLastRow();
      sheet.getRange(lastRow, 4).setValue(data3); // Column D = 4
    }