#JP15 Membuat Auto Link Update Google Form

*Untuk Mendapatkan Full Script tanpa password silahkan Klik Disini*

Membuat Auto Link Update Google Form

1. Buatlah Google Form

(Atau bisa Copy Klik Disini)

2. Klik "Setelan" dan aktifkan pengeditan.

3. Klik "Jawaban" dan buatlah database Spreadsheet.

4. Setelah Spreadsheet terbuka, buatlah lembar kerja AppScript dengan cara klik menu "Ekstensi" - pilih "AppScript".

5. Buatlah file script dengan nama "Code.gs", "EditUrl.gs" dan "EDITURL.gs"

(Nama harus sama perhatikan gambar dibawah ini)

6. Copy dan paste script dibawah ini ke menu "Code.gs".

Masukkan Password Untuk Melihat Script (Password ada di dalam video)

// Visit : www.javabitpro.com
// Youtube : javabitpro


// Create the Form Submit Trigger
function createFormTrigger() {
  var triggerName = 'https://docs.google.com/spreadsheets/d/1Mmt02hmVRKkvhs1HbnovO1xV511mf0V7wnEnhL3oIjA/edit?resourcekey#gid=1623805066';
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  ScriptApp.newTrigger(triggerName)
    .forSpreadsheet(spreadsheet)
    .onFormSubmit()
    .create();
}

function addFormResponseUrl(e) {
  // Get the Google Form linked to the response
  var responseSheet = e.range.getSheet();
  var googleFormUrl = responseSheet.getFormUrl();
  var googleForm = FormApp.openByUrl(googleFormUrl);

  // Get the form response based on the timestamp
  var timestamp = new Date(e.namedValues.Timestamp[0]);
  var formResponse = googleForm.getResponses(timestamp).pop();

  // Get the Form response URL and add it to the Google Spreadsheet
  var responseUrl = formResponse.getEditResponseUrl();
  var row = e.range.getRow();
  var responseColumn = 8; // Column where the response URL is recorded.
  responseSheet.getRange(row, responseColumn).setValue(responseUrl);
}

Sesuaikan alamat Spreadsheet kalian.

7. Copy dan pastekan script dibawah ini ke "EditUrl.gs".

Masukkan Password Untuk Melihat Script (Password sama dengan di atas)

// Visit : www.javabitpro.com
// Youtube : javabitpro

function registerNewEditResponseURLTrigger() {
  // check if an existing trigger is set
  var existingTriggerId = PropertiesService.getUserProperties().getProperty('onFormSubmitTriggerID')
  if (existingTriggerId) {
    var foundExistingTrigger = false
    ScriptApp.getProjectTriggers().forEach(function (trigger) {
      if (trigger.getUniqueId() === existingTriggerId) {
        foundExistingTrigger = true
      }
    })
    if (foundExistingTrigger) {
      return
    }
  }
  var trigger = ScriptApp.newTrigger('onFormSubmitEvent')
    .forSpreadsheet(SpreadsheetApp.getActive())
    .onFormSubmit()
    .create()
  PropertiesService.getUserProperties().setProperty('onFormSubmitTriggerID', trigger.getUniqueId())
}
function getTimestampColumn(sheet) {
  for (var i = 1; i <= sheet.getLastColumn(); i += 1) {
    if (sheet.getRange(1, i).getValue() === 'Timestamp') {
      return i
    }
  }
  return 1
}
function getFormResponseEditUrlColumn(sheet) {
  var form = FormApp.openByUrl(sheet.getFormUrl())
  for (var i = 1; i <= sheet.getLastColumn(); i += 1) {
    if (sheet.getRange(1, i).getValue() === 'Form Response Edit URL') {
      return i
    }
  }
  // get the last column at which the url can be placed.
  return Math.max(sheet.getLastColumn() + 1, form.getItems().length + 2)
}
/**
 * params: { sheet, form, formResponse, row }
 */
function addEditResponseURLToSheet(params) {
  if (!params.col) {
    params.col = getFormResponseEditUrlColumn(params.sheet)
  }
  var formResponseEditUrlRange = params.sheet.getRange(params.row, params.col)
  formResponseEditUrlRange.setValue(params.formResponse.getEditResponseUrl())
}
function onOpen() {
  var menu = [{ name: 'Add Form Edit Response URLs', functionName: 'setupFormEditResponseURLs' }]
  SpreadsheetApp.getActive().addMenu('Forms', menu)
}
function setupFormEditResponseURLs() {
  var sheet = SpreadsheetApp.getActiveSheet()
  var spreadsheet = SpreadsheetApp.getActive()
  var formURL = sheet.getFormUrl()
  if (!formURL) {
    SpreadsheetApp.getUi().alert('No Google Form associated with this sheet. Please connect it from your Form.')
    return
  }
  var form = FormApp.openByUrl(formURL)
  // setup the header if not existed
  var headerFormEditResponse = sheet.getRange(1, getFormResponseEditUrlColumn(sheet))
  var title = headerFormEditResponse.getValue()
  if (!title) {
    headerFormEditResponse.setValue('Form Response Edit URL')
  }
  var timestampColumn = getTimestampColumn(sheet)
  var editResponseUrlColumn = getFormResponseEditUrlColumn(sheet)
  
  var timestampRange = sheet.getRange(2, timestampColumn, sheet.getLastRow() - 1, 1)
  var editResponseUrlRange = sheet.getRange(2, editResponseUrlColumn, sheet.getLastRow() - 1, 1)
  if (editResponseUrlRange) {
    var editResponseUrlValues = editResponseUrlRange.getValues()
    var timestampValues = timestampRange.getValues()
    for (var i = 0; i < editResponseUrlValues.length; i += 1) {
      var editResponseUrlValue = editResponseUrlValues[i][0]
      var timestampValue = timestampValues[i][0]
      if (editResponseUrlValue === '') {
        var timestamp = new Date(timestampValue)
        if (timestamp) {
          var formResponse = form.getResponses(timestamp)[0]
          editResponseUrlValues[i][0] = formResponse.getEditResponseUrl()
          var row = i + 2
          if (row % 10 === 0) {
            spreadsheet.toast('processing rows ' + row + ' to ' + (row + 10))
            editResponseUrlRange.setValues(editResponseUrlValues)
            SpreadsheetApp.flush()
          }
        }
      }
    }
    
    editResponseUrlRange.setValues(editResponseUrlValues)
    SpreadsheetApp.flush()
  }
  registerNewEditResponseURLTrigger()
  SpreadsheetApp.getUi().alert('You are all set! Please check the Form Response Edit URL column in this sheet. Future responses will automatically sync the form response edit url.')
}
function onFormSubmitEvent(e) {
  var sheet = e.range.getSheet()
  var form = FormApp.openByUrl(sheet.getFormUrl())
  var formResponse = form.getResponses().pop()
  addEditResponseURLToSheet({
    sheet: sheet,
    form: form,
    formResponse: formResponse,
    row: e.range.getRow(),
  })
}

8. Copy dan pastekan script dibawah ini ke "EDITURL.gs"

Masukkan Password Untuk Melihat Script (Password sama dengan di atas)

// Visit : www.javabitpro.com
// Youtube : javabitpro

// Form URL
var formURL = 'https://docs.google.com/forms/d/e/1FAIpQLScpkBzw_Z2hXSNt0g5EAN_2WPK0k8HY0uquFk2B3e_Nj2AojA/viewform';
// Sheet name used as destination of the form responses
var sheetName = 'Form Responses 1';
/*
 * Name of the column to be used to hold the response edit URLs 
 * It should match exactly the header of the related column, 
 * otherwise it will do nothing.
 */
var columnName = 'Edit Url' ;
// Responses starting row
var startRow = 2;

function getEditResponseUrls(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues(); 
  var columnIndex = headers[0].indexOf(columnName);
  var data = sheet.getDataRange().getValues();
  var form = FormApp.openByUrl(formURL);
  for(var i = startRow-1; i < data.length; i++) {
    if(data[i][0] != '' && data[i][columnIndex] == '') {
      var timestamp = data[i][0];
      var formSubmitted = form.getResponses(timestamp);
      if(formSubmitted.length < 1) continue;
      var editResponseUrl = formSubmitted[0].getEditResponseUrl();
      sheet.getRange(i+1, columnIndex+1).setValue(editResponseUrl);
    }
  }
}

Sesuaikan alamat Google Form kalian.

9. Klik icon "Save".

10. Ubah pengaturan untuk dapat mengakses pengeditan Google Form dengan cara klik menu "EditUrl.gs" - ubah pengaturan running menjadi "setupFormEditResponsesURLs"

(Perhatikan gambar dibawah)

11. Klik "Run" atau "Jalankan".

12. Apabila membutuhkan hak akses, silahkan tonton tutorial videonya untuk melewatinya (Klik Disini)

13. Setelah di Run, silahkan buka Spreadsheet. Maka akan muncul notifikasi seperti gambar dibawah ini, silahkan klik "OKE"

14. Pada Spreadsheet otomatis akan ada kolom baru yang berisi link untuk mengedit pada Google Form

SELESAI !

NB. Setiap penginputan pada Google Form, link pada kolom edit akan terisi otomatis