How to copy data from the answer sheet of the form to another spreadsheet or additional send the answers directly to another spreadsheet

Question

How to copy data from the answer sheet of the form to another spreadsheet or additional send the answers directly to another spreadsheet

Navigation

#1 by (0 votes)

0

Good morning, I hope someone can help me with my doubt, I'm new to this from Google spreadsheets and I start to automate some things. I have a script that validates the answers of a form, my problem is that I have not been able to copy data from the answer sheet of the form to another spreadsheet or directly send the answers of the form to another spreadsheet.

I share the detailed script and in advance I appreciate your valuable help.

Valid the answers when the form was sent and assigned a consecutive number;

function formSubmitReply(e) {

var Consecutivo = AgregarNumeroConsecutivo();

var timestamp =e.values[0];
var Contrasena =e.values[1];
var Autoriza =e.values[2];

Answers 2 and 0 are sent to cell G1 on the form's answer sheet.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var ss = SpreadsheetApp.getActive();
var sh =  ss.getSheets()[0];
var sheet =ss.getSheets()[0];
SpreadsheetApp.getActiveSpreadsheet().getSheets() 
[0].getRange("G1:G1").setValue(Autoriza+"\n\n"+timestamp);

Then I try to copy cell G1 to another book; I could not make this step work.

var target = 
SpreadsheetApp.openById("1osnF08ciK8b0jnYpWwOi9uahv7LxVh4We1hyFXapo-k");
var source_sheet = ss.getSheetByName("Respuestas de formulario 1");
var target_sheet = target.getSheetByName("Vale de salida");
var source_range = source_sheet.getRange("G1:G1");
var target_range = target_sheet.getRange("B5:B5");

source_range.copyTo(target_range);
}

Function to add consecutive number to form response.

function AgregarNumeroConsecutivo() {
var hoja = SpreadsheetApp.getActiveSheet();
var fila =SpreadsheetApp.getActiveSheet().getLastRow();
var Consecutivo = fila -1;
hoja.getRange(fila,4).setValue(Consecutivo);
return Consecutivo;

}

javascript google-apps-script google-forms

asked by RicardoSS 26.03.2018 в 18:19

source

1 answer

How do I make my figure shoot a bullet type without moving the triangle together? How to add 7 column in Sql?

score 0 · Answer 1

Good day, thanks for the help you provide on the site.

I was able to solve my problem with the initial question. Use something simple.

Remove this part of the code.

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ss = SpreadsheetApp.getActive();
  var sh =  ss.getSheets()[0];
  var sheet =ss.getSheets()[0];
  SpreadsheetApp.getActiveSpreadsheet().getSheets() 
  [0].getRange("G1:G1").setValue(Autoriza+"\n\n"+timestamp);
  var target = 
  SpreadsheetApp.openById("1osnF08ciK8b0jnYpWwOi9uahv7LxVh4We1hyFXapo-");
  var source_sheet = ss.getSheetByName("Respuestas de formulario 1");
  var target_sheet = target.getSheetByName("Vale de salida");
  var source_range = source_sheet.getRange("G1:G1");
  var target_range = target_sheet.getRange("B5:B5");

  source_range.copyTo(target_range);
  }

Since I only needed 2 answers and the target range would always be the same, I replaced it with this line. Now every time they answer a form, the answer goes to the default answer sheet and in addition 2 answers are going to another spreadsheet.

 SpreadsheetApp.openById("1osnF08ciK8b0jnYpWwOi9uahv7LxVh4We1hyFXapo-").getSheets()[0].getRange("B22").setValue(Autoriza+"\n\n"+timestamp);

So I get my final script.

function formSubmitReply(e) {

var Consecutivo = AgregarNumeroConsecutivo();

var timestamp =e.values[0];
var Contrasena =e.values[1];
var Autoriza =e.values[2];

SpreadsheetApp.openById("1osnF08ciK8b0jnYpWwOi9uahv7LxVh4We1hyFXapo- 
").getSheets()[0].getRange("B22").setValue(Autoriza+"\n\n"+timestamp);
}


function AgregarNumeroConsecutivo() {
var hoja = SpreadsheetApp.getActiveSheet();
var fila =SpreadsheetApp.getActiveSheet().getLastRow();
var Consecutivo = fila -1;
hoja.getRange(fila,4).setValue(Consecutivo);
return Consecutivo;

}