CODEWITHSUNDEEP
google-sheetsgoogle-apps-script
Akash Dev
Akash Dev

Reputation: 33

Update function not working in Google Sheets AppScript

My Requirement :

Step 1 : Display the details of a person in the cell range C5:C15 using his/her phone number(in B3)from 'EnquiriesDB' sheet, when the search button is clicked (this function is working fine)

Step 2 : After fetching the details edit the desired values in the range C5:C15 and update it in 'EnquiriesDB' sheet where the value of 'B3' in 'sales' sheet matches the row of column F in 'EnquiriesDB' sheet. This update should start from column B of 'EnquiriesDB' sheet, when Update button is clicked.

Below is the update function I use. When I click the update button the script is running successfully but no updates are reflected in the 'EnquiriesDB' sheet.

 function updateData() {  
  var SPREADSHEET_NAME = "EnquiriesDB";  
  var SEARCH_COL_IDX = 0;  
  var RETURN_COL_IDX = 0;   
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var formSS = ss.getSheetByName("Sales");  
  var datasheet = ss.getSheetByName("EnquiriesDB");
  var str = formSS.getRange("B3").getValue();  
  var values = ss.getSheetByName(SPREADSHEET_NAME).getDataRange().getValues();  
  for (var i = 0; i < values.length; i++) {    
    var row = values[i];    
    if (row[SEARCH_COL_IDX] == str) {     
      var INT_R = i+1      
      var values1 = [[formSS.getRange("C5").getValue(),        
                      formSS.getRange("C6").getValue(),                 
                      formSS.getRange("C7").getValue(),                 
                      formSS.getRange("C8").getValue(),                 
                      formSS.getRange("C9").getValue(),
                      formSS.getRange("C10").getValue(),
                      formSS.getRange("C11").getValue(),
                      formSS.getRange("C12").getValue(),
                      formSS.getRange("C13").getValue(),
                      formSS.getRange("C14").getValue(),
                      formSS.getRange("C15").getValue()]]; 
      datasheet.getRange(INT_R, 2, 1, 11).setValues(values1); 
      SpreadsheetApp.getUi().alert(' "Data Updated "');      
      return row[RETURN_COL_IDX];    
    }  
  }
}

I changed the name from 'abc' to 'abcd' and clicked the update button. But it did not update the row in 'EnquiriesDB' sheet.

Can someone please help me figure out the issue?

EnquiriesDB sheet : enter image description here

Sales sheet :

enter image description here

Upvotes: 0

Views: 91

Answers (1)

vector
vector

Reputation: 1022

Based on your comment, and available screenshot which shows form fields and column are in correct order. how about trying this :-

function updateData()
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formSS = ss.getSheetByName('Sales')
  var datasheet  = ss.getSheetByName('EnquiriesDB')
  var dataRange = formSS.getRange('C5:C11').getValues().map(function(r){return r[0]}); // getting values and changing it 1D array
  datasheet.appendRow([dataRange[0],dataRange[1],dataRange[2],dataRange[3],dataRange[4],dataRange[6]])
}

Note: appendRow() gets the last empty row and update it.

Resource:

appendRow()

Upvotes: 0

Related Questions