compare two spreadsheet and output the difference using google app scripts - Stack Overflow

PHOTO

Mon Sep 12 2022 09:29:46 GMT+0000 (Coordinated Universal Time)

function getNewData() {
  //deleting triggers
  var triggers = ScriptApp.getProjectTriggers();    
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction()=='getNewData'){
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }  
  //max running time = 5.5 min
  var MAX_RUNNING_TIME = 330000;
  var startTime= (new Date()).getTime();
  //get cache
  var cache = CacheService.getUserCache();
  var downloaded =JSON.parse(cache.get('downloaded'));  
  var compared =JSON.parse(cache.get('compared'));
  //start
  if (downloaded==1 && compared!=1){
    //folder
    var Folder = DriveApp.getFoldersByName('theFolder').next();
    var FolderId = licitacionesFolder.getId();
    //call old_spreadsheet
    var searchFor ="fullText contains 'sheet_old' and '" + FolderId + "' in parents";  
    var files = DriveApp.searchFiles(searchFor); 
    var old_file = files.next();  
    var old_spreadsheet = SpreadsheetApp.openById(old_file.getId());
    var old_sheet = old_spreadsheet.getSheets()[0];
    var old_array = old_sheet.getDataRange().getValues();
    //call spreadsheet
    var searchFor ="fullText contains 'sheet' and '" + FolderId + "' in parents";  
    var files = DriveApp.searchFiles(searchFor); 
    var file = files.next();  
    var spreadsheet = SpreadsheetApp.openById(old_file.getId());
    var sheet = spreadsheet.getSheets()[0];
    var array = sheet.getDataRange().getValues();
    Logger.log(array.length+'::'+old_array.length); 
    // Column
    var searchString = 'NAME';
    var RC = getColumn(array,searchString);    
    var Row = RC.Row;
    var Column = RC.Column;
    var RC = getColumn(old_array,searchString);    
    var Row_old = RC.Row;
    var Column_old = RC.Column;    
    Logger.log(Row_old+':::'+Column_old+'\n'+Row+':::'+Column);      
    //compare 
    var diff_index =JSON.parse(cache.get('diff_index'));
    var row_ind =JSON.parse(cache.get('row_ind'));  
    var Roww =JSON.parse(cache.get('Row'));  
    if (diff_index==null){var diff_index = [];}
    if (row_ind==null){var row_ind = 0;}
    if (Roww==null){var Roww = Row;}    
    Logger.log(row_ind+'\n'+Roww);    
    for (var i=Roww;i<array.length;i++){  
      var currTime = (new Date()).getTime();
      if(currTime - startTime >= MAX_RUNNING_TIME){
        Logger.log((currTime - startTime)/(1000*60));
        Logger.log(i+'::'+row_ind);
        cache.putAll({'diff_index': JSON.stringify(diff_index),'row_ind': JSON.stringify(row_ind),'Row': JSON.stringify(i-1)},21600);   
        ScriptApp.newTrigger('getNewData').timeBased().after(2 * 60 * 1000).create();
        return;
      } else {
        Logger.log(i);
        var existe = ArrayLib.indexOf(old_array, Column_old, array[i][Column]);
        if (existe==-1){      
          Logger.log(row_ind+'!!!');
          diff_index[row_ind]=i;
          row_ind++;          
        }
      }
    }    
    cache.putAll({'diff_index': JSON.stringify(diff_index),'Row': JSON.stringify(Row),'compared': JSON.stringify(1)},21600);
  } else {
    Logger.log('file not downloaded yet or already compared');
  }
}

function getColumn(array,searchString){
  for (var i = 0; i < array.length; i++) {    
    for (var j = 0; j < array[i].length; j++) {    
      if (array[i][j] == searchString) {
        var Row = i+1;
        var Column = j;
        break;
      }      
    }
    if (Row != undefined){
      break;
    }
  }
  return {Row: Row, Column: Column};
}

COPY

https://stackoverflow.com/questions/44391955/compare-two-spreadsheet-and-output-the-difference-using-google-app-scripts

Save snippets that work from anywhere online with our extensions

Comments

GAS

@adegard

Google App Script Search and Update Single Cell Value Across Multiple Sheets If Found - Stack Overflow How Can I Use A Google Script to Keep Revision History in Google Drive for Files Edited After A Certain Date? - Stack Overflow compare two spreadsheet and output the difference using google app scripts - Stack Overflow

#html #javascript #nodejs

How to Save HTML Form Data in JSON - Express

<form action="/new" method="post">
 
  <input name="title" type="text">
  <input name="description" type="text">
  <button type="submit">Submit Form</button>
 
</form>

> More steps

#javascript

Get URL and URL Parts in JavaScript

var newURL = window.location.protocol + "//" + window.location.host + "/" + window.location.pathname + window.location.search

#javascript

Add event listener to multiple buttons with the same class

    btns = document.getElementsByClassName("saveBtn");
    for (var i = 0; i < btns.length; i++) {
        btns[i].addEventListener("click", function () {
			//Add function here
        });
    }

#javascript

Replace Textarea with Codemirror editor

<link rel="stylesheet" type="text/css" href="plugin/codemirror/lib/codemirror.css">

<body>
	<textarea class="codemirror-textarea"></textarea>
</body>

<script>

$(document).ready(function(){
    var codeText = $(".codemirror-textarea")[0];
    var editor = CodeMirror.fromTextArea(codeText, {
        lineNumbers : true
    });
});

</script>

<script type="text/javascript" src="plugin/codemirror/lib/codemirror.js"></script>

#javascript #nodejs #commandline

HTTPS Redirect in Nodejs

npm install heroku-ssl-redirect

> More steps

#javascript #nodejs

Save to MongoDB without Duplicates Using Mongoose

var mongoose = require('mongoose');
var Schema = mongoose.Schema;

const exampleSchema = new Schema({
    title: { type: String , required: true},
    content: [{type: String}]
});


var Example = mongoose.model('Example', exampleSchema);
module.exports = Example;

> More steps

#javascript

3 Ways To Detect Selenium Bots Using Javascript - CodingTutz

if (navigator.webdriver) {
    document.body.innerHTML = "This is a Bot";
}

#javascript

Full Stack Developer

function full_stack_developer() {
    full_stack_developer();
}

#html #javascript #jsfunctions #dom #dommanipulation

getAttribute() - how to get the value of any attribute of HTML element

<html>

<input id="contact" name="address">

<script>

    var x = document.getElementById("contact").getAttribute('name');

</script>

</html>

#javascript #javascript #functions #parameters

Required Parameters for Functions in JavaScript

 const isRequired = () => { throw new Error('param is required'); };

const hello = (name = isRequired()) => { console.log(`hello ${name}`) };

// These will throw errors
hello();
hello(undefined);

// These will not
hello(null);
hello('David');
The idea here is that it uses default parameters, like how the b parameter here has a default if you don’t send it anything:
function multiply(a, b = 1) {
  return a * b;
}

#javascript #promises #howto

How to Get the Domain From A URL Using Javascript

var url = "http://scratch99.com/web-development/javascript/";
var urlParts = url.replace('http://','').replace('https://','').split(/[/?#]/);
var domain = urlParts[0];

#javascript

Mapping an Object

const object1 = {
  a: 'somestring',
  b: 42
};

for (let [key, value] of Object.entries(object1)) {
  console.log(`${key}: ${value}`);
}

#javascript

Copy Text to Clipboard with Line Breaks

function copyToClipboard(){

    var codeToBeCopied = document.getElementById('code-snippet').innerText;
    var emptyArea = document.createElement('TEXTAREA');
    emptyArea.innerHTML = codeToBeCopied;
    const parentElement = document.getElementById('post-title');
    parentElement.appendChild(emptyArea);

    emptyArea.select();
    document.execCommand('copy');

    parentElement.removeChild(emptyArea);
    M.toast({html: 'Code copied to clipboard'})

    }

#javascript

How to store fetch response in javascript variable - Stack Overflow

async function fun() {
  return fetch('https://jsonplaceholder.typicode.com/todos/1').then(res => res.json());
}

const data  = await fun();

#javascript

Detect click outside element (vanilla JavaScript) - Stack Overflow

var specifiedElement = document.getElementById(%27a%27);

//I%27m using "click" but it works with any event
document.addEventListener(%27click%27, function(event) {
  var isClickInside = specifiedElement.contains(event.target);

  if (!isClickInside) {
    //the click was outside the specifiedElement, do something
  }
});

compare two spreadsheet and output the difference using google app scripts - Stack Overflow

Save snippets that work from anywhere online with our extensions

Comments

More like this

GAS

Browse more snippets >>

compare two spreadsheet and output the difference using google app scripts - Stack Overflow

Save snippets that work from anywhere online with our extensions

Comments

More like this

GAS

Browse more snippets >>

Embed code snippet