CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsgoogle-sheets-formulaspreadsheet
Felipe Arêde
Felipe Arêde

Reputation: 3

Copy data from one spreadsheet to another on edit

I already try everthing but I cant make this work =/

I'm trying to copy data from one spreadsheet to other using setValues(), because link doesn't work for me. I also need to keep the trigger on edit.

So, I create one fuction called AddConvocacao, and always when have any change the script run.

function addConvocacao(e){
  var linha = e.range.getRow(); //get the number of the line, where have edit
  var nome = e.source.getActiveSheet().getRange(linha,2).getValue(); //get the value of the field 

  var targetSheet = SpreadsheetApp.openById('1iQbZ7iB9ddc-HwLK9vG0eVEeWWqXKJZ0ry7U_Hm4SkQ') //open the other spreedsheet 
  var targetName = targetSheet.getSheetByName('Pac'); //open the tab
  var lastRow = targetName.getLastRow(); //count the last row in this tab
  lastRow = lastRow+1; //add 1
  
 targetName.getRange(lastRow, 2).setValue(nome); // set the value 
 // targetName.getRange(lastRow, 3).setValue(valorCol7);
 // targetName.getRange(lastRow, 5).setValue(dose);
 // targetName.getRange(lastRow, 6).setValue(diagnostico);
 // targetName.getRange(lastRow, 7).setValue(medico);
}

why dosen't work when I use on edit?

Thanks so much! =)

Upvotes: 0

Views: 78

Answers (1)

Cooper
Cooper

Reputation: 64040

I did it a little different I think. I put checkboxes in column one and capture the value from column 2 and sent it to the other spreadsheet to the bottom of column 2

function onMyEdit(e) {
  //e.source.toast('entry');
  const sh = e.range.getSheet();
  //Logger.log(JSON.stringify(e));
  if (sh.getName() == 'Sheet1' && e.range.columnStart==1 && e.value=="TRUE") {
    //e.source.toast('cond');
    const v = sh.getRange(e.range.rowStart, 2).getValue();
    var tss = SpreadsheetApp.openById(getGlobal('targetid'));
    var tsh = tss.getSheetByName('Sheet1');
    tsh.getRange(tsh.getLastRow() + 1, 2).setValue(v);
    e.range.setValue("FALSE");
  }
}

You must use an installable trigger to us openById();

getGlobal(targetid) just gets a spreadsheet id you can replace that with the id

Also I limited the action to only one sheet and only when I set the checkbox to true. I reset it back to false in the code.

You'll need to modify a few things to get it to work for you because when I'm doing the examples I do it my way.

Upvotes: 1

Related Questions