CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Leon K.
Leon K.

Reputation: 115

Update data from a Spreadsheet - Apps Script

I'm doing a data transfer of several spreadsheets to a single one, what I do is transfer the last data of certain columns to the master spreadsheet and also insert them in the last available row of certain columns, for now, I insert all the data but I would like to to know how I can have it examine the master spreadsheet so that if those data already exist, it does not delete them but update them. The script that I have is the following ... 

  function  Gas10(){  
  var ss1 = SpreadsheetApp.openById("ID"); 
  var ssh1 = ss1.getSheetByName("Sheet 1");


var lastRow1 = ssh1.getLastRow();
var gtRange1 = ssh1.getRange("C"+(lastRow1)+":K"+(lastRow1)).getValues();
var gtRange2= ssh1.getRange("A" + (lastRow1)).getValue();

var ss2 = SpreadsheetApp.getActiveSpreadsheet();

var ssh2 = ss.getSheetByName("Sheet 2");
var lastRow2 = ssh2.getLastRow() + 1;
var setRange1 = ssh2.getRange(lastRow2, 4, gtRange1.length, gtRange1[0].length).setValues(gtRange1);
var setRange2 = ssh2.getRange(lastRow2, 3).setValue(gtRange2);            
}

I need to know how I can do it when I insert a piece of information (I already do that), but update it if it already exists. This is the example that I created so that it can be better understood, in this example I have two sheets of which from sheet 1 I pass data to sheet 2 and what I'm looking for is that sheet 2 updates all the data that are equal to (Name, Num, Proyect). I hope that now I understand better what I'm looking for.

Upvotes: 0

Views: 3079

Answers (2)

JSmith
JSmith

Reputation: 4808

Basically what you have to do is

  • get the new Line you want to add to the destination spreadsheet
  • get all the required datas of the destination spreadsheet
  • Check if the new Line datas have the same datas than in the destination data array
    • If so change ID value
  • paste changed datas in the destination spreadsheet

based on this spreadsheet The code should look something like this

function  Gas10(){  
    var ss1 = SpreadsheetApp.getActiveSpreadsheet();
    var ssh1 = ss1.getSheetByName("Sheet 1");
    var ssh2 = ss1.getSheetByName("Sheet 2");
    var lastRow1 = ssh1.getLastRow();
    var lastCol1 = ssh1.getLastColumn();
    var newLine = ssh1.getRange(lastRow1, 2, 1, lastCol1 - 1 ).getValues();
    var destDatas = ssh2.getDataRange().getValues();
  for (var i = 1; i < destDatas.length; i++)
  {
      if (newLine[0][0] == destDatas[i][0]
       && newLine[0][1] == destDatas[i][1]
        && newLine[0][2] == destDatas[i][2])
      {
       destDatas[i][3] = newLine[0][3];
      }
  }
  // add newLine to destDatas
    destDatas.splice(destDatas.length, 0, newLine[0]);
  var lastColumn = ssh2.getLastColumn();
  var lastRow2 = ssh2.getLastRow() + 1;
  ssh2.getRange(1, 1,  destDatas.length, lastColumn).setValues(destDatas);
  ssh1.deleteRow(lastRow1);
}

Upvotes: 2

Cooper
Cooper

Reputation: 64062

Here's an example I played around with:

It looks at the slave sheet for any data. When it finds data it puts the row and col and value into an obj which is then added to an array. When it finishes it calls the updMaster which then looks for data in those same cells (assuming that the cells are in the same place if those cells are blank then it adds data and I also changed the background to lightblue to show me where it updated the cells.

You could run the getSlaveData() for different sheets if you wish.

function getSlaveData(){
  var ss=SpreadsheetApp.getActive();
  var ssh=ss.getSheetByName('Sheet2');
  var sA=[];
  var srg=ssh.getDataRange();
  var svA=srg.getValues();
  for(var i=0;i<svA.length;i++){
    for(var j=0;j<svA[i].length;j++){
      //if(svA[i][j]){
      if(!ssh.getRange(i+1,j+1).isBlank()){//optional way to look for values
        var sObj={};
        sObj['row']=i + 1;
        sObj['col']=j + 1;
        sObj['value']=svA[i][j];
        sA.push(sObj);
      }
    }
  }
  updMaster(sA);
}

function updMaster(sA){
  var ss=SpreadsheetApp.getActive();
  var msh=ss.getSheetByName('Sheet1');
  for(var i=0;i<sA.length;i++){
    if(msh.getRange(sA[i].row,sA[i].col).isBlank()){
      msh.getRange(sA[i].row,sA[i].col).setValue(sA[i].value);
      msh.getRange(sA[i].row,sA[i].col).setBackground('lightblue');
    } 
  }
}

Upvotes: 2

Related Questions